Hi,

How to use operators like "&&" in postgis queries?

Hibernate version: 3

Name and version of the database you are using:
Postgres 8.1 Plus Postgis 1.0

The generated SQL (show_sql=true): Never generates it as desired...


In Postgis you can perform geometrical queries like this one:
select * from spatial as s1 where s1._Geom && setSRID( cast('BOX3D(1 2, 3 4)'as box3d) ,4326)

The && operator means for example "geometry_overlap", both operands are geometries.

I haven't a clue how to get the && operator in a clean way into the hibernate queries. I tested the following options:
1) Writing '&&' directly in the query does not work, Hibernate does not like it.
2) Substituting using the property "hibernate.query.substitutions" does not work because apparently only letters can be substituted.
3) Using a SQL query instead of an Hibernate Query would work, but unfortunatelly we defined a joined-subclass of spatial and the generated SQL is not valid, because there are references to aliases called "s1_1_" and "s1_2_" (for the joined-subclasses?), that aren't defined anywhere in the query.

The perfect solution would be an hibernate escape character so that we can just send any operator to sql. Why is this not possible?

Anybody has a good idea, that is straight to use? I would prefer not to program new hibernate classes just to circumvent the operators.

Any help is appreciated

Jean-Pierre Quirion

Replace operators with functions, but SQL query must work without problems too.

Hi Baliukas,
thanks for the reply.

The SQL query problem is strange, I found some posts in this forum with the same problem but no solution. It was said that Hibernate 3 does have better support for SQL Queries using joined-subclasses. My version is still 3.0beta4b, would an update help?

I already replaced the postgres operator by a postgres function (example: "&&" by "geometry_replace()". It works. Unfortunatelly the function does not use the indexes so that queries become too slow. That is a postgres/postgis problem, not a hibernate problem that I can't solve.

My dream is still to have an escape character in HQL, but this seems difficult to implement. We get the same problem with the postgres cast operator which is "::". I am sure a lot of people would appreciate an escape character hence there would be less workaround and more straight-forward coding.

Is there a document where we can find all valid characters (and character combinations) that Hibernate considers valid and that are sent unalterted to the JDBC?

Jean-Pierre

try to create index using function, postgres supports it.
http://www.postgresql.org/docs/7.4/stat ... ional.html


And report problem related to SQL queries to jira, it looks like a bug.