2

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?

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.

| improve this answer | |
  • 1
    On a side note, you seem to be invoking ST_POINT with latitude and then longitude as parameters but the expected order of parameters is opposite of that. so New York would be ST_POINT(-70, 40) and not ST_POINT(40,-70) – KnightFox Aug 6 '17 at 18:52
  • Wow thank you! So since this will not be only local to new york, I think I'm going to have to do some looking up on the geography type. The idea is that I should be returning all the locations within X miles of a certain point, New York was just an example. Thank you again for the reference! – nab1994 Aug 8 '17 at 21:17

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.