|JPQL and Spatial Queries [message #388108]
||Fri, 22 May 2009 11:44
| Oliver Snowden
Registered: July 2009
Hi folks, we have some pretty big JPQL queries (many filters) with |
count/pagination etc. Our data has a spatial component (PostGIS) and so
we need to use some of the native filter functionality, such as
ST_Intersects(geometry1, geometry2); think two geographies overlap.
Currently, we are completing this with two queries the first restricts
the resultset with JPQL/normal filters and the second takes the first
result set and creates a native query using ST_Intersects and an IN to
consider only the eligible results from the first query.
Assume that we have different types of asset (buildings, plant, staff)
around the world and we want to find a certain asset matching n number of
attributes in, say, Europe.
Query 1 (JPQL)
SELECT a FROM Asset a WHERE <many filters>
(Exception if too many results to consider for second query)
Query 2 (Native)
SELECT a FROM Asset a, Geography selectedGeography
WHERE selectedGeography.id = ?<europe> AND a.id IN (<first asset ids>)
Clearly we c[sh]ould just use a single native query but then we lose the
benefits of JPQL. Switching the query the other way does not help because
ST_Intersects is a very costly operation...and should be the last filter.
I have little experience of kodo/OpenJPA but there is reference to JPQL
extensions (possibly not implemented). I guess that is what I am after in
Can anyone offer some suggestions?
Powered by FUDForum
. Page generated in 0.01753 seconds