Database procedure for locations

Question

Hi,

I'm trying to store a database of GPS locations and run queries which find the points within a given radius and also the closest points. I'm using mysql and have been looking at the spatial extensions. I am not sure if i see how to actually use the spatial extensions to do what I'm looking for in the location radius queries.

So here is what im thinking for my options:

1. Store the lat and long gps coordinates as float indexed variables in the db. When i get a point gps coordinate and a range, calculate the max and min lat and long values to query for and then sort based on a distance function within these to order.

2. Use the spatial extension. I'm not quite positive that this will work. The Distance() function is not implemented. Using a spatial index requires finding a bounding box (doable) and calling the MBRContains bounding box function to find the points that are in this bounding box. However a point has a boundary of zero so the MBRContains function doesnt work on points.

I'm not sure what the standard way to do this is (it doesnt seem like there is one) and would greatly appreciate any words of experience/ thoughts / help with the decision. I'm currently using mysql 5.13 and im pretty sure that even 5.5 on doesn't have the distance metric.

Also - even if 2. works, which one will be faster? Please let me know what your thoughts are and especially if you are sure / have seen something work for quick and large lookups!

Mysql Spatial Index: http://dev.mysql.com/doc/refman/5.5/en/using-a-spatial-index.html

Answer 1

Something like this should work to get the distance between a coordinate and an arbitrary point, in this example "origin."

SELECT 
place.name,
place.foo,
ROUND( SQRT(
   POW((69.1 * ({$origin->lat} - place.lat)), 2) 
 + POW((53.0 * ({$origin->lon} - place.lon)), 2)
), 1) AS distance
FROM place
ORDER BY distance ASC

Hope that helps...

Answer 2

How close together are the gps coordinates? If they are relatively close such that the curvature of the Earth is not an issue, you could first attempt to a query using a linear distance yourself to see how that performs. However, that does require a full table scan and may not produce accurate results if the points are not sufficiently clustered. At that point, you'd need to move on to a bounding box + great arc distance calculation.