PostGIS with geographic types


Kevin A. Roll
 

I am attempting to map a simple point field into a PostGIS database. I’ve configured package.jdo according to the example. Schema tool is generating something like the following:

CREATE TABLE "IMAGESOURCE" ();
ALTER TABLE "IMAGESOURCE" ADD COLUMN "IMAGESOURCE_ID" SERIAL;
SELECT AddGeometryColumn( '', 'IMAGESOURCE', 'LOCATION', 4326, 'POINT', 2 );

This creates a column of type geometry in the database. PostGIS supports two geospatial types: geometry (plane-based) and geography (sphere-based). My desire is to use the geography type in order to get true distance-based results; is this possible using DataNucleus? To further confuse the issue, the docs for AddGeometryColumn don’t show a way to create a geography column - this can only be done with a direct create table statement. If I can’t get schema tool to do this automatically, can I alter the table afterwards and expect everything else to still work OK? Thanks!


Andy
 
Edited

Hi,
I presume you mean your java type is com.vividsolutions.jts.geom.Point ? This is currently only supported for persisting to PostGIS "geometry", as per http://www.datanucleus.org:15080/products/accessplatform_5_1/jdo/mapping.html#_geospatial_types  (page down to that java type).

The two people who contributed this support (back in 2006) didn't add anything for "geography" (maybe it didn't exist back in 2006?). Ought to be doable to support it, but I have no time so you'd have to get the code and add it.

Specifically, if you forked the datanucleus-geospatial project, and then look at this package https://github.com/datanucleus/datanucleus-geospatial/tree/master/src/main/java/org/datanucleus/store/types/geospatial/rdbms/mapping/jts2postgis
Each java field in a class will have a "JavaTypeMapping".
In the case of a JTS Point (using Geometry) that will be a https://github.com/datanucleus/datanucleus-geospatial/blob/master/src/main/java/org/datanucleus/store/types/geospatial/rdbms/mapping/jts/PointMapping.java This JavaTypeMapping, in turn, has an RDBMS mapping (equivalent of the column) ... in that case https://github.com/datanucleus/datanucleus-geospatial/blob/master/src/main/java/org/datanucleus/store/types/geospatial/rdbms/mapping/jts2postgis/PointRDBMSMapping.java 
Likely you'd need to add GeographyRDBMSMapping, and equivalent subclasses.

Then, back in your application, specify the JDO metadata for that field to use that sql-type
i.e <column name="..." sql-type="geography"/>
Once basic persistence and retrieval works, then there are query functions that would maybe need modifications/updates


If the java type is instead org.postgis.Point then the referenced package/classes above will be different.


HTH


Kevin A. Roll
 

Thanks Andy, that helps a lot. I actually tried all 3 types and settled on org.postgis.Point, but that is not written in stone. Given the complexity of this I may approach it another way... I might be able to get by with the planar distance measurements, or even pull in the points and do the calculation in-memory as I don't expect to have a very large dataset. Now I know where to look if I want to support this. Thanks again!