java.sql.SQLException: Cannot find a CRS

classic Classic list List threaded Threaded
3 messages Options
rsn
Reply | Threaded
Open this post in threaded view
|

java.sql.SQLException: Cannot find a CRS

rsn
hi there,

i'm hoping an H2GIS developer, or more experienced users can help me out here.

i'm consistently getting this exception (formatted for readability):

...SQLRuntimeException: org.h2.jdbc.JdbcSQLException:
Cannot find a CRS; SQL statement:
SELECT "ID", "V", ST_Transform(ST_Centroid("G"), 3857) AS "G" FROM "P68004L2"
WHERE ST_Intersects(ST_GeomFromText('POLYGON ((151.09232745846617 ...))', 4326), "G");

my SQLRuntimeException is a sub-class of RuntimeException that wraps an SQLException so i can use code that may throw such an exception in Java 8 Lambda functions.  the cause in the stack trace is...

Caused by: java.sql.SQLException: Cannot find a CRS
        at org.h2gis.functions.spatial.crs.ST_Transform.ST_Transform(ST_Transform.java:94)
        at sun.reflect.GeneratedMethodAccessor331.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.h2.engine.FunctionAlias$JavaMethod.getValue(FunctionAlias.java:481)
        at org.h2.expression.JavaFunction.getValue(JavaFunction.java:38)
        at org.h2.expression.Alias.getValue(Alias.java:36)
        at org.h2.command.dml.Select.queryFlat(Select.java:549)
        at org.h2.command.dml.Select.queryWithoutCache(Select.java:655)
        at org.h2.command.dml.Query.query(Query.java:341)
        at org.h2.command.dml.Query.query(Query.java:309)
        at org.h2.command.dml.Query.query(Query.java:36)
        at org.h2.command.CommandContainer.query(CommandContainer.java:110)
        at org.h2.command.Command.executeQuery(Command.java:201)
        ... 3 more

looking at the source of ST_Transform shows the exception is being thrown b/c the geometry argument has no CRS.  however the same SQL works w/ other similar tables.

just to be sure, i modified the SQL that created this table to ST_SetSRID on the geometry column when specifying it like so...

CREATE TABLE "P68004L2" AS
SELECT "R"."ID" AS "ID",
  SUM(CAST("S"."C1" AS DOUBLE)) AS "V",
  ST_SetSRID(ST_Buffer("R"."G", 0.04491576420597607), 4326) AS "G"
FROM "P68004L1" AS "R"
LEFT JOIN "P68004L1" AS "S"
ON ST_DWithin("R"."G", "S"."G", 0.04491576420597607)
GROUP BY "R"."ID"

this didn't change the outcome.

any help in finding the cause + work-around is much appreciated.

TIA + cheers;
rsn
Reply | Threaded
Open this post in threaded view
|

Re: java.sql.SQLException: Cannot find a CRS

nicolas-f
Administrator
Hi,

Sorry for the delay.

The function ST_Transform want on the first argument a geometry that contain a SRID value. It seems that the geometry "G" does not contain the SRID value.

So this issue can be solved by using the function ST_SETSRID(GEOM, SRID ):

 SELECT "ID", "V", ST_Transform(ST_SETSRID(ST_Centroid("G"), 4326), 3857) AS "G" FROM "P68004L2"
WHERE ST_Intersects(ST_GeomFromText('POLYGON ((151.09232745846617 ...))', 4326), "G");

best regards,

Nicolas Fortin
rsn
Reply | Threaded
Open this post in threaded view
|

Re: java.sql.SQLException: Cannot find a CRS

rsn
hi Nicolas,

re-writing the SQL as you suggested solved the problem!

thanks + cheers;
rsn