使用 mySQL 的 OpenStreetMap 邻近搜索

Question

我只是在玩弄由 JOSM 生成的我所在区域的数据集。我使用 Osmosis 将其转移到具有 0.6 API 方案的 mySQL 数据库中，现在我正在拼命尝试以下操作：

我想获取一个城市的所有街道。 据我所知，OSM 数据中没有标签/关系来确定这一点，因此我尝试使用邻近搜索来获取代表市中心的节点周围半径内的所有节点。

大多数时候我都会查看此处的方法，

我得到的是以下 SQL 代码，它应该可以让我找到最近的 100 个节点id为36187002的节点周围，半径10km以内。

set @nodeid = 36187002;
set @dist = 10;
select longitude, latitude into @mylon, @mylat from nodes where id=@nodeid limit 1;


SELECT id, ( 6371 * acos( cos( radians(@mylon) ) * cos( radians( latitude ) ) * 
cos( radians(  longitude ) - radians(@mylat) ) + sin( radians(@mylon) ) * sin( radians( latitude ) ) ) ) 
AS distance
FROM nodes HAVING distance < @dist ORDER BY distance LIMIT 0 , 100;

嗯..这不起作用。 :( 我想主要问题是 OSM 纬度/经度乘以 10.000.000，我不知道如何纠正这个函数以使其工作。

对此有什么想法吗？非常欢迎所有解决方案/替代方案！

Answer 1

将额外的列添加到表中以 double 数据类型表示的纬度和经度可能会更快（因此三角函数有机会） - 您可能需要进一步预先计算 x 轴， yaxis 和 zaxis 作为列（再次存储为 double），

因此，您的新列是松散的（您可能需要根据需要添加数据类型转换）：

XAxis   = cos(radians(Latitude / 10000000)) * cos(radians(Longitude / 10000000))
YAxis   = cos(radians(Latitude / 10000000)) * sin(radians(Longitude / 10000000))
ZAxis   = sin(radians(Latitude / 10000000))

然后，您的邻近搜索将变为：

set @nodeid = 36187002;
set @dist = 10;
SELECT XAxis, YAxis, ZAxis
INTO @CntXAxis, @CntYAxis, @CntZAxis
FROM nodes
WHERE id=@nodeid limit 1;

SELECT id, ( 6371 * acos(
             CASE
                WHEN nodes.XAxis * @CntXAxis
              + nodes.YAxis * @CntYAxis
              + nodes.ZAxis * @CntZAxis > 1.0 THEN 1.0
              ELSE  nodes.XAxis * @CntXAxis
              + nodes.YAxis * @CntYAxis
              + nodes.ZAxis * @CntZAxis 
             END
           ) AS Distance
FROM nodes 
HAVING Distance < @dist 
ORDER BY distance LIMIT 0 , 100;

Answer 2

我稍微修改了查询并且它有效。
这是我的代码：

    set @nodeid = 122317;
    set @dist = 10;
    select lon, lat into @mylon, @mylat from nodes where id=@nodeid limit 1;

    SELECT id, ( 6371 * acos(
    sin(radians(@mylat)) * sin(radians(lat)) +
    cos(radians(@mylat)) * cos( radians(lat)) * 
    cos(radians(lon) - radians(@mylon)) 
    )) 
    AS distance
    FROM nodes having distance <@dist

我从德语维基百科得到了公式，它工作得很好。我已经在一些 ruby​​ 代码中使用过，但它也可以用作 sql 查询。

为了选择一些特殊节点，我将其添加

(select nodes.id,lat,lon,k,v from nodes join node_tags on nodes.id=node_tags.id where k='public_transport') as stations

为 FROM 条件来指定节点的标签。 （当然，它改变了上面代码中对 station.lat/stations.log 的 lat/log 访问。