1

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 
  • 2
    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 Dec 14 '14 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 Dec 14 '14 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 Dec 14 '14 at 2:01
  • 3
    Please edit the question to add information, so that those who would answer don't have to sift through comments for critical details. – Vince Dec 14 '14 at 2:56
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 )

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.