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
– Erwin Brandstetter, Commented Oct 2, 2016 at 13:41

Rohit Gaikwad · Accepted Answer · 2016-10-02 13:36:14Z

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 2, 2016 at 13:36

Rohit Gaikwad

3,9443 gold badges21 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

brunoroberto Over a year ago

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?

Rohit Gaikwad Over a year ago

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.)

brunoroberto Over a year ago

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.

Rohit Gaikwad Over a year ago

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 Over a year ago

a small typo mistake in above comment; createQuery it returns a object

brunoroberto · Accepted Answer · 2016-10-02 15:53:27Z

0

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.

answered Oct 2, 2016 at 15:53

brunoroberto

113 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:19:26Z

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.

Collectives™ on Stack Overflow

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

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related