Hibernate spatial query exception, but in the postgres console is working

Question

I'm trying to execute this query in my Java code, using Hibernate and Hibernate Spatial:

Query q = s.createQuery("SELECT c FROM crimes c WHERE ST_DWITHIN(ST_MakeLine(ARRAY['SRID=4326;POINT(-49.30621000000001 -25.515020000000003)','SRID=4326;POINT(-49.30619 -25.515770000000003)','SRID=4326;POINT(-49.306180000000005 -25.5162)','SRID=4326;POINT(-49.305780000000006 -25.5162)']), c.location, 0.0001) = true;");

But, this query causes an Exception:

e = (org.hibernate.hql.internal.ast.QuerySyntaxException) org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE_BRACKET, found ',' near line 1, column 151 [SELECT c FROM com.safecity.server.db.model.EntityCrime c WHERE ST_DWITHIN(ST_MakeLine(ARRAY['SRID=4326;POINT(-49.305820000000004 -25.515330000000002)','SRID=4326;POINT(-49.306200000000004 -25.515340000000002)','SRID=4326;POINT(-49.30619 -25.515770000000003)','SRID=4326;POINT(-49.306180000000005 -25.5162)','SRID=4326;POINT(-49.305780000000006 -25.5162)']), c.location, 0.0001) = true]

I checked the query, and I cannot find the error. But, if I get this same query and execute on postgres console, the query is executed without any error and returns the correct value.

Please, someone can help me? Thanks.

While being at it, simplify your query. `ST_DWITHIN()` already returns `boolean`. No need to append `= true`, that's just noise: `SELECT c FROM crimes c WHERE ST_DWITHIN(ST_MakeLine(ARRAY[ ... ]), c.location, 0.0001)` — Erwin Brandstetter, Oct 02 '16 at 13:41

score 1 · Answer 1 · answered Oct 02 '16 at 13:36

1

You are using native query here in hibernate. For this you have to use the query as below:

Query q = s.createSQLQuery("SELECT c FROM crimes c WHERE ST_DWITHIN(ST_MakeLine(ARRAY['SRID=4326;POINT(-49.30621000000001 -25.515020000000003)','SRID=4326;POINT(-49.30619 -25.515770000000003)','SRID=4326;POINT(-49.306180000000005 -25.5162)','SRID=4326;POINT(-49.305780000000006 -25.5162)']), c.location, 0.0001) = true;");

Use createSQLQuery() instead of createQuery(), if you want to create a db native query instead of HQL.

answered Oct 02 '16 at 13:36

Rohit Gaikwad

3,677
3
17
40

I tried your suggestion, but now is happening other Exception: e = (org.hibernate.MappingException) org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111. Do you know why? – brunoroberto Oct 02 '16 at 15:08
Its a type mapping exception that says the column type cannot be mapped to a database type by hibernate. If you are not able to map the things properly then please add your entire code (pojo class, table structure and main class where you fire your query.) – Rohit Gaikwad Oct 02 '16 at 15:11
It's strange, because I use another query, with the same table and object, and it doesn't throw any exception. But, I was using in this another query the createQuery only, instead of createSQLQuery. – brunoroberto Oct 02 '16 at 15:22
remember, using native queries you will not retrieve an object. instead you will get the column values directly(same as with jdbc simple statement) and not a object. Using createSQLQuery it returns a object, its the HQL query. If you want to use HQL then you need to amend your query in HQL format and make use of createQuery statement. – Rohit Gaikwad Oct 02 '16 at 15:29
a small typo mistake in above comment; createQuery it returns a object – Rohit Gaikwad Oct 03 '16 at 07:26

score 0 · Answer 2 · answered Oct 02 '16 at 15:53

I solved this problem changing the query, for this one:

Query q = s.createQuery("SELECT c FROM crimes c WHERE ST_DWITHIN(ST_GeomFromText('LINESTRING(-49.305820000000004 -25.515330000000002,-49.306200000000004 -25.515340000000002,-49.30619 -25.515770000000003,-49.306180000000005 -25.5162,-49.305780000000006 -25.5162)', 4326), c.location, 0.0001) = true");

I don't know why, but it works.

Thanks for the help.

score 0 · Answer 3 · edited May 23 '17 at 12:19

I have seen similar problems with ARRAY constructors in Hibernate before.

PostgreSQL: Issue with passing array to procedure

Replace with an array literal (and optionally a type cast) to make it work. A simple string literal will avoid various complications in the communication.

You are using the PostGis function ST_MakeLine() taking an array of geometry as input parameter.

geometry ST_MakeLine(geometry[] geoms_array)

So:

Query q = s.createQuery(
  "SELECT c FROM crimes c
   WHERE  ST_DWITHIN(ST_MakeLine('{SRID=4326;POINT(-49.30621000000001 -25.515020000000003)
                                  ,SRID=4326;POINT(-49.30619 -25.515770000000003)
                                  ,SRID=4326;POINT(-49.306180000000005 -25.5162)
                                  ,SRID=4326;POINT(-49.305780000000006 -25.5162)}'::geometry[]), c.location, 0.0001)");

This would also explain why your alternative answer providing a linestring (as string literal!) works as well.

Also simplified the boolean expression in the WHERE clause like I commented. Appending = true is just noise.

Hibernate spatial query exception, but in the postgres console is working

3 Answers3