how to do index scan (&& operator) via HQL?

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

how to do index scan (&& operator) via HQL?

Samuel Gendler
The tutorial gives very little clue about how to write HQL or use criteria queries to utilize geometry functionality.  The criteria appear to only be useful if passing location information as a constant to a where clause.  There doesn't seem any obvious way to filter the rows of one table based on the joined rows of another table via geometry.

This SQL query works in psql:

select p.id, p.name 
  from thermal_space t 
  join floor f on t.floor = f.id 
  join epoint p on p.floor = f.id 
 where t.id = 5 
   and p.location && f.perimeter and Contains(f.perimeter, p.location);

But this HQL does not - it complains about the && operator:

org.hibernate.QueryException: unexpected char: '&'


SELECT p 
  FROM com.ecorithm.domain.ThermalSpace s 
  join s.floor f 
  join f.points p 
 where s.id = :spaceId 
   and p.location && s.perimeter and Contains(s.perimeter, p.location)

Anyone have any idea how to accomplish filtering like that via hibernate spatial?

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
Reply | Threaded
Open this post in threaded view
|

Re: how to do index scan (&& operator) via HQL?

shane
Instead of the PostGIS specific && operator, you need to use the HQL functions that Hibernate Spatial has registered. This is untested, but try:

SELECT p 
  FROM com.ecorithm.domain.ThermalSpace s 
  join s.floor f 
  join f.points p 
 where s.id = :spaceId 
   and intersects( p.location, s.perimeter) = TRUE
   and contains(s.perimeter, p.location) = TRUE


I'm not aware of any published list of the registered HQL functions, but you can look at the source to see which are registered. Here's the PostGIS provider's dialect from the trunk:

-Shane


On Tue, Apr 26, 2011 at 2:48 PM, Samuel Gendler <[hidden email]> wrote:
The tutorial gives very little clue about how to write HQL or use criteria queries to utilize geometry functionality.  The criteria appear to only be useful if passing location information as a constant to a where clause.  There doesn't seem any obvious way to filter the rows of one table based on the joined rows of another table via geometry.

This SQL query works in psql:

select p.id, p.name 
  from thermal_space t 
  join floor f on t.floor = f.id 
  join epoint p on p.floor = f.id 
 where t.id = 5 
   and p.location && f.perimeter and Contains(f.perimeter, p.location);

But this HQL does not - it complains about the && operator:

org.hibernate.QueryException: unexpected char: '&'


SELECT p 
  FROM com.ecorithm.domain.ThermalSpace s 
  join s.floor f 
  join f.points p 
 where s.id = :spaceId 
   and p.location && s.perimeter and Contains(s.perimeter, p.location)

Anyone have any idea how to accomplish filtering like that via hibernate spatial?

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users




--
Shane StClair
Software Engineer
Axiom Consulting & Design
523 W 8th Ave
Suite 104
Anchorage, AK 99501
http://www.axiomalaska.com

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
Reply | Threaded
Open this post in threaded view
|

Re: how to do index scan (&& operator) via HQL?

Samuel Gendler


On Tue, Apr 26, 2011 at 4:06 PM, Shane StClair <[hidden email]> wrote:
Instead of the PostGIS specific && operator, you need to use the HQL functions that Hibernate Spatial has registered. This is untested, but try:

SELECT p 
  FROM com.ecorithm.domain.ThermalSpace s 
  join s.floor f 
  join f.points p 
 where s.id = :spaceId 
   and intersects( p.location, s.perimeter) = TRUE
   and contains(s.perimeter, p.location) = TRUE


I'm not aware of any published list of the registered HQL functions, but you can look at the source to see which are registered. Here's the PostGIS provider's dialect from the trunk:

There was a list published to the mailing list a couple of years ago by Karel Maesen:

They are (almost) the same as specified in the OGC specification.   
Here they are: 

