How to select geography features within a certain distance given in miles?

Question

I am just taking the first steps in using POSTGIS, but having trouble finding a simple example.

I have a database with a Key field, lat and lon fields and a GEOGRAPHY field.

I just want to have a query that gives me all of the Key fields with 100 miles of the following Lat/Lon 52.474702, -132.373041.

A pseudo query would be something like

select Key from GIS where GEOM is within 100 miles of 52.474702, -132.373041

Have a look at the answer to gis.stackexchange.com/questions/13082/…. Please note that it is good practice to use descriptive titles for your threads and include information about what you have tried so far in your question. — underdark, Commented Dec 14, 2014 at 0:32
I looked at that example, but it doesn't use the GEOGRAPHY field. That's the problem I'm having I usually like to have a series of very small, tightly focused queries that show how to achieve simple tasks. This then leads to developing a greater understanding. With POSTGIS I keep finding examples, with lots of stuff, I don't, which hides the thing I am actually trying to achieve. So far I haven't tried anything with a level of understanding to know I am on the right lines. — user31141, Commented Dec 14, 2014 at 2:00
So a psuedo query would be something like select Key from GIS where GEOM is within 100 miles of 52.474702, -132.373041 — user31141, Commented Dec 14, 2014 at 2:01
Please edit the question to add information, so that those who would answer don't have to sift through comments for critical details. — Vince, Commented Dec 14, 2014 at 2:56

underdark · Accepted Answer · 2014-12-14 20:15:25Z

2

Use STDWithin to check if the features are within the desired distance

For geography units are in meters and measurement is defaulted to use_spheroid=true

The documentation page provides simple examples of the usage of this function. In your case this should work:

SELECT key FROM the_table WHERE ST_DWithin( GEOM, ST_GeographyFromText('SRID=4326;POINT(-132.373041 52.474702)'), 100 * 1609.344 )

answered Dec 14, 2014 at 20:15

underdark

84.5k22 gold badges233 silver badges415 bronze badges

Add a comment |

1 Answer 1