I finally got some time to play with Cayenne 4.2 and PostGIS [1]. I implemented
something similar to Tore's WKTGeometryType, using
"org.locationtech.jts:jts-core" lib for geometries. Though it required an extra
step: byte[] coming back on select would not convert to Geometry properly. So I
had to wrap select column in a PostGIS "ST_AsBinary" function. Luckily Cayenne
4.2 makes it possible, and actually quite easy, as there is a full AST for the
generated SQL that can be manipulated [2]. Thanks Nikita, this API is awesome!
:)
This is just a POC, and I can think of a few more improvements. E.g.:
* Lazy geometry parsing
* Support for other geometry representations, such as GeoJSON
("org.wololo:jts2geojson"), that will require other column functions .
* Provide a set of custom functions for expressions.
* ...
I am not yet sure as to the final home of this module. Initially I was planning
to use it on a commercial project. But I'd certainly want to distill it to a
reusable open source library.
Andrus
[1] https://github.com/andrus/cayenne-postgis
<https://github.com/andrus/cayenne-postgis>
[2]
https://github.com/andrus/cayenne-postgis/blob/master/src/main/java/org/example/cayenne/postgis/cayenne/PostgisSQLTreeProcessor.java
> On Feb 25, 2019, at 10:17 AM, Nikita Timofeev <[email protected]>
> wrote:
>
> 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 <[email protected]>
> 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 <[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
>>>>
>>>
>>
>
>
> --
> Best regards,
> Nikita Timofeev
