Postgres filter by distance in Kms-CodePudding

I have a database of properties geolocated by latitude and longitude. I'm trying to filter properties within a 15km radius of (lon=1.8284, lat=41.7317). I'm using the below query which is taking advantage of an GIST index:

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;

The result is coming in like this:

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|

which looks good, but looking at those coordinates in the map, it's definitely not between 15kms of distance from (lon=1.8284, lat=41.7317)

https://www.mapcustomizer.com/map/Distance not in 15kms

What I am doing wrong here ?

CodePudding user response：

There are two errors in the query.

When using ST_DWithin, distance is in meters for geographies or in the CRS units for geometries, so here the query fetches points within 15 degrees of the target point.

Secondly, the <-> operator applied to geometries computes a Cartesian distance, again in degrees (and fully wrong since a degree of longitude doesn't have the same ground length as a degree of latitude).

You can cast all your points to geography to fix these issues, including in the index.

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,
            15
        )
)
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;