java - 在 Spring 使用 Haversine 公式获取最近的位置

Question

我有一个实体，它在数据库中有两列，如下所示:

@Column(name = "latitude", nullable = false)
private float currentPositionLatitude;

@Column(name = "longitude", nullable = false)
private float currentPositionLongitude;

我的数据库 (PostgresQl) 中有多个实体条目，所有条目都具有不同的纬度和经度。现在我想给出一个点(纬度、经度)和一个半径，并显示该点附近的所有实体。我首先在 Spring Jpa 中使用这样的 Harversine 公式尝试了它:

   String HAVERSINE_PART = "(6371 * acos(cos(radians(:latitude)) * cos(radians(s.latitude)) *" +
        " cos(radians(s.longitude) - radians(:longitude)) + sin(radians(:latitude)) * sin(radians(s.latitude))))";

    @Query("SELECT currentPositionLatitude,currentPositionLongitude FROM Entity WHERE  '%:HAVERSINE_PART%' < :distance ORDER BY '%:HAVERSINE_PART%' DESC")
      public List<Entity> findEntiryWithLocation(
      @Param("currentPositionLatitude") final float latitude,
      @Param("currentPositionLongitude") final float longitude, @Param("distance") final double distance )

这没有用，所以我尝试了:

    @Query("SELECT s FROM Entity s WHERE " + HAVERSINE_PART + " < :distance ORDER BY "+ HAVERSINE_PART + " DESC")
List<Entity> findEntityWithInDistance(
@Param("latitude") double latitude,
 @Param("longitude") double longitude,
 @Param("distance") double distanceWithInKM );

这也没有用，所以我尝试了:

@Query(value = "SELECT *, earth_distance(ll_to_earth(:latitude, :longitude), ll_to_earth(latitude, longitude)) as distance FROM Car WHERE  (earth_box(ll_to_earth(:latitude, :longitude), :radiusInMeters) @> ll_to_earth(latitude, longitude))ORDER BY distance ASC",
nativeQuery = true)
List<Entity> getPlacesNear ( @Param("latitude") float latitude,
                             @Param("longitude")float longitude,
                             @Param("radiusInMeters") float radiusInMeters);

这编译了但是当我试图在我的 Controller 中输入值时我得到了错误:jdbc 错误:2022 没有方言映射

这里是我的服务和 Controller 方法(数据类型可能已经改变):

public List<Entity>showNearestEntityByDistances(float latitude, float longitude, float distance){
        return EntityRepository.getPlacesNear(latitude,longitude,distance);
    }

和端点:

@GetMapping("/location")
public List<Entity>showEntityNearby(@RequestParam float latitude,@RequestParam float longitude,@RequestParam float distance){
  

    return carService.showNearestEntitysByDistances(latitude,longitude,distance);
}

我已经看过了: https://www.postgresql.org/docs/current/earthdistance.html和 postgresql jpa earth_distance query

但是没有用。有谁知道我做错了什么？谢谢。

Answer 1

以下对我有用:

首先，运行:

 create extension cube;

 create extension earthdistance; 

这会安装扩展，除其他外，无需安装 PostGIS 即可启用基于位置的查询

然后，不要将纬度和经度存储在单独的列中，而是将它们一起存储在“点”类型的列中，请记住首先是经度，而不是您期望的纬度，例如

  create table Entity (city varchar(100), location point);

并存储您的数据，例如

insert into places(name, location) values ('lambertville', point((74.9429,40.3659)));
    
insert into places(name, location) values ('ny', point(74.0060, 40.7128));
    
insert into places(name, location) values ('phila', point( 75.1652, 39.9526));

(请注意，新泽西州兰伯特维尔大约位于费城和纽约之间的中间位置)。

然后，例如，如果您想选择与另一个实体之间的距离小于一定距离的实体，您可以执行以下操作:

select * from places 
 where 
location <@> (select location from places where name = 'ny') < 70  
  and name != 'ny';

或者，如果您已经有了坐标，并且想要找到一定距离内的实体，

select * from places 
 where 
location <@> point(74.0060, 40.7128) < 70    
  and name != 'ny';

我没有检查这些查询是否可以适应 JPA @Query 注释，YMMV。