Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » JPQL and Spatial Queries
JPQL and Spatial Queries [message #388108] Fri, 22 May 2009 07:44 Go to next message
Oliver Snowden is currently offline 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.
Re: JPQL and Spatial Queries [message #388119 is a reply to message #388108] Mon, 25 May 2009 13:45 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

You may be able to define the function using an EclipseLink
ExpressionOperator and use it in an Expression query.

I agree it would be useful to allow access the EclipseLink
ExpressionOperators in JPQL, please log a bug for this.

You could also continue to use native SQL.

---
James
http://www.nabble.com/EclipseLink---Users-f26658.html


James : Wiki : Book : Blog : Twitter
Previous Topic:Custom SQL Functions?
Next Topic:Using XMLType in Oracle XE-Database with Eclipselink
Goto Forum:
  


Current Time: Tue Jul 22 11:45:22 EDT 2014

Powered by FUDForum. Page generated in 0.01870 seconds