2

I am facing a problem with the earthdistance extension of PostgreSQL.

Following this guide, I have added the following extensions:

CREATE EXTENSION cube;
CREATE EXTENSION earthdistance;

I tried to run the following SQL query from Postgres command line and everything works fine:

SELECT * FROM table 
WHERE earth_box(ll_to_earth(40.463444, -3.637716), 2000) @> ll_to_earth(table.lat, table.lon)

When I try to run the same query in HQL, I get the following exception:

org.hibernate.QueryException: unexpected char: '@' [Select 1 from table WHERE earth_box(ll_to_earth(40.463444, -3.637716), 2000) @> ll_to_earth(table.lat, table.lon)]
! at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:250) ~[logistics-statemachine-1.0.jar:na]
! at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158) ~[logistics-statemachine-1.0.jar:na]
! at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:131) ~[logistics-statemachine-1.0.jar:na]
! at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:93) ~[logistics-statemachine-1.0.jar:na]
! at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:167) ~[logistics-statemachine-1.0.jar:na]
! at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301) ~[logistics-statemachine-1.0.jar:na]
! at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236) ~[logistics-statemachine-1.0.jar:na]
! at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1836) ~[logistics-statemachine-1.0.jar:na]
...

which tells me that HQL dialect does not recognise the installed Postgres extension.

Was anybody able to use this operator with HQL?

Giu87
  • 96
  • 5

1 Answers1

0

The exception will be gone when the @ is taken off. Have you turned on the hibernate log to see the converted sql.

ggao
  • 21
  • 2