Issue Details (XML | Word | Printable)

Key: NUCSPATIAL-28
Type: Improvement Improvement
Status: Closed Closed
Resolution: Fixed
Priority: Minor Minor
Assignee: Unassigned
Reporter: Baris ERGUN
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
DataNucleus Types : Geospatial

Implement missing Spatial methods for Postgis

Created: 25/Mar/13 09:40 AM   Updated: 31/Jul/13 04:53 PM   Resolved: 08/Jul/13 08:08 PM
Component/s: None
Affects Version/s: 3.1.1, 3.2.0.m1, 3.2.0.m3, 3.2.0.release
Fix Version/s: 3.2.2

File Attachments: 1. File patch_nucspatial_28.diff (159 kB)
2. File patch_nucspatial_28_1.diff (51 kB)
3. File patch_nuctest_x.diff (15 kB)


Datastore: PostgreSQL
Severity: Development


 Description  « Hide
Implement missing JDOQL Spatial methods for Postgis. According to the JDOQL spatial methods url the missing methods will be identified and implemented.

http://www.datanucleus.org/products/accessplatform_3_2/datastores/rdbms_jdoql_spatial_methods.html

Sort Order: Ascending order - Click to sort in descending order
Baris ERGUN added a comment - 25/Mar/13 09:41 AM
can be assigned to me.

Baris ERGUN added a comment - 28/Apr/13 06:05 PM
The nature of Datanucleus plugin development with SqlExpression abstraction is fairly a simple task. Although because I need to test the implementations it takes time. I have completed half of the spatial methods mentioned in http://www.datanucleus.org/products/accessplatform_3_2/datastores/rdbms_jdoql_spatial_methods.html. I have also added some new methods like geographyfromwkb and geographyfromtext. Hope to finish rest of the work within this week. FYI

Andy Jefferson added a comment - 08/May/13 08:09 AM
That doc (written by Thomas/Stefan a long time ago, when they wrote the spatial plugin) says whether PostGIS is supported for the different methods, and it has a tick whether it is (and the vast majority are ticked for PostGIS). Perhaps you could define what methods you mean are not supported, or whether this is for the most recent release of PostGIS (perhaps their implementation is for an old version of PostGIS?). It's not clear from the description which are being covered. Thanks!

Baris ERGUN added a comment - 17/May/13 07:02 AM
st_geographyfromtext
http://www.postgis.org/docs/ST_GeographyFromText.html
st_geogfromwkb
http://postgis.refractions.net/docs/ST_GeogFromWKB.html
st_transform
http://www.postgis.org/docs/ST_Transform.html

Are the new spatial methods I am introducing for Postgis on Datanucleus.


Baris ERGUN added a comment - 04/Jul/13 04:26 PM
The code is complete please find the attached diff for evaluation.

Baris ERGUN added a comment - 04/Jul/13 09:18 PM
I have been testing datanucleus Postgres9.0 and Postgis2.0 and greater versions. And none of the ticked abstract methods in Spatial namespace (eg Spatial.distance) were supported for Postgis/Postgresql. Thats why I prepared the attached patch.

Andy Jefferson added a comment - 08/Jul/13 08:08 PM
SVN trunk has your patch (with one or two files reformatted to DN conventions). Thx.

No idea why Thomas/Stefan claimed it supported those on PostgreSQL before, and only they can answer that. I don't have PSQL+PostGIS readily available so reliant on your testing (those methods are all included in "test.jdo.spatial" so you ought to have run that with "postgresql" profile i.e cd to that test suite and run "mvn clean test -Ppostgresql")

Baris ERGUN added a comment - 09/Jul/13 11:22 AM
"those methods are all included in "test.jdo.spatial" so you ought to have run that with "postgresql" profile i.e cd to that test suite and run "mvn clean test -Ppostgresql".

I could not understand this well? Can u explain it a bit more? Thx.

Andy Jefferson added a comment - 09/Jul/13 03:46 PM
Of course. DataNucleus comes with unit tests (mainly end-to-end tests) in SVN trunk under
test/accessplatform/trunk/

