Calculating Great-circle Distance With Sqlite

Here is my problem, I have a SQLite table with locations and latitudes / longitudes. Basically I need to: SELECT location, HAVERSINE(lat, lon) AS distance FROM location ORDER BY di

Solution 1:

So far I could only think of this solution:

$db = new PDO('sqlite:geo.db');

$db->sqliteCreateFunction('ACOS', 'acos', 1);
$db->sqliteCreateFunction('COS', 'cos', 1);
$db->sqliteCreateFunction('RADIANS', 'deg2rad', 1);
$db->sqliteCreateFunction('SIN', 'sin', 1);

And then execute the following lengthy query:

SELECT "location",
       (6371*ACOS(COS(RADIANS($latitude)) *COS(RADIANS("latitude")) *COS(RADIANS("longitude") - RADIANS($longitude)) +SIN(RADIANS($latitude)) *SIN(RADIANS("latitude")))) AS "distance"
FROM "locations"
HAVING "distance" < $distance
ORDERBY "distance" ASC
LIMIT 10;

If anyone can think of a better solution please let me know.

---

I just found this interesting link, I'll try it tomorrow.

Solution 2:

From your "interesting link".

functionsqlite3_distance_func($lat1,$lon1,$lat2,$lon2) {
    // convert lat1 and lat2 into radians now, to avoid doing it twice below$lat1rad = deg2rad($lat1);
    $lat2rad = deg2rad($lat2);
    // apply the spherical law of cosines to our latitudes and longitudes, and set the result appropriately// 6378.1 is the approximate radius of the earth in kilometresreturn acos( sin($lat1rad) * sin($lat2rad) + cos($lat1rad) * cos($lat2rad) * cos( deg2rad($lon2) - deg2rad($lon1) ) ) * 6378.1;
}

$db->sqliteCreateFunction('DISTANCE', 'sqlite3_distance_func', 4);

Then do a query with:

"SELECT * FROM location ORDER BY distance(latitude,longitude,{$lat},{$lon}) LIMIT 1"

---

EDIT (by QOP): I finally needed this again and this solution worked out great, I just ended up modifying the code a bit to it is a bit less verbose and handles non-numeric values gracefully, here it is:

$db->sqliteCreateFunction('distance', function () {
    if (count($geo = array_map('deg2rad', array_filter(func_get_args(), 'is_numeric'))) == 4) {
        return round(acos(sin($geo[0]) * sin($geo[2]) + cos($geo[0]) * cos($geo[2]) * cos($geo[1] - $geo[3])) * 6378.14, 3);
    }

    return null;
}, 4);

Solution 3:

Building off Alix's answer...

$db->sqliteCreateFunction('HAVERSINE', 'haversine', 2);

I would imagine that this would allow the query that you specified in your question to work.