Finding points near points using PostgreSQL?

Question

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

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