Skip to main content

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 11:44 Go to next message
Oliver Snowden is currently offline Oliver SnowdenFriend
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 = ?<europe> AND 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. 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 17:45 Go to previous message
James Sutherland is currently offline James SutherlandFriend
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 : Wiki : Book : Blog : Twitter
Previous Topic:Custom SQL Functions?
Next Topic:Horizontal partitions/Shards with EclipseLinks and JPA 2.0
Goto Forum:

Current Time: Sun Sep 27 00:28:32 GMT 2020

Powered by FUDForum. Page generated in 0.04942 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top