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 <[email protected]> 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 <[email protected]> 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 >> >
