Re: Cayenne geospacial features
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 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 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 geometryClass;
> >
> >public WKTGeometryType(Class 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
Re: Cayenne geospacial features
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 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 geometryClass;
>
>public WKTGeometryType(Class 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 wrote:
>>
>> Hi All,
>>
>> I had an interesting discussion with my colleagues who use PostGIS and
>> they are really interested in better
Re: Cayenne geospacial features
On Sat, Feb 23, 2019 at 4:40 AM Tore Halset 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 geospacial features
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 geometryClass;
public WKTGeometryType(Class 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 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
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 > 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
