如何查询 PostgreSQL 数据库以获取特定坐标半径 10 公里内的所有点

Question

我有一个商店数据库，其中保存了这些商店的坐标。我想获取半径10公里范围内的商店列表，但我不确定如何编写Postgres查询语句，因为我正在使用Postgres数据库。

我的数据库：

我正在尝试将查询添加到SpringBoot地理位置微服务中：

Repository代码：

@Repository
public interface SellerGeolocationRepository extends CrudRepository<Seller_Geolocation, Long> {

    @Query(value="SELECT * FROM seller_geolocation_ms WHERE 
   // get coordinates that are in the vicinity
", nativeQuery = true)
        public Set<Seller_Geolocation> findAllSellersInRange(double longitude, double latitude);

    }

卖家对象：

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "seller_geolocation_ms")
public class Seller_Geolocation {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private static final long serialVersionUID = 2L;

    private double latitude;
    private double longitude;
    private String country;
    private String city;
    private String zipCode;
    private String town;
    private String address;
    private Long sellerId;
}

- Erent

---

https://gis.stackexchange.com/questions/77072/return-all-results-within-a-30km-radius-of-a-specific-lat-long-point - ipave

我之前不知道com.vividsolutions.jts.geom.Point这个类，如果我要使用它，如何进行查询呢？ - Erent

你需要使用 hibernate-spatial 并安装 postgis。我将会在 spring-boot 中发布一个使用它们的答案。 - Omid

@Omid，你有使用hibernate-spatial和安装postgis的示例吗？我已经安装了它们，但似乎找不到一个好的解释来使用它。 - Erent

你尝试过使用 Criteria Queries 吗？（https://www.baeldung.com/hibernate-criteria-queries） - Nikhil

Answer 1

您可以使用haversine公式，使用您当前的坐标（目标纬度_deg/经度）和列名称（latitude_deg，longitude_deg），两者都以度为单位表示。

SELECT * 
FROM myTable
WHERE acos(
       sin(radians(target_latitude_deg)) 
         * sin(radians(latitude_deg)) 
       + cos(radians(target_latitude_deg)) 
         * cos(radians(latitude_deg)) 
         * cos( radians(target_longitude_deg)
           - radians(longitude_deg))
       ) * 6371 <= 10;

或者，可以看一下PostGIS扩展

SELECT * 
FROM MyTable 
WHERE ST_Dwithin(geom, st_point(target_longitude, target_latitude,10000);

- JGH

---

我不确定我理解Haversine公式以及如何从我的数据库中传递当前坐标和查询经纬度。 - Erent

在这种情况下，这不是解决问题的好方法。使用像Spring Boot结合Hibernate-Spatial这样的框架的重点不是涉及地理空间公式的低级细节。OP应该使用这些框架的内置功能，而不是原始的SQL查询。 - Omid

@Omid 好的，虽然没有 PostGIS，但我非常好奇它如何被框架利用。 - JGH

@Omid :-/ 所以这对于 OP 来说要么是一个重要的变化（最好的情况），要么是提供的原始 SQL 替代方案。 - JGH

为了避免进行全面搜索，首先在以某个点为中心的边长为20公里的范围内搜索点。这种搜索可以使用普通索引，并将返回一个更小的记录集，数据库将使用此表达式评估这些记录。 - aschoerk

Answer 2

为了使这个工作起来，您需要安装postgis:

brew install postgis

之后，您需要在您的postgres数据库"seller_geolocation_ms"上安装postgis：

   1. $ sudo -i -u postgres
   2. $ psql
   3. postgres=# \c seller_geolocation_ms;
   4. seller_geolocation_ms=# CREATE EXTENSION postgis;
   5. \q

完成这些步骤后，postgis应该已经安装在您的数据库上了。接下来要做的是在您的Springboot存储库接口上编写查询，如下所示：

最初的回答：

@Repository
public interface SellerGeolocationRepository extends CrudRepository<Seller_Geolocation, Long> {

    @Query(value="SELECT * FROM seller_geolocation_ms WHERE ST_DWithin(cast(seller_geolocation_ms.location as geography),ST_SetSRID(ST_Point(?2, ?1),4326), 10000);", nativeQuery = true)
    public Set<Seller_Geolocation> findAllSellersInRange(double longitude, double latitude);

}

要深入了解ST_DWithin的工作原理，请点击这里。"Original Answer"翻译成中文是"最初的回答"。

- DvixExtract

---

非常感谢您的回答@DivxExtract！！以它目前的形式，对我来说并没有起作用，但是一旦我将?2、?1参数绑定切换为命名参数：longitude:latitude，它就起作用了。 - Bram Luyten

Answer 3

看看 Postgres earthdistance，这里还提到了一个示例 here 不过，你可以使用 Postgis，它具有丰富的地理空间功能。 如果你想在业务逻辑中进行计算，请使用如 @JGH 回答中所述的 Haversine 公式。

- grindlewald