sugumar1983
New Member
Here is my problem, I have a SQLite table with locations and latitudes / longitudes. Basically I need to:\[code\]SELECT location, HAVERSINE(lat, lon) AS distance FROM location ORDER BY distance ASC;\[/code\]\[code\]HAVERSINE()\[/code\] 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 \[code\]locations\[/code\] 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:I think for this case \[code\]PDO::sqliteCreateFunction()\[/code\] 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:\[code\]$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();\[/code\]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!