MySQL GIS and Spatial Extensions - how to map regions and query against them

Question

I am trying to make a smartphone app which will return a list of users within a certain proximity, say 100m. It's easy to get the coordinates of my BlackBerry and write them to a database, but in order to return a list of other users within 100m, I need to pull every other record from the database and compare the distance between the two points, checking to see if it's within range, before outputting that user's information.

This is going to be time consuming if there are many users involved. So I would like to map areas (countries, cities, I'm not yet sure of the resolution I'll need) so that I can first target a smaller subset of all users. This will save on processing time.

I have read the basics of GIS and spatial querying on the mysql website but to be honest the query is over my head and I hate copying and pasting code without understanding it. Plus it only checks for proximity - I want to first check if a coordinate falls within a certain area.

Does anyone have any experience of such matters and feel like giving me some pointers? Resources such as any preexisting databases of points describing countries as polygons would be really helpful too.

Many thanks to anyone who takes the time :)

Answer 1

I would reccomend against using MySQL for doing spatial analysis. They have only implemented bounding box analysis and it is not implemented for most spatial functions. I would recommend using either PostGIS or perhaps spatialCouch or extend MongoDB. This would be much better for what you look to be doing.