PostgreSQL 9.4, use earth_distance in my JPA namedQuery

Question

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.

score 1 · Accepted Answer · answered Jul 27 '15 at 21:56

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!

Please tell me why i am still getting all my fields from database even they are not in radius? — anduplats, Oct 07 '19 at 13:43

Adam Horvath · Answer 2 · 2015-07-27T13:30:49.617

0

I think the HINT tells it all: You need to add explicit type casts.

You can do this by adding double semicolon and the type you want to cast it into. I.e.

'-28.175613'::text

Or whatever the function is expecting.

edited Jul 27 '15 at 13:30

answered Jul 27 '15 at 11:59

Adam Horvath

1,249
1
10
25

score 0 · Answer 3 · answered Jul 18 '17 at 21:42

It's highly recommended to use FUNCTION instead of FUNC (formerly implementation: http://www.eclipse.org/eclipselink/documentation/2.6/jpa/extensions/jpql.htm#func). EclipseLink supports order by and group by FUNCTIONS, whereas it does not work with FUNC.

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

neshant sharma · Answer 4 · 2020-06-12T06:51:56.253

@Query(
    """
            FROM Place pl
            WHERE 
            FUNCTION('cube_contains',
             FUNCTION('earth_box',
              FUNCTION('ll_to_earth', :latitude, :longitude),
              :radiusInMeters),
             FUNCTION('ll_to_earth', pl.latitude, pl.longitude) 
              ) = TRUE
            """)
fun getPlacesNear(
    pageable: Pageable,
    @Param("latitude") latitude: Double,
    @Param("longitude") longitude: Double,
    @Param("radiusInMeters") radiusInMeters: Int
): Page<Place>

This works like a charm with paging and distance support

For some weird reason

@Query(
        """
                FROM Place pl
                WHERE 
                 FUNCTION('ll_to_earth', pl.latitude, pl.longitude)                    
                 < FUNCTION('earth_box', FUNCTION('ll_to_earth', :latitude, :longitude), :radiusInMeters)
                """)

https://www.postgresql.org/docs/9.5/cube.html#CUBE-GIST-OPERATORS

For some weird reason '<' does not work expectedly because the '<' is not interpreted correctly in postgres and is giving wrong results.

For postgres you need '@<' symbol which is not interpreted by spring JPA so I had to switch to using cube_contains function

I could have used native_query = true but that would not give me pagination support that is why I switched to JPA query.

So if you don't need pagination you could use the below.

@Query(
    """SELECT *, earth_distance(ll_to_earth(:latitude, :longitude), ll_to_earth(latitude, longitude)) as distance
            FROM place
            WHERE  (earth_box(ll_to_earth(:latitude, :longitude), :radiusInMeters) @> ll_to_earth(latitude, longitude))
            ORDER BY distance ASC
            """, nativeQuery = true)
fun getPlacesNear(
    @Param("latitude") latitude: Double,
    @Param("longitude") longitude: Double,
    @Param("radiusInMeters") radiusInMeters: Int
): List<Place>?

PostgreSQL 9.4, use earth_distance in my JPA namedQuery

4 Answers4