There is one test suite project called "test.jdo.spatial" and this has a series of tests for the RDBMS spatial plugin (which will make use of many of those methods you changed/implemented). These tests should be runnable against any datastore and you set the datastore to use via the Maven "profile". So to run those tests against PostgreSQL you type

mvn clean test -Ppostgresql

This will look for a PostgreSQL database on the local machine with schema called "nucleus". See http://www.datanucleus.org/development/test/unittests.html

Baris ERGUN added a comment - 19/Jul/13 05:35 PM
I started executing Junit intergation tests found some problems fixed some of them yet continuing on others. The below one is about not being able to cast the Postgis Geometry into JTS Geometry. I found how to do it, although expecting your advice on best place(plugin, core?) to fix it. Your help can speed up me,

Thanks

{code:none}
testGeomFromText(org.datanucleus.tests.JtsGeometrySpatialTest) Time elapsed: 0.495 sec <<< ERROR!
javax.jdo.JDOUserException: Exception thrown while loading remaining rows of query
        at org.datanucleus.api.jdo.JDOAdapter.getUserExceptionForException(JDOAdapter.java:1139)
        at org.datanucleus.store.rdbms.query.ForwardQueryResult.closingConnection(ForwardQueryResult.java:272)
        at org.datanucleus.store.query.AbstractQueryResult.disconnect(AbstractQueryResult.java:107)
        at org.datanucleus.store.rdbms.query.AbstractRDBMSQueryResult.disconnect(AbstractRDBMSQueryResult.java:68)
        at org.datanucleus.store.rdbms.query.JDOQLQuery$2.transactionPreClose(JDOQLQuery.java:711)
        at org.datanucleus.store.connection.AbstractManagedConnection.transactionPreClose(AbstractManagedConnection.java:94)
        at org.datanucleus.store.connection.ConnectionManagerImpl$2.transactionPreCommit(ConnectionManagerImpl.java:438)
        at org.datanucleus.TransactionImpl.internalPreCommit(TransactionImpl.java:375)
        at org.datanucleus.TransactionImpl.commit(TransactionImpl.java:264)
        at org.datanucleus.api.jdo.JDOTransaction.commit(JDOTransaction.java:98)
        at org.datanucleus.tests.JtsGeometrySpatialTest.loadData(JtsGeometrySpatialTest.java:1800)
        at org.datanucleus.tests.JtsGeometrySpatialTest.setUp(JtsGeometrySpatialTest.java:1707)
        at junit.framework.TestCase.runBare(TestCase.java:128)
        at junit.framework.TestResult$1.protect(TestResult.java:106)
        at junit.framework.TestResult.runProtected(TestResult.java:124)
        at junit.framework.TestResult.run(TestResult.java:109)
        at junit.framework.TestCase.run(TestCase.java:120)
        at junit.framework.TestSuite.runTest(TestSuite.java:230)
        at junit.framework.TestSuite.run(TestSuite.java:225)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:601)
        at org.apache.maven.surefire.junit.JUnitTestSet.execute(JUnitTestSet.java:96)
        at org.apache.maven.surefire.junit.JUnit3Provider.executeTestSet(JUnit3Provider.java:117)
        at org.apache.maven.surefire.junit.JUnit3Provider.invoke(JUnit3Provider.java:94)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:601)
        at org.apache.maven.surefire.util.ReflectionUtils.invokeMethodWithArray(ReflectionUtils.java:164)
        at org.apache.maven.surefire.booter.ProviderFactory$ProviderProxy.invoke(ProviderFactory.java:110)
        at org.apache.maven.surefire.booter.SurefireStarter.invokeProvider(SurefireStarter.java:175)
        at org.apache.maven.surefire.booter.SurefireStarter.runSuitesInProcessWhenForked(SurefireStarter.java:81)
        at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:68)
