时间戳

首页 > 解决方案 > 如何优化 SQL 查询地理定位数据？

sql - 如何优化 SQL 查询地理定位数据？

问题描述

我们的任务是在客户指定的位置找到房产。我们有一个 MySQL 表，其中包含属性的经度和纬度。

地址经度纬度

地址 1 42.4001742 -71.1213472

地址 2 42.4651592 -71.01366

因此，从逻辑上讲，我们可以根据客户端位置进行 SQL 搜索

Select * from addresses where longitude between (client.location. longitude + 0.1) 
and (client.location. longitude - 0.1) and latitude between 
(client.location. latitude + 0.1) and (client.location. latitude - 0.1)

如果有成千上万的房产，这种搜索的效果如何？优化此搜索的可能方法是什么？

标签： sqlgeolocationquery-optimization

解决方案

您将需要此索引（使用 BTREE 索引）。

CREATE INDEX latlon ON addresses (latitude, longitude);

您的查询将对纬度（南北方向）进行索引范围扫描，然后在东西方向上进行过滤。这是相当有效的。

一个纬度（南北）是 69 英里或 111.111 公里（根据拿破仑对米的定义……从赤道到极地的一千万米）。

每经度的距离（西-东）根据您与赤道的距离而有所不同。离赤道越远，距离越短。在美国马萨诸塞州萨默维尔，一个经度度约为 51 英里。（你确实通过你的例子告诉我们你在哪里。）

因此，如果您想要一个以 (42.4002 -71.1213) 为中心的每个方向三英里的边界框，您需要这个过滤子句。

WHERE latitude  BETWEEN  42.4002 - (3.0/69.0) 
                    AND  42.4002 + (3.0/69.0)
  AND longitude BETWEEN -71.1213 - (3.0/(69.0 * COS(RADIANS(42.4002))))
                    AND -71.1213 + (3.0/(69.0 * COS(RADIANS(42.4002))))

如果您想以公里为单位提供您的盒子，请使用 111.111 代替 69.0。如果您想要一个不同大小的盒子，请使用不同的数字代替 3.0。

该过滤器表达式使用我提到的索引就好了。

如果表中的所有位置主要分布在东西方向而不是南北方向（例如，沿着马萨诸塞州收费公路），则切换索引中两列的顺序以获得更好的选择性。但通常这并不重要。

CREATE INDEX lonlat ON addresses (longitude, latitude);

您可能会发现阅读此内容很有帮助。

专业提示最好在海洋或玉米地中给出纬度/经度示例。

推荐阅读