使用SQLite计算大圆距离

Question

以下是我的问题，我有一个SQLite表格，其中包含位置和纬度/经度信息。基本上我需要：

SELECT location, HAVERSINE(lat, lon) AS distance FROM location ORDER BY distance ASC;

HAVERSINE()是一个PHP函数，它应该返回给定一对纬度和经度值的大圆距离（以英里或公里为单位）。其中一个纬度/经度对应该由PHP提供，另一个纬度/经度对应于locations表中可用的每个纬度/经度行。 由于SQLite没有任何地理空间扩展（AFAIK SpatiaLite存在但仍然......），我想最好的方法是使用自定义函数和PDO方法之一：

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

我认为对于这种情况，PDO::sqliteCreateFunction()就足够了，但是我的有限经验可以简化为类似于PHP手册中提供的用法示例。

$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 UDFs（在我看来是SQLite的一个重要优点）。谢谢！

- Alix Axel

---

你有没有一种不需要定义自定义函数就能工作的方法？请参考https://dev59.com/_3A75IYBdhLWcg3wv73A。 - Pentium10

@Pentium10：不，至少不是快速的。请查看我的另一个问题：https://dev59.com/QHI95IYBdhLWcg3w7CfL。 - Alix Axel

Answer 1

目前，我只能想到这个解决方案：

$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);

然后执行以下冗长的查询：

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;

如果有更好的解决方案，请告诉我。

---

我刚刚发现了这个有趣的链接，明天我会尝试一下。

- Alix Axel

Answer 2

从你的“有趣的链接”来看。

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）：最终我再次需要这个解决方案，它非常好用。我只是稍微修改了一下代码，让它更加简洁，并且可以优雅地处理非数字值。以下是修改后的代码：

$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);

- djunod

---

这个函数返回的是英里还是公里？ - Mat Jones

有 JavaScript 版本吗？ - chovy

Answer 3

在Alix的回答基础上...

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

我想象这将允许您在问题中指定的查询起作用。

- mattbasta

---

我可以使用 HAVERSINE("latitude", "longitude")，但我不知道如何处理 $latitude 和 $longitude PHP 变量。 - Alix Axel