You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cayenne.apache.org by Nikita Timofeev <nt...@objectstyle.com> on 2019/01/21 07:11:13 UTC
Cayenne geospacial features
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
Re: Cayenne geospacial features
Posted by Andrus Adamchik <an...@objectstyle.org>.
I recall Tore had a suite of GIS software written in Cayenne. But this was years ago.
Andrus
> On Jan 21, 2019, at 2:11 AM, Nikita Timofeev <nt...@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
Re: Cayenne geospacial features
Posted by Mike Kienenberger <mk...@gmail.com>.
On Sat, Feb 23, 2019 at 4:40 AM Tore Halset <ha...@pvv.ntnu.no> wrote:
> 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.
>
A google search turned out a mangled version here:
https://grokbase.com/t/cayenne/commits/088bkd5g1s/conf-apache-cayenne-mapping-jts-geometries-page-edited
For some reason it's not in the internet archive wayback machine.
Re: Cayenne geospatial features
Posted by Andrus Adamchik <an...@objectstyle.org>.
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 <nt...@objectstyle.com> 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 <an...@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 <ha...@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 <nt...@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
Re: Cayenne geospacial features
Posted by Nikita Timofeev <nt...@objectstyle.com>.
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 <an...@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 <ha...@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 <nt...@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
Re: Cayenne geospacial features
Posted by Andrus Adamchik <an...@objectstyle.org>.
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 <ha...@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 <nt...@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
>>
>
Re: Cayenne geospacial features
Posted by Tore Halset <ha...@pvv.ntnu.no>.
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 <nt...@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
>