問題描述
這是我的問題，我有一個包含位置和緯度/經度的 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 distance ASC;
HAVERSINE() 是一個 PHP 函數，它應該返回 大圓距離(以英里或公里為單位)給定一對緯度和經度值.其中一對應由 PHP 提供，另一對應由 locations 表中的每個緯度/經度行提供.
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.
因為 SQLite 沒有任何地理空間擴展 (AFAIK SpatiaLite 存在，但仍然......) 我猜最好的方法是將自定義函數與 PDO 方法之一一起使用:
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()
我認為對于這種情況 PDO::sqliteCreateFunction() 就足夠了，但是我對這個函數的有限經驗可以簡化為類似于 PHP 手冊中提供的用例:
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();
我在弄清楚如何讓 SQLite 用戶定義函數同時處理來自 PHP 和表數據的數據時遇到了一些麻煩，如果有人能幫我解決這個問題，我將不勝感激問題，同時也更好地理解 SQLite UDF(SQLite IMO 的一大勝利).
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.
提前致謝！
推薦答案
來自您的有趣鏈接".
function sqlite3_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 kilometres
return acos( sin($lat1rad) * sin($lat2rad) + cos($lat1rad) * cos($lat2rad) * cos( deg2rad($lon2) - deg2rad($lon1) ) ) * 6378.1;
}
$db->sqliteCreateFunction('DISTANCE', 'sqlite3_distance_func', 4);
然后進行查詢:
"SELECT * FROM location ORDER BY distance(latitude,longitude,{$lat},{$lon}) LIMIT 1"
<小時>
編輯(按 QOP):我終于再次需要這個，這個解決方案效果很好，我只是稍微修改了代碼，讓它不那么冗長，并且可以處理非數字值優雅地，這里是:
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);
這篇關于用 SQLite 計算大圓距離的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！