NestedThrowablesStackTrace:
java.lang.ClassCastException: org.postgis.PGgeometry cannot be cast to org.postgis.jts.JtsGeometry
        at org.datanucleus.store.rdbms.mapping.jts2postgis.GeometryRDBMSMapping.getObject(GeometryRDBMSMapping.java:84)
        at org.datanucleus.store.rdbms.mapping.jts.GeometryMapping.getObject(GeometryMapping.java:126)
        at org.datanucleus.store.rdbms.fieldmanager.ResultSetGetter.fetchObjectField(ResultSetGetter.java:178)
        at org.datanucleus.state.JDOStateManager.replacingObjectField(JDOStateManager.java:2168)
        at org.datanucleus.samples.jtsgeometry.SamplePoint.jdoReplaceField(SamplePoint.java)
        at org.datanucleus.samples.jtsgeometry.SamplePoint.jdoReplaceFields(SamplePoint.java)
        at org.datanucleus.state.JDOStateManager.replaceNonLoadedFields(JDOStateManager.java:3463)
        at org.datanucleus.store.rdbms.query.PersistentClassROF$2.fetchNonLoadedFields(PersistentClassROF.java:656)
        at org.datanucleus.ExecutionContextImpl.findObject(ExecutionContextImpl.java:3193)
        at org.datanucleus.store.rdbms.query.PersistentClassROF.getObjectForDatastoreId(PersistentClassROF.java:623)
        at org.datanucleus.store.rdbms.query.PersistentClassROF.getObject(PersistentClassROF.java:396)
        at org.datanucleus.store.rdbms.query.ForwardQueryResult.nextResultSetElement(ForwardQueryResult.java:175)
        at org.datanucleus.store.rdbms.query.ForwardQueryResult$QueryResultIterator.next(ForwardQueryResult.java:379)
        at org.datanucleus.store.rdbms.query.ForwardQueryResult.processNumberOfResults(ForwardQueryResult.java:137)
        at org.datanucleus.store.rdbms.query.ForwardQueryResult.advanceToEndOfResultSet(ForwardQueryResult.java:158)
        at org.datanucleus.store.rdbms.query.ForwardQueryResult.closingConnection(ForwardQueryResult.java:260)
        at org.datanucleus.store.query.AbstractQueryResult.disconnect(AbstractQueryResult.java:107)
        at org.datanucleus.store.rdbms.query.AbstractRDBMSQueryResult.disconnect(AbstractRDBMSQueryResult.java:68)
        at org.datanucleus.store.rdbms.query.JDOQLQuery$2.transactionPreClose(JDOQLQuery.java:711)
        at org.datanucleus.store.connection.AbstractManagedConnection.transactionPreClose(AbstractManagedConnection.java:94)
        at org.datanucleus.store.connection.ConnectionManagerImpl$2.transactionPreCommit(ConnectionManagerImpl.java:438)
        at org.datanucleus.TransactionImpl.internalPreCommit(TransactionImpl.java:375)
        at org.datanucleus.TransactionImpl.commit(TransactionImpl.java:264)
        at org.datanucleus.api.jdo.JDOTransaction.commit(JDOTransaction.java:98)
        at org.datanucleus.tests.JtsGeometrySpatialTest.loadData(JtsGeometrySpatialTest.java:1800)
        at org.datanucleus.tests.JtsGeometrySpatialTest.setUp(JtsGeometrySpatialTest.java:1707)
        at junit.framework.TestCase.runBare(TestCase.java:128)
        at junit.framework.TestResult$1.protect(TestResult.java:106)
        at junit.framework.TestResult.runProtected(TestResult.java:124)
        at junit.framework.TestResult.run(TestResult.java:109)
        at junit.framework.TestCase.run(TestCase.java:120)
        at junit.framework.TestSuite.runTest(TestSuite.java:230)
        at junit.framework.TestSuite.run(TestSuite.java:225)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:601)
        at org.apache.maven.surefire.junit.JUnitTestSet.execute(JUnitTestSet.java:96)
        at org.apache.maven.surefire.junit.JUnit3Provider.executeTestSet(JUnit3Provider.java:117)
        at org.apache.maven.surefire.junit.JUnit3Provider.invoke(JUnit3Provider.java:94)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:601)
        at org.apache.maven.surefire.util.ReflectionUtils.invokeMethodWithArray(ReflectionUtils.java:164)
        at org.apache.maven.surefire.booter.ProviderFactory$ProviderProxy.invoke(ProviderFactory.java:110)
        at org.apache.maven.surefire.booter.SurefireStarter.invokeProvider(SurefireStarter.java:175)
        at org.apache.maven.surefire.booter.SurefireStarter.runSuitesInProcessWhenForked(SurefireStarter.java:81)
        at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:68)

