postgresql jpa earth_distance query

Question

Thanks for the post. I would like to seek yr advice, for I tried to call the functions in Postgresql using JPA/Hibernate, I kept getting the error message. Could you please help? Thanks.

Following is the code:

String sql = “from Motot u where FUNCTION(‘earth_box’, FUNCTION(‘ll_to_earth’, :lat, :lon), :range) > FUNCTION(‘ll_to_earth’, u.g_Latitude, u.g_Longitude)”;

List resMeter2 = entityManager.createQuery(sqlMeter2, Motor.class)
.setParameter(“lat”, lat)
.setParameter(“lon”, lon)
.setParameter(“range”, radius)
.getResultList();

Here is the error messages:

org.hibernate.exception.SQLGrammarException: could not extract ResultSet

Caused by: org.postgresql.util.PSQLException: ERROR: function function(unknown, double precision, double precision) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 930

Thanks alot

Your JPA provider will have converted that JPQL into SQL for execution in the datastore. Post the SQL it tried to execute (in the JPA providers log). — Neil Stockton, Aug 19 '16 at 17:25
Hi, Neil, Thanks, looks like it is complaining the earth_box function is unknow. Here is the converted SQL :select motor0_.motorID as motorID1_10_, motot0_.Address as Address2_10_, from motor motor0_ where FUNCTION('earth_box', FUNCTION('ll_to_earth', ?, ?), ?)>FUNCTION('ll_to_earth', meter0_.geom_Latitude, meter0_.geom_Longitude) — ggao, Aug 23 '16 at 17:31
The SQL should not have "FUNCTION" in it. It should use the PostgreSQL functions directly. FUNCTION is simply the JPA way of saying invoke this SQL function here. If Hibernate puts FUNCTION in then raise a bug on them, the provider I use handles such things correctly — Neil Stockton, Aug 23 '16 at 17:35
Just try executing the Hibernate "SQL" directly in PostgreSQL and it should fail, hence you can conclude the SQL is invalid, hence an error in JPA provider — Neil Stockton, Aug 23 '16 at 17:48

score 0 · Answer 1 · answered May 14 '18 at 23:38

0

You need install extensions try execute in your sql tool client:

CREATE EXTENSION earthdistance SCHEMA [name of you scheme];

answered May 14 '18 at 23:38

Rodrigo Bermudez

1
1

could you be more specific in your answer. – rawwar May 14 '18 at 23:46

score 0 · Answer 2 · answered Jun 12 '20 at 06:54

 @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 jpa earth_distance query

2 Answers2

Linked