Use ST_DWithin on WGS84 geometry data

Question

I've just used osm2pgsql to import osm data to a PostGIS database, using lat/long option to store it in WGS84 projection. I would like to use ST_DWithin to select nodes close to a random point. When I try that directly on the WGS84 data it returns wrong results but works if I reproject it in Web Mercator. What am i missing to avoid the reprojection?

SELECT ST_AsText(way),
       ST_X(ST_AsText(way)) as X,
       ST_Y(ST_AsText(way)) as Y,
       railway
  FROM planet_osm_point
 WHERE railway IS NOT NULL AND
       ST_DWithin(ST_Transform(way, 3785),
                  ST_Transform(ST_SetSRID(ST_MakePoint(-1.631560, 48.107485), 4326), 3785),
                  500)
 LIMIT 1000;

SELECT ST_AsText(way),
       ST_X(ST_AsText(way)) as X, 
       ST_Y(ST_AsText(way)) as Y,
       railway
  FROM planet_osm_point 
 WHERE railway IS NOT NULL AND
       ST_DWithin(way, ST_SetSRID(ST_MakePoint(-1.631560, 48.107485), 4326), 500)
 LIMIT 1000;

As you can see the outputs from WGS84 projection aren't even close to the point I'm lookin at and I can't figure out why when I am using the same geometry type for ST_DWithin. Is there a step I'm missing? (also I am not showing all of the results here, the Web Mercator projection gets me about 154 results, while WGS84 projection goes beyond 1000)

Answer 1

The problem is that you are using ST_DWithin on GEOMETRY objects with coordinates in WGS84. Therefore, your function call is requesting all features within 500 degrees (i.e., everything on the planet). https://postgis.net/docs/ST_DWithin.html

To calculate the distance in metres, cast the geometries to geography data type:

SELECT ST_AsText(way),
       ST_X(ST_AsText(way)) as X, 
       ST_Y(ST_AsText(way)) as Y,
       railway
  FROM planet_osm_point 
 WHERE railway IS NOT NULL AND
       ST_DWithin(way::geography,
                  ST_SetSRID(ST_MakePoint(-1.631560, 48.107485), 4326)::geography,
                  500)
 LIMIT 1000;

This will return slightly different distances compared to when you project to EPSG:3857 (Web Mercator) because that projection does not maintain distances to scale. Casting to geography will provide more accurate results by calculating distances using ellipsoid calculations, by default.