postgresql Postgres按距离(公里)过滤

mgdq6dx1 于 2023-01-25 发布在 PostgreSQL

关注(0)|答案(1)|浏览(245)

我有一个属性数据库的地理位置由纬度和经度。我试图过滤属性在15公里半径（lon=1.8284，lat=41.7317）。我使用下面的查询是利用GIST索引：

CREATE INDEX CONCURRENTLY properties_geo_idx
ON properties
USING GIST (st_setsrid(st_makepoint(longitude, latitude), 4326));

WITH geo_nearby AS (
    SELECT
        latitude,
        longitude,
        bedrooms
    FROM properties
    WHERE 
        ST_DWithin(
            st_setsrid(st_makepoint(longitude, latitude), 4326),
            st_setsrid(st_makepoint(1.8284, 41.7317), 4326),
            15
        )
)
SELECT
    st_setsrid(st_makepoint(longitude, latitude), 4326) <-> st_setsrid(st_makepoint(1.8284, 41.7317), 4326) AS distance_kms,
    longitude,
    latitude,
    bedrooms
FROM geo_nearby
WHERE 
    bedrooms IN (3)
LIMIT 50;

结果是这样的：

distance_kms      |longitude         |latitude          |bedrooms|
------------------+------------------+------------------+--------+
 8.540781165192094|           9.20301|          46.03984|       3|
  9.96650170329088|          -0.16479|          51.49686|       3|
11.293155131144706|          12.90896|          43.91265|       3|
7.9370436736734655|          -4.55633|          37.01663|       3|
 6.095201658165212|          -1.93704|          36.93869|       3|
 9.972838081233446|          -0.18108|          51.49999|       3|
 10.40662225543428|           5.57883|          51.43902|       3|
10.945600831589932|-8.066421985626226|37.052097940287005|       3|
 8.580569450753254|           8.82644|          46.69694|       3|
3.8714160223360152|           5.36858|          43.29854|       3|
 7.936783092575982|          -4.55915|          37.02089|       3|
2.4448419241333372|           3.11484|          39.65268|       3|
12.183439828213542|          13.96874|          42.75559|       3|
 8.759152697201937|          -5.15033|           36.4383|       3|
14.095219617905924|          15.77691|          43.76007|       3|

这看起来不错，但看看Map上的坐标，它肯定不在距离（lon=1.8284，lat=41.7317）15公里以内
https://www.mapcustomizer.com/map/Distance%20not%20in%2015kms
我做错了什么？

postgresql

来源：https://stackoverflow.com/questions/75151410/postgres-filter-by-distance-in-kms

1条答案

按热度按时间

d6kp6zgx1#

查询中有两个错误。
当使用ST_DWithin时，geographies的距离以米为单位，geometries的距离以CRS为单位，因此这里的查询获取目标点15度以内的点。
其次，应用于geometries的<->运算符计算笛卡尔距离，也是以度为单位（这完全是错误的，因为经度和纬度的地面长度不同）。
您可以将所有点转换为geography来修复这些问题，包括索引中的问题。

CREATE INDEX CONCURRENTLY properties_geo_idx
ON properties
USING GIST ((st_setsrid(st_makepoint(longitude, latitude), 4326)::geography));

WITH geo_nearby AS (
    SELECT
        latitude,
        longitude,
        bedrooms
    FROM properties
    WHERE 
        ST_DWithin(
            st_setsrid(st_makepoint(longitude, latitude), 4326)::geography,
            st_setsrid(st_makepoint(1.8284, 41.7317), 4326)::geography,
            15000
        )
)
SELECT
    st_setsrid(st_makepoint(longitude, latitude), 4326)::geography <-> st_setsrid(st_makepoint(1.8284, 41.7317), 4326)::geography AS distance_kms,
    longitude,
    latitude,
    bedrooms
FROM geo_nearby
WHERE 
    bedrooms IN (3)
LIMIT 50;

赞(0）回复(0）举报 2023-01-25

我来回答

postgresql Postgres按距离(公里)过滤

1条答案

相关问题

热门标签

最新问答