| JPQL and Spatial Queries [message #388108] |
Fri, 22 May 2009 11:44  |
 Oliver Snowden
Messages: 1
Registered: July 2009 |
Junior Member |
|
|
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.
Rough Example:
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>)
AND ST_Intersects(a.geometry,selectedGeography.geometry)
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
Eclipselink.
http://edocs.bea.com/kodo/docs41/full/html/ref_guide_enterpr ise_queryext.html
Can anyone offer some suggestions?
Many thanks.
|
|
|
|
Powered by
FUDForum. Page generated in 0.02612 seconds
] 
Search
Help
Register
Login
Home
