MySQL GeoLocation查询-全表扫描
提问于 2016-09-09 14:50:57
问题:查找给定经度/纬度的办公室列表
MySQL版本:我们目前使用的是5.5.1
表模式：
mysql> describe delivery_office;
+-------------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------------+--------------+------+-----+---------+-------+
| id | bigint(20) | NO | PRI | NULL | |
| address_1 | varchar(255) | YES | | NULL | |
| address_2 | varchar(255) | YES | | NULL | |
| address_3 | varchar(255) | YES | | NULL | |
| address_4 | varchar(255) | YES | | NULL | |
| latitude | double | YES | MUL | NULL | |
| longitude | double | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| postcode | varchar(255) | YES | | NULL | |
| pt_loca | point | NO | MUL | NULL | |
+-------------------------------+--------------+------+-----+---------+-------+表索引：
mysql> show index from delivery_office;
+---------------------+------------+--------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+- --------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------+------------+--------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+- --------------+
| delivery_office | 0 | PRIMARY | 1 | id | A | 2156 | NULL | NULL | | BTREE | | |
| delivery_office | 1 | geo_index | 1 | latitude | A | 1896 | NULL | NULL | YES | BTREE | | |
| delivery_office | 1 | geo_index | 2 | longitude | A | 1939 | NULL | NULL | YES | BTREE | | | | | |
| delivery_office | 1 | pt_loca2 | 1 | pt_loca | A | 2156 | 32 | NULL | | SPATIAL | | |
+---------------------+------------+--------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+- --------------+解决方案:使用地理位置查询来查找它。
我使用了以下解决方案，但这两种解决方案都会导致全表扫描。
我的表只有2168条记录。
Fastest Way to Find Distance Between Two Lat/Long Points
mysql> explain SELECT *
-> FROM delivery_office
-> WHERE MBRContains
-> (
-> LineString
-> (
-> Point (
-> 51.5177 + 1 / 111.1,
-> -0.0968 + 1 / ( 111.1 /COS(RADIANS(51.5177)))
->
-> ),
-> Point (
-> 51.5177 - 1 / 111.1,
-> -0.0968 - 1 / ( 111.1 / COS(RADIANS(51.5177)))
->
-> )
-> ),
-> GeomFromText('POINT(51.5177 -0.0968)')
-> );
+----+-------------+---------------------+------------+------+----- ----------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | delivery_office | NULL | ALL | NULL | NULL | NULL | NULL | 2156 | 100.00 | NULL |
+----+-------------+---------------------+------------+------+------ ---------+------+---------+------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)https://gis.stackexchange.com/questions/31628/find-points-within-a-distance-using-mysql
mysql> explain EXTENDED SELECT (
-> 3959 * acos (
-> cos ( radians(51.3191750) )
-> * cos( radians( latitude ) )
-> * cos( radians( longitude ) - radians(-0.5632660) )
-> + sin ( radians(51.3191750) )
-> * sin( radians( latitude ) )
-> )
-> ) AS distance_in_miles, delivery_office.*
-> FROM delivery_office
-> HAVING distance_in_miles < 10
-> ORDER BY distance_in_miles
-> LIMIT 0 , 30;
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | delivery_office | NULL | ALL | NULL | NULL | NULL | NULL | 2156 | 100.00 | Using where; Using filesort |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 2 warnings (0.00 sec)有没有一种方法可以触发查询，而不会导致全表扫描。
回答 1
Stack Overflow用户
发布于 2016-09-12 11:20:27
根据@RickJames的建议，我可以使用这里提供的解决方案来优化查询：
https://www.scribd.com/presentation/2569355/Geo-Distance-Search-with-MySQL https://blog.fedecarg.com/2009/02/08/geo-proximity-search-the-haversine-equation/
以下是使用此优化的结果，行扫描从全表扫描减少到181条记录：
mysql> SELECT (
-> 3959 * acos (
-> cos ( radians(51.3191750) )
-> * cos( radians( latitude ) )
-> * cos( radians( longitude ) - radians(-0.5632660) )
-> + sin ( radians(51.3191750) )
-> * sin( radians( latitude ) )
-> )
-> ) AS distance_in_miles
-> FROM delivery_office
-> where longitude between (-0.5632660 - 10/abs(cos(radians(51.3191750))*69)) and (-0.5632660 + 10/abs(cos(radians(51.3191750))*69)) and latitude between (51.3191750 - (10/69)) and (51.3191750 + (10/69))
-> HAVING distance_in_miles < 10
-> ORDER BY distance_in_miles
-> LIMIT 0 , 10;
+--------------------+
| distance_in_miles |
+--------------------+
| 0.3381472408327969 |
| 1.9060143092544148 |
| 2.840055550183541 |
| 3.2969844240913697 |
| 3.356363831356166 |
| 4.815726191392366 |
| 5.134370232935941 |
| 5.177268204112493 |
| 5.528244889409913 |
| 5.702281140258665 |
+--------------------+
10 rows in set (0.00 sec)
mysql> explain EXTENDED SELECT ( -> 3959 * acos (
-> cos ( radians(51.3191750) )
-> * cos( radians( latitude ) )
-> * cos( radians( longitude ) - radians(-0.5632660) )
-> + sin ( radians(51.3191750) )
-> * sin( radians( latitude ) )
-> )
-> ) AS distance_in_miles
-> FROM delivery_office
-> where longitude between (-0.5632660 - 10/abs(cos(radians(51.3191750))*69)) and (-0.5632660 + 10/abs(cos(radians(51.3191750))*69)) and latitude between (51.3191750 - (10/69)) and (51.3191750 + (10/69))
-> HAVING distance_in_miles < 10
-> ORDER BY distance_in_miles
-> LIMIT 0 , 10;
+----+-------------+---------------------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
| 1 | SIMPLE | delivery_office | NULL | range | geo_index | geo_index | 18 | NULL | 181 | 11.11 | Using where; Using index; Using filesort |
+----+-------------+---------------------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
1 row in set, 2 warnings (0.00 sec)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/39414357
复制
相似问题
与Omnis Studio的持续集成
UML图和Omnis Studio 5或8
Omnis 64位
在Omnis中验证和清理JSON？
Python中的zip(*)功能
添加站长 进交流群
领取专属 10元无门槛券
AI混元助手 在线答疑
关注 腾讯云开发者公众号
洞察 腾讯核心技术
剖析业界实践案例
腾讯云开发者
