Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question
    
I guess PostGIS is the way to go here, see for example this (recent) question: gis.stackexchange.com/questions/21903/… –  atlefren May 23 '13 at 13:40

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

share|improve this answer
    
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. –  simplexio May 23 '13 at 20:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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