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)


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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.