19

I have records stored in a table with latitude/longitude coordinates stored in a geometry field. I want to find all records nearby a user supplied reference point. Note "nearby" probably means less than 100km (possibly even smaller).

Most examples I see use ST_DWithin. Is there a reason you can't use ST_Distance? What is the difference between using ST_DWithin and ST_Distance to do this?

For example:

SELECT name, ST_AsText(coords)
FROM places
WHERE ST_DWithin(coords, ST_GeomFromText('POINT(-12.5842 24.4944)',4326), 1)

vs

SELECT name, ST_AsText(coords)
FROM places
WHERE ST_Distance(coords, ST_GeomFromText('POINT(-12.5842 24.4944)',4326)) < 1
PolyGeo
  • 65,136
  • 29
  • 109
  • 338
User
  • 1,223
  • 1
  • 9
  • 8

1 Answers1

25

ST_Distance is a calculation which must be executed and evaluated on every row. ST_DWithin can use an index, so it's likely to be much faster.

Note that you really ought to be using a geography cast, so instead of Cartesian degrees (which are useless) a geodesic ground distance could be specified:

SELECT name, ST_AsText(coords)
FROM   places
WHERE  ST_DWithin(coords::geography, 
            ST_GeomFromText('POINT(-12.5842 24.4944)',4326)::geography,
            100000) -- 100km in meters
Vince
  • 20,017
  • 15
  • 45
  • 64
  • 5
    Also managed to find this which says pretty much same thing: http://postgis.net/2013/08/26/tip_ST_DWithin – User Dec 10 '13 at 05:45
  • Updated link for my above comment (old link giving a 404): https://postgis.net/documentation/tips/st-dwithin/ – User Feb 29 '24 at 05:51