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