I'm going to have a database with a 'people' table and a 'shops' table. I'd like to find all people within 500m of a shop, and details from both tables.

I'm used to working with mySQL, but I'm getting the idea that this would be better in PostgreSQL especially as this needs to scale to a lot of records. I just can't find a suitable example query or helpful documentation. Both tables will be points (and it's where people live, so not a moving target!).

The aim of this is to be able to loop through the results (in PHP) and say

The nearest butcher shops to Tom are: Browns Butchers, Smiths Butchers, and Tescos.

The nearest butcher shops to Richard are: Browns Butchers, and Tescos.

If anyone knows a mySQL query that wouldn't be too bad, then that's great too. It looks like this cutting down the area first will be most helpful and so I found this which is a bit more than just an sql query. http://www.movable-type.co.uk/scripts/latlong-db.html


1 Answer 1

SELECT s.name, s.type, ST_Distance(s.geom, p.geom) As distance, s.geom 
FROM shops s, people p 
WHERE p.name = 'tom' AND s.type = 'butcher' 
  AND ST_Intersects(s.geom, ST_Buffer(p.geom, 500))
ORDER BY distance;

One note, this assumes that both layers are in the same projection, and that projection can't just be lat/long or you'll need to use ST_Transform to reproject them to a coordinate reference system that uses meters.

Edit: Forgot to mention, make sure you index the geometry columns of both tables, it will make this query run much faster. Here's a guide to PostGIS spatial indexes.

Edit 2: I spaced on this, but an even simpler way to get the answer is to replace the ST_Intersects with ST_Dwithin

  • or 'WHERE ST_Distance(s.geom,pgeom) < 500' or ' ST_contains(s.geom, ST_Buffer(p.geom,500)... And note numeral is in used srid. So if your data is in WGS84 (srid 4326) that would not work because 4326 uses degrees, so you want to use metric system. etc etc. May 23, 2013 at 20:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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