dimension 
srid 
envelope 
astext 
asbinary 
isempty 
issimple 
boundary 
overlaps 
intersects 
equals 
contains 
crosses 
disjoint 
touches 
within 
relate 
distance 
buffer 
convexhull 
difference 
intersection 
symdifference 
geomunion (named thus because 'union' is a reserved word in sql/hql) 
extent 

Most of these also work on Oracle Spatial. 

However, I tried removing the && operator and I'm still getting complaints from my code

org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: ( near line 1, column 115 [SELECT p FROM com.ecorithm.domain.ThermalSpace s join s.floor f join f.points p where s.id = :spaceId and contains(s.perimeter, p.location)]


With regard to the && operator and bounding box overlap, Karel wrote this in 2009:

That is true, except that this is no longer relevant for later   
versions of Postgis (>= 1.3). The Postgis documentation states that:   
"As of PostGIS 1.3.0, most of the Geometry Relationship Functions,   
with the notable exceptions of ST_Disjoint and ST_Relate, include   
implicit bounding box overlap operators." So using the '&&' operator   
has become superfluous. 

However, the documentation for 1.5.2 still explicitly says that one should use the bounding box operator in combination with function calls in order to make use of the index.  I'm not quite sure who to believe.  There's nothing in explain analyze output to indicate whether bounding box comparison is being used when using the function on its own at all, but that's not surprising if it happens inside the function call.

My persistence.xml file does correctly list the Postgis dialect as follows:

<persistence-unit name="persistenceUnit" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <properties>
            <property name="hibernate.dialect" value="org.hibernatespatial.postgis.PostgisDialect"/>
            <!-- value="create" to build a new database on each run; value="update" to modify an existing database; value="create-drop" means the same as "create" but also drops tables when Hibernate closes; value="validate" makes no changes to the database -->
            <property name="hibernate.hbm2ddl.auto" value="validate"/>
            <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
            <property name="hibernate.connection.charSet" value="UTF-8"/>
            <!-- Uncomment the following two properties for JBoss only -->
            <!-- property name="hibernate.validator.apply_to_ddl" value="false" /-->
            <!-- property name="hibernate.validator.autoregister_listeners" value="false" /-->
        </properties>
    </persistence-unit>
</persistence>

 

-Shane


On Tue, Apr 26, 2011 at 2:48 PM, Samuel Gendler <[hidden email]> wrote:
The tutorial gives very little clue about how to write HQL or use criteria queries to utilize geometry functionality.  The criteria appear to only be useful if passing location information as a constant to a where clause.  There doesn't seem any obvious way to filter the rows of one table based on the joined rows of another table via geometry.

This SQL query works in psql:

select p.id, p.name 
  from thermal_space t 
  join floor f on t.floor = f.id 
  join epoint p on p.floor = f.id 
 where t.id = 5 
   and p.location && f.perimeter and Contains(f.perimeter, p.location);

But this HQL does not - it complains about the && operator:

org.hibernate.QueryException: unexpected char: '&'


SELECT p 
  FROM com.ecorithm.domain.ThermalSpace s 
  join s.floor f 
  join f.points p 
 where s.id = :spaceId 
   and p.location && s.perimeter and Contains(s.perimeter, p.location)

Anyone have any idea how to accomplish filtering like that via hibernate spatial?

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users




--
Shane StClair
Software Engineer
Axiom Consulting & Design
523 W 8th Ave
Suite 104
Anchorage, AK 99501
http://www.axiomalaska.com

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users



_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
Reply | Threaded
Open this post in threaded view
|

Re: how to do index scan (&& operator) via HQL?

Samuel Gendler


On Tue, Apr 26, 2011 at 4:20 PM, Samuel Gendler <[hidden email]> wrote:

However, I tried removing the && operator and I'm still getting complaints from my code

org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: ( near line 1, column 115 [SELECT p FROM com.ecorithm.domain.ThermalSpace s join s.floor f join f.points p where s.id = :spaceId and contains(s.perimeter, p.location)]




Looks like hibernate's query parser isn't willing to treat 'contains(x,y)' as an expression.  It requires 'contains(x,y) = true' before it considers it to be valid. Everything works now.

--sam


_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users