{code}

Baris ERGUN added a comment - 22/Jul/13 12:22 PM
Hi Andy;

This morning I had a look from where I left over on Friday. It looks like the fix needs to be applied on store.types.spatial project on org.datanucleus.store.rdbms.mapping.jts2postgis.GeometryRDBMSMapping class getObject method. There is a comment like "// TODO What if this is PGgeometry? is postgis-jdbc-jts responsible for conversion?" on where the error condition is raised. I will implement a private method converting JTSGeometry to PGGeometry on the same class.

Andy Jefferson added a comment - 22/Jul/13 01:30 PM
Hi Baris,
yes, I added that TODO ages ago because I saw the exception and don't understand what each of the different JTS/Postgresql libs do ... I'm guessing "postgis-jdbc-jts" is supposed to do the conversion here (maybe it did for an earlier version of Postgresql but not with latest versions?) but that library was hard to find on the web anyway so likely not a "supported" solution, and certainly couldn't get any info about what it was supposed to provide.
If you provide a converter method then that should do the job perfectly well.

Andy Jefferson added a comment - 23/Jul/13 12:25 PM
I fixed a bug in SpatialRelateMethod3 with invalid use of array index, and added a simple converter method (feel free to improve it). Should sort out the majority of those tests

Baris ERGUN added a comment - 25/Jul/13 01:55 PM
Hi Andy;

1-) I have narrowed down the errors to 3 by adding the convertor method above and adding missing Jts method matchings on pulgin.xml and fixing a bug on SpatialUnionMethod3 yet I have not commited them
2-) I have detected a mistype on org.datanucleus.tests.JtsGeometrySpatialTest on line 710
query.setResult("geom.intersection(Spatial.geomFromText('LINESTRING(25 25, 25 75)', 4326))).isEmpty() AS isEmpty");
I changed to
query.setResult("geom.intersection(Spatial.geomFromText('LINESTRING(25 25, 25 75)', 4326)).isEmpty() AS isEmpty");
Notice the excess paranthesis coming after srid parameter
I wonder why didnt u face any problem on Mysql tests until now?
3-)Thanks for the fix I ve also noticed it.
4-) I started using Datanucleus coding conventions and when I would like to format the classes like JtsGeometrySpatialTest which seems to be old style formatted. If I go forward the diff will show alot of changes, do u have any problem with that....

Andy Jefferson added a comment - 25/Jul/13 02:51 PM
2. I fixed that in SVN trunk. MySQL handling of types isn't as strict as PostgreSQL so maybe thats why it worked there.
4. No problem

Baris ERGUN added a comment - 25/Jul/13 05:07 PM
Hi Andy;

I have attached spatial diff (patch_nucspatial_28_1.diff) and also test project diff (patch_nuctest_x.diff)to the issue. I could have opened a new issue under Test project but anyway I attached it here also. With these patches applied there remains 2 Errors they are about multiple dimensionality errors from postgresql I will read documentation to understand them better.

Baris ERGUN added a comment - 25/Jul/13 05:37 PM
I was puzzled did u apply my patches that I attached straight away? Because on GeometryRDBMSMapping I have added a function called convertGeometry and I see that u implement the same way ??? on 23th of July. Did I give you a patch before or u found the same converter method from Google? Maybe I am getting old :( Anyway I have revised my diffs and reattached them.

Andy Jefferson added a comment - 25/Jul/13 07:17 PM - edited
i added the converter method when I made my comment (23/Jul/13 12:25 PM), something I found by web searching. Your patches should be in SVN trunk now (with the exception of .project/.classpath). Thx

Andy Jefferson added a comment - 31/Jul/13 11:51 AM
Just released 3.2.2 of spatial, so if you find a fix for those tests you mentioned, if it means a change to the spatial plugin (i.e not the test itself) raise a new issue and attach patch there.

Baris ERGUN added a comment - 31/Jul/13 12:54 PM
ok will do so...