[FIXED] @Query: function ll_to_earth(double precision, double precision) does not exist

Issue

I am trying to implement the following query in order to fetch places within a certain radius (in meters) given a location (lat,lng):

@RepositoryRestResource(collectionResourceRel = "places", path = "places")
public interface PlaceRepository extends JpaRepository<PlaceEntity, Long> {

    @Query(value = "" +
            "SELECT p " +
            "FROM PlaceEntity p " +
            "WHERE earth_distance( " +
            "   ll_to_earth(p.latitude, p.longitude), " +
            "   ll_to_earth(latitude, longitude) " +
            ") < radius")
    List<PlaceEntity> findByLocationAndRadius(@Param("latitude") Float latitude,
                                              @Param("longitude") Float longitude,
                                              @Param("radius") Integer radius);
}

However, running it will throw:

Caused by: org.postgresql.util.PSQLException: ERROR: function ll_to_earth(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: 343
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2284)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2003)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:200)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:424)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:161)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
    ... 76 more

I have also tried to change set nativeQuery = true as well as changing the @Query to

@Query(value = "" +
        "SELECT p " +
        "FROM PlaceEntity p " +
        "WHERE FUNCTION('earth_distance', " +
        "   FUNCTION('ll_to_earth', p.latitude, p.longitude), " +
        "   FUNCTION('ll_to_earth', latitude, longitude) " +
        ") < radius")

with the same result.

What is the right syntax for this?

Solution

From https://johanndutoit.net/searching-in-a-radius-using-postgres/ I found that I have to install some extensions:

Start psql shell:

psql postgres -h localhost -d <database-name>

Execute:

<database-name>=# CREATE EXTENSION cube;
<database-name>=# CREATE EXTENSION earthdistance;

Further, set nativeQuery = true and reference parameters correctly:

@Query(value = "" +
        "SELECT * " +
        "FROM place " +
        "WHERE earth_distance( " +
        "   ll_to_earth(place.latitude, place.longitude), " +
        "   ll_to_earth(:latitude, :longitude) " +
        ") < :radius", nativeQuery = true)
List<PlaceEntity> findByLocationAndRadius(@Param("latitude") Float latitude,
                                          @Param("longitude") Float longitude,
                                          @Param("radius") Integer radius);

Note: This can get slow. The link shows how to improve the performance if necessary.

Answered By - Stefan Falk
Answer Checked By - Pedro (JavaFixing Volunteer)