hibernate - @Query : function ll_to_earth(double precision, double )不存在

Question

我正在尝试实现以下查询，以便在给定位置 (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);
}

但是，运行它会抛出:

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

我还尝试更改设置 nativeQuery = true 以及将 @Query 更改为

@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")

同样的结果。

正确的语法是什么？

Answer 1

来自 https://johanndutoit.net/searching-in-a-radius-using-postgres/我发现我必须安装一些扩展:

启动psql shell:

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

执行:

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

进一步设置nativeQuery = true并正确引用参数:

@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);

注意:这可能会变慢。 link展示了如何在必要时提高性能。