Retrieving and comparing locations in miles

Question

I am currently storing the locations with a SRID of 4326 (what seems to be most commonly suggested for lat, long).

I need to be able to run a query in postgres to find all locations that are within X miles.

Here's my current query:

SELECT * FROM users WHERE ST_Distance(location, ST_SetSRID(ST_Point(40, -70), 4326)) < 200;

From what I understand, this finds all points (location column in users) within 200 meters of New York (40, -70).

Would a standard conversion from meters to miles after the query or is there a better/more correct way to do this with miles?

Answer 1

There are a couple of issues with your query. In order of severity:

1. ST_Distance will return in the same units as the projection system is in, which is in your case planar degrees (see first example in de docs) and not meters (!).

2. The ST_Distance function is relatively expensive and since your are not really interested in the distance but only in whether it falls within a certain radius you will be better of using ST_DWithin. But again, this will calculate in degrees, so you will have to transform to a mile based coordinate system first

3. When you really need to store points in lat/lon (makes sense when you are dealing with world scale) and you want to calculate on them you should check the use of geography type. This post will explain a lot better, do read it.

The short answer:

SELECT * 
FROM users 
WHERE 
ST_DWithin(
 ST_Transform(location,32118), 
 ST_SetSRID(ST_Point(40, -70), 32118)
), 321869);

32118 is the projection system used in New York, but this can be different for your use case. It uses meters as units but there is also a projection system that has feet as unit.

The last big number (321869) is the number of meters in 200 miles. You would likely do this conversion within your query, but can just as well be handled somewhere else.