如何优化SQL查询地理定位数据?

编程入门行业动态更新时间:2024-10-24 01:55:31

本文介绍了如何优化SQL查询地理定位数据?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

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

Our task is to find the properties within the location specified by a client. We have a MySQL table that contains the longitudes and latitudes of properties.

地址经纬度

地址 1 42.4001742 -71.1213472

address 1 42.4001742 -71.1213472

地址 2 42.4651592 -71.01366

address 2 42.4651592 -71.01366

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

So, logically, we can do a SQL search based on the client location

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)

这种搜索在数千个属性的情况下有多有效?优化此搜索的可能方法是什么?

How effective is this search in case of many thousands properties? What is a possible way to optimize this search?

推荐答案

您将需要此索引(使用 BTREE 索引).

You will want this index (use a BTREE index).

CREATE INDEX latlon ON addresses (latitude, longitude);

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

Your query will do an index range scan on the latitude (the south-north direction) and then filter on the east-west direction. That is fairly efficient.

一个纬度(南北)是 69 英里或 111.111 公里(基于拿破仑对米的定义......其中一千万从赤道到极地).

One degree of latitude (south-north) is 69 miles or 111.111 km (based on the Napoleonic definition of meter ... ten million of them from the equator to the pole).

每度经度(西-东)的距离因您离赤道的距离而异.你离赤道越远，距离就越短.在美国马萨诸塞州萨默维尔，一经度约为 51 英里.(你确实通过你的例子告诉我们你在哪里.)

The distance per degree of longitude (west-east) varies based on how far you are from the equator. The further you are from the equator the shorter the distance. In Somerville, Mass, USA, one longitude degree is about 51 miles. (You did tell us where you are by your example.)

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

So if you want a bounding box three miles in each direction centered around (42.4002 -71.1213) you need this filtering clause.

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.

If you want to give your box in km, use 111.111 in place of 69.0. If you want a differently sized box use a different number in place of 3.0.

那个过滤器表达式使用了我提到的索引就好了.

That filter expression uses the index I mentioned just fine.

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

If all the locations in your table are spread out mostly in the east-west direction but not the north-south direction (for example, along the Massachusetts Turnpike) then switch the order of the two columns in the index to get better selectivity. But usually this doesn't matter.

CREATE INDEX lonlat ON addresses (longitude, latitude);

您可能会发现阅读本文很有帮助.