Searching mySQL database for near by coordinates

Question

I doing an iPhone application which has an external database in this case i am using mySql.

i wan to store coordinates into the database and later i want to search the database using a coordinates to return only the coordinates that are near to the coordinates i am querying.

Example

-----------------
    Database    |
-----------------
1- Coordinate A |
2- Coordinate B |
3- Coordinate C |
-----------------

search using Coordinate D lets say coordinate A and Coordinate C are near to Coordinate D lets say with 5KM radius distance then my query result to show Coordinates A, Coordinates C and ignore Coordinate B as it is not near to Coordinate D

Does anyone have any idea on What format should my database be in ? How to query the database for near by coordinates ?

possible duplicate of [MySQL Great Circle Distance (Haversine formula)](http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula) — bryanmac, Oct 21 '11 at 15:24
exact dupe: http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula — bryanmac, Oct 21 '11 at 15:25

score 0 · Answer 1 · answered Oct 21 '11 at 14:58

Store your coordinates as 2 columns, each a float. So you'd have your table structure like:

lon float(X,Y)
lat float(X,Y)

Where X and Y are your desired precision.

For searching you can use the Haversine distance formula for geo / spatial searching with a fixed distance (your 5KM)

Searching mySQL database for near by coordinates

1 Answers1