Calculating Great-Circle Distance with SQLite

Question

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 distance ASC;

HAVERSINE() is a PHP function that should return the Great-Circle Distance (in miles or km) given a pair of latitude and longitude values. One of these pairs should be provided by PHP and the other pair should be provided by each latitude / longitude row available in the locations table.

Since SQLite doesn't has any Geo Spatial extension (AFAIK SpatiaLite exists but still...) I'm guessing the best approach would be to use a custom function with either one of the PDO methods:

• PDO::sqliteCreateFunction()
• PDO::sqliteCreateAggregate()

I think for this case PDO::sqliteCreateFunction() would be enough, however my limited experience with this function can be reduced to usage cases similar to the one provided in the PHP Manual:

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

function md5_and_reverse($string) { return strrev(md5($string)); }

$db->sqliteCreateFunction('md5rev', 'md5_and_reverse', 1);
$rows = $db->query('SELECT md5rev(filename) FROM files')->fetchAll();

I'm having some trouble figuring out how can I get an SQLite user defined function to process data from PHP and table data at the same time and I would appreciate if someone could help me solve this problem while also understanding SQLite UDFs (a big win of SQLite IMO) a little bit better.

Thanks in advance!

Answer 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
ORDER BY "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.

Answer 2

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.