I am completely new to MySQL spatial extensions, so please excuse my ignorance on the topic.
The scenario:
1) I collect a user's location via latitude and longitude. I store that location in a MySQL table.
2) I then display any other users within a range of that user. The range would be small - let's say 1000 ft.
Question: What is the best practice for storing the location points? What is the best practice for querying and selecting the points nearest that user?
Use the POINT Spatial datatype to store the coordinates.
The structure is like this:
CREATE TABLE gps name varchar(0), `location` point NOT NULL;
Please note the type of column 'location'.
After that, you have to define a SPATIAL index for the 'location' column.
CREATE SPATIAL INDEX sp_index ON gps(`location`);
Inserting data (latitude and longitude)
INSERT INTO gps (name, location) VALUES ( 'India' , GeomFromText( ' POINT(31.5 42.2) ' ) )
The function GeomFromText takes a string and returns a Geometry Object. Here 31.5 and 42.2 are latitude and longitude respectively. Note that there is NO comma between them. They are delimited by a space.
Reading data
SELECT name, AsText(location) FROM gps;
The AsText function converts the internal representation of a geometry to a string format.
Note: You have to use MySQL 5.0 or above the spatial extension support. Also use 'MyIsam' db engine for faster queries.
Unfortunately there is no such function in MySQL as select points in radius. But you can easily use this cosine law formula: d = acos( sin(φ1)*sin(φ2) + cos(φ1)*cos(φ2)*cos(Δλ) )*R to get distance between 2 points. You can find details here: http://www.movable-type.co.uk/scripts/latlong-db.html SQL query will look like this:
SELECT acos(sin(radians(Y(point1)))*sin(radians(Y(point2))) + cos(radians(Y(point1)))*cos(radians(Y(point2)))*cos(radians(X(point2))-radians(X(point1)))) * 6371 AS `distance`
FROM locations
