Issue Details (XML | Word | Printable)

Key: NUCSPATIAL-29
Type: Bug Bug
Status: Closed Closed
Resolution: Won't Fix
Priority: Major Major
Assignee: Unassigned
Reporter: Baris ERGUN
Votes: 0
Watchers: 0
Operations

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

Using Spatial methods for ORDER BY evaluation causes exception

Created: 28/Mar/13 03:54 PM   Updated: 31/Jul/13 04:53 PM   Resolved: 05/Apr/13 08:03 PM
Component/s: None
Affects Version/s: 3.2.0.release
Fix Version/s: None

Environment: RHEL 5.4, Postgresql 9.2 Postgis 2.0.2

Datastore: PostgreSQL
Severity: Development


 Description  « Hide
With the below JDOQL query

{code}
                        Query query =
pm.newQuery ("SELECT FROM com.telenity.canvas.location.smartdeals.model.CompanyPoiPoint"
+ " WHERE Spatial.distance(point,:point,:useSpheroid ) < :distance" +
" ORDER BY Spatial.distance(point,:point,:useSpheroid ) asc");
query
.declareImports ("import com.telenity.canvas.location.smartdeals.model.CompanyPoiPoint;");
query.setClass (CompanyPoiPoint.class);

HashMap<String, Object> parameterMap = new HashMap<String, Object> ();
parameterMap.put ("point", searchParameters.getPoint ());
parameterMap.put ("useSpheroid", true);
parameterMap.put ("distance", searchParameters.getDistanceInKm () * 1000);
                        companyPoiResults = (List<CompanyPoiPoint>) query.executeWithMap (parameterMap);


{code}

I get the following exception:

org.postgresql.util.PSQLException: No value specified for parameter 4.
at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:176)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:244)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:510)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:386)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.datanucleus.store.rdbms.ParamLoggingPreparedStatement.executeQuery(ParamLoggingPreparedStatement.java:381)
at org.datanucleus.store.rdbms.SQLController.executeStatementQuery(SQLController.java:504)
at org.datanucleus.store.rdbms.query.JDOQLQuery.performExecute(JDOQLQuery.java:637)
at org.datanucleus.store.query.Query.executeQuery(Query.java:1786)
at org.datanucleus.store.query.Query.executeWithMap(Query.java:1690)
at org.datanucleus.api.jdo.JDOQuery.executeWithMap(JDOQuery.java:334)

where as with the native sql below query I test to be successfull.
{code}
Query query =
pm.newQuery (
"javax.jdo.query.SQL",
"SELECT * FROM \"SD_COMPANY_POINTS\""
+ " WHERE st_distance(st_geogfromwkb(\"POI_POINT\"),st_geographyfromtext(:point),:useSpheroid ) < :distance"
+ " ORDER BY st_distance(st_geogfromwkb(\"POI_POINT\"),st_geographyfromtext(:point),:useSpheroid ) asc");

query.setClass (CompanyPoiPoint.class);

HashMap<String, Object> parameterMap = new HashMap<String, Object> ();
parameterMap.put ("point", "SRID=4326;POINT(28.49091 77.080109)");
parameterMap.put ("useSpheroid", true);
parameterMap.put ("distance", 4000);
companyPoiResults = (List<CompanyPoiPoint>) query.executeWithMap (parameterMap);
{code}

Since I have wrote the Spatial.distance extension for Postgresql I will deal with the problem and try to find the root cause and report the fix.


Sort Order: Ascending order - Click to sort in descending order
Baris ERGUN added a comment - 28/Mar/13 03:59 PM
NUCRDBMS-108 has reported an issue to use ORDER BY for Spatial.distance method. This problem happened on Mysql as I understood from forum http://www.jpox.org/servlet/forum/viewthread_thread,5421. This issue gave me the clue that it is possible to use Spatial methods with ORDER BY, so as I said I will try to solve the problem with Postgres.

Baris ERGUN added a comment - 28/Mar/13 04:15 PM
Sorry I think the issue can be moved to RDBMS project.

Andy Jefferson added a comment - 29/Mar/13 09:59 AM
How can you say its a problem in store.rdbms when I see no definition of where the problem lies ? Maybe the SQL Method is doing something wrong? For example, can you do ORDER BY of *any* method ? Yes. So what is the defining factor in when you have the problem ? use of parameters ? just a specific method ? something else ?

Baris ERGUN added a comment - 29/Mar/13 02:20 PM - edited
Hi Andy;

I still didnt make any concrete judgment where the problem is. I started debugging the problem few hours ago. I have found that the JDOQL is being matched to the below prepared statement

SELECT "A1"."COMPANY_ID","A1"."CREATE_DATE","A1"."ID","A1"."MODIFY_DATE","A1"."POI_STREET_ADDRESS","A1"."POI_DESC","A1"."POI_NAME","A1"."POI_TYPE","A0"."POI_POINT","A0"."VERSION",st_distance(st_geogfromwkb('SRID=4326;POINT(28.49091 77.080109)'),st_geogfromwkb('1'),TRUE) AS NUCORDER0 FROM "SD_COMPANY_POINTS" "A0" INNER JOIN "SD_COMPANY_POIS" "A1" ON "A0"."ID" = "A1"."ID" WHERE st_distance(st_geogfromwkb("A0"."POI_POINT"),st_geogfromwkb('4000.0'),?) < ? ORDER BY NUCORDER0

which has problems as you can see. at the moment I only suspect from rdbms because I discovered that sql in JDOQLQuery.class. You are right its early to make to judgment for the moving of the issue. Lets keep it here until provide all the information.

Baris ERGUN added a comment - 31/Mar/13 09:59 PM - edited
As parameters are only used on HAVING and WHERE clauses, it was a mistake trying to use parameters for ORDER BY. I realised that the core module was discarding parameters for ORDER BY, and rdbms classes were right in not handling such parameters for ORDER BY. It was a good and helpful debuging session for me to understand some of the internals of datanucleus access platform and to remember parameter usage in sqls.

So in order to use the Spatial.distance method for ORDER BY clause, I decided to use embedded strings as illustrated below.
Spatial.distance(point,'SRID=4326;POINT(28.49091 77.080109)',true ).

So the first argument is the persisted member attribute of org.postgis.Point type of my persistent class; which is in sql terms the geometry type of column of the table. That is being processed as a PrimaryExpression by datanucleus. The second argument is the String presentation of org.postgis.Point object and the third argument is boolean indicating if the calculation should be done as spheroid or sphere.

Using Spatial.geogFromText which I added for Postgresql I managed to write the above JDOQL query as

SELECT FROM CompanyPoiPoint"
+ " WHERE Spatial.distance(Spatial.geogFromWKB(point),Spatial.geogFromWKB(:point),:useSpheroid ) < :distance" +
" ORDER BY Spatial.distance(Spatial.geogFromWKB(point),Spatial.geogFromText(<strRepresentaionOfPoint>),true ) asc"

This issue can be closed.