How to use GPS data like the double value returned by getLatitude()?

Question

I have been searching quite a bit for an answer, but maybe I'm just not using the correct terminology. I am creating an app that will access a database to return a list of other users that are within a certain distance of the users location. I've never worked with this type of data, and I don't really know what the values mean. I'd like to do all the calculations on the backend with either MySQL or PHP. Currently, I am storing the latitude and longitude as doubles within the database. I can access them and store them, but I have no idea how I might be able to sort them based on distance. Perhaps I should be using a different type or some technique that is common in this area. TIA.

Answer 1

It sounds like you need to use the haversine formula which gets the distance between two sets of long/lat coordindates (adjusting for curvature of the earth).

If you run a query with that as an output, you can easily sort them based on minimum distance from the user.

Here is a link to implementing the haversine in 9 commonly used languages and here is a SO question which implements it inside a SQL query.

Here is the query that you could adapt (gets anything within 25 miles ordered from closest to furthest):

SELECT 
    id, 
    ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance 
FROM 
    markers 
HAVING 
    distance < 25 
ORDER BY 
    distance 
LIMIT 
    0 , 20;

Answer 2

I would suggest using Vicenty's Inverse Formula (http://en.wikipedia.org/wiki/Vincenty's_formulae) instead of the Haversine Great Circle distance, since Vincenty's been shown to be more accurate (Vincenty assumes the earth is an oblate spheroid instead of a perfect sphere, which is more accurate).

Here's the original Vincenty paper for the formula: http://www.ngs.noaa.gov/PUBS_LIB/inverse.pdf - Section 4

Here's the actual code from the Android platform that is used to calculate distance for distanceTo(Location), which uses Vincenty's Inverse Formula: https://github.com/android/platform_frameworks_base/blob/master/location/java/android/location/Location.java#L272

As to sorting distances based on a database query, for optimum performance you'll want to use a spatial database that allows spatial queries. MySQL has a spatial database plugin: http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html

Check out this post, which should give you the details to go from there, including notes on precision using Vicenty: Geo-Search (Distance) in PHP/MySQL (Performance)