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 >
