使用SQLite计算大圆距离 [英] Calculating Great-Circle Distance with SQLite

问题描述

这是我的问题，我有一个包含位置和纬度/经度的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.

提前谢谢！

推荐答案

通过您的有趣的链接".

From your "interesting link".

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计算大圆距离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！