cidy.long cidy.long - 3 months ago 20
Java Question

PostgreSQL 9.4, use earth_distance in my JPA namedQuery

I had a table site stored my sites information with latitude and longitude.

ozssc=> \d site;
......
site_latitude | double precision |
site_longitude | double precision |
......


I added to my PostgreSQL server 9.4.3

CREATE EXTENSION cube;
CREATE EXTENSION earthdistance;


There are all functions was added to my database and I changed the functions owner to my login user.

When I tried to PSQL to my server and execute:

ozssc=> select * from site s where earth_box('-28.175613','153.52578399999993',100000) @> ll_to_earth(s.site_latitude,s.site_longitude);
ERROR: function earth_box(unknown, unknown, integer) does not exist
LINE 1: select * from site s where earth_box('-28.175613','153.52578...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.


and when I tried to put earth_box function in my named query in JPA as:

@NamedQuery(name = "SiteEntity.findByEarthDistance", query = "SELECT s FROM SiteEntity s WHERE earth_box((ll_to_earth( :positionLatitude, :positionLongitude), :range) @> ll_to_earth(s.siteLatitude,s.siteLongitude))"),


My intelliJ complains there is not function matching??

When I tried to deploy my JAVAEE application to my webLogic 12c, I found error as:

Message icon - Error An error occurred during activation of changes, please see the log for details.
Message icon - Error Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.JPQLException Exception Description: Syntax error parsing [SELECT s FROM SiteEntity s WHERE earth_box((ll_to_earth( :positionLatitude, :positionLongitude), :range) > ll_to_earth(s.siteLatitude,s.siteLongitude))]. [33, 151] The expression is not a valid conditional expression.
Message icon - Error Substituted for missing class Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd) - org.eclipse.persistence.exceptions.JPQLException Exception Description: Syntax error parsing [SELECT s FROM SiteEntity s WHERE earth_box((ll_to_earth( :positionLatitude, :positionLongitude), :range) > ll_to_earth(s.siteLatitude,s.siteLongitude))]. [33, 151] The expression is not a valid conditional expression.


I lost my direction now, Could any one help me please!!

Edit Again

I found there must have some bugs in earth_box function of PostgreSQL 9.4, it can't always get right result set, I have to change it to earth_distance function and use multiple selection projection to make it work well, the newer namedQuery as:

@NamedQuery(name = "SiteEntity.findBySiteLNR", query = "SELECT s, FUNC('earth_distance', FUNC('ll_to_earth',:positionLatitude, :positionLongitude), FUNC('ll_to_earth',s.siteLatitude,s.siteLongitude)) AS dfcl " +
"FROM SiteEntity s WHERE dfcl < :range ORDER BY dfcl"),


I didn't use hibernate, instead of, I use EclipseLink and JPA 2.1, EJB container is Weblogic 12C, EJB specification 3.1.

Answer

After about 4 hours struggling with the issue, I had found out the solutions. Mainly, there as it is a function call from JPA, some tricking has to know.

PSQL statement as:

select * from site s where earth_box(ll_to_earth(-28.175613,153.52578399999993),100000) @> ll_to_earth(s.site_latitude,s.site_longitude);

And JPA namedQuery as:

@NamedQuery(name = "SiteEntity.findByEarthDistance", query = "SELECT s FROM SiteEntity s WHERE FUNC('earth_box', FUNC('ll_to_earth',:positionLatitude, :positionLongitude), :range) > FUNC('ll_to_earth',s.siteLatitude,s.siteLongitude)"),

nested function call in JPA works too, Thank you!

Comments