Hi Andrus, > > 1. Custom functions (already on master - [1]) > 2. Joins that are not equi-joins (spatial joins [2] in our case - > ST_Intersects/ST_Contains/etc). > > @Nikita - anything else I might have missed?
Essentially that's it for now. Here are links to related JIRA tasks: https://issues.apache.org/jira/browse/CAY-2512 https://issues.apache.org/jira/browse/CAY-2526 https://issues.apache.org/jira/browse/CAY-2529 https://issues.apache.org/jira/browse/CAY-2528 On Sat, Feb 23, 2019 at 5:38 PM Andrus Adamchik <and...@objectstyle.org> wrote: > > Hi Tore, > > Thanks for sharing your solutions. > > > Next up is PostGIS queries. PostGIS queries are very powerful, but the > > syntax can be quite strange. SQLTemplate or something similar should work > > fine. I have some simple application specific java abstraction to the most > > common stuff like ST_Intersects to fetch rows matching a tile and such. > > This is primarily the area where we think we might improve Cayenne > abstractions for both spatial and non-spatial advanced SQL. We'd like to add > the ability for object queries / mapping to contain the following: > > 1. Custom functions (already on master - [1]) > 2. Joins that are not equi-joins (spatial joins [2] in our case - > ST_Intersects/ST_Contains/etc). > > @Nikita - anything else I might have missed? > > None of the above appears to break the basic ORM assumptions, just taking > them to the next level. So hopefully it won't create leaky abstractions. > > Andrus > > [1] https://issues.apache.org/jira/browse/CAY-2512 > [2] http://postgis.net/workshops/postgis-intro/joins.html > > > > > > On Feb 23, 2019, at 1:40 AM, Tore Halset <hal...@pvv.ntnu.no> wrote: > > > > Hello. > > > > With Cayenne, you can easily create a ExtendedType that convert JTS > > geometries on the java side to Well-Known Binary on the database side. I > > did create a similar ExtendedType to create JTS geometries to Well-Known > > Text for storing JTS geometries as text (se below). We have used this in > > production for several years. For PostGIS, I have not used Cayenne on the > > geometries, but it should work identical. > > > > A long time ago, I wrote about this over at > > http://objectstyle.org/confluence/display/CAY/Mapping+JTS+Geometries , but > > that website does not exist anymore. > > > > Next up is PostGIS queries. PostGIS queries are very powerful, but the > > syntax can be quite strange. SQLTemplate or something similar should work > > fine. I have some simple application specific java abstraction to the most > > common stuff like ST_Intersects to fetch rows matching a tile and such. > > > > For related libraries, JTS has lots of geometry goodies. It is in the > > middle of a move with package name switch. The old one from vividsolutions > > is most compatible with other libraries for now. Geotools has lots of > > goodies for projection and such. And perhaps our library, > > https://github.com/ElectronicChartCentre/java-vector-tile , for > > constructing Mapbox Vector Tiles from java for use in Mapbox GL JS or > > Native. > > > > Regards, > > Tore Halset. > > > > /** > > * An ExtendedType that can be used by cayenne to covert WKT to/from JTS > > * Geometry - com.vividsolutions.jts.geom.Geometry > > */ > > public class WKTGeometryType implements ExtendedType { > > > > private static final String CHARSET = "UTF-8"; > > > > private Class<? extends Geometry> geometryClass; > > > > public WKTGeometryType(Class<? extends Geometry> geometryClass) { > > this.geometryClass = geometryClass; > > } > > > > public String getClassName() { > > return geometryClass.getName(); > > } > > > > public boolean validateProperty(Object source, String property, Object > > value, > > DbAttribute dbAttribute, ValidationResult validationResult) { > > Geometry g = (Geometry) value; > > if (!g.isValid()) { > > String msg = "Invalid geometry"; > > validationResult.addFailure(new BeanValidationFailure(source, > > property, msg)); > > return false; > > } > > return true; > > } > > > > public void setJdbcObject(PreparedStatement statement, Object value, int > > pos, int type, > > int precision) throws Exception { > > Geometry g = (Geometry) value; > > switch (type) { > > case Types.BLOB: > > statement.setBytes(pos, g.toText().getBytes(CHARSET)); > > break; > > default: > > statement.setString(pos, g.toText()); > > break; > > } > > } > > > > public Object materializeObject(ResultSet rs, int index, int type) > > throws Exception { > > String wkt = null; > > switch (type) { > > case Types.BLOB: > > wkt = new String(rs.getBytes(index), CHARSET); > > break; > > default: > > wkt = rs.getString(index); > > break; > > } > > if (wkt == null) { > > return (Geometry) null; > > } > > return new WKTReader(new GeometryFactory()).read(wkt); > > } > > > > public Object materializeObject(CallableStatement rs, int index, int > > type) throws Exception { > > String wkt = null; > > switch (type) { > > case Types.BLOB: > > wkt = new String(rs.getBytes(index), CHARSET); > > break; > > default: > > wkt = rs.getString(index); > > break; > > } > > if (wkt == null) { > > return (Geometry) null; > > } > > return new WKTReader(new GeometryFactory()).read(wkt); > > } > > > > } > > > >> On 21 Jan 2019, at 08:11, Nikita Timofeev <ntimof...@objectstyle.com> > >> wrote: > >> > >> Hi All, > >> > >> I had an interesting discussion with my colleagues who use PostGIS and > >> they are really interested in better support from Cayenne. Well, > >> actually they can’t use Cayenne on that project, as it will be like > >> using just raw SQL. > >> > >> So I was wondering if anyone else actively uses spacial features and > >> can help to determine missing parts in Cayenne. Maybe we can push this > >> forward and get some really nice cayenne-spacial module. > >> > >> Any feedback is welcome. > >> > >> -- > >> Best regards, > >> Nikita Timofeev > >> > > > -- Best regards, Nikita Timofeev
