使用mysql表上的空间索引选择最近的地理坐标不起作用 [英] Selecting nearest geo coordinates using spatial index on mysql table not working

问题描述

我有一个 mysql MyISAM 表，其中有许多行包含有关地点的信息，例如地理坐标和地名.

我想根据给定的输入纬度和经度从该表中选择最近的地方.我想这样做创建一个带有地理坐标点的列，使用空间索引和 MBRContains 来选择数据.

我在选择数据时出错.我会写下我在这里做的步骤:

步骤 1

ALTER TABLE mytable ADD mypoint Point;更新我的表SET mypoint = Point(lat, lon);ALTER TABLE mytable MODIFY mypoint NOT NULL;在 mytable(mypoint) 上创建空间索引 sx_mytable_coords;

结果:我在 mypoint 列中看到了这一点(我不确定这是否正确，但所有记录都相同):[GEOMETRY - 25Bytes]我看到添加了索引:Name: mypoint Type: SPATIAL Field: mypoint (32) Collat​​ion: A

步骤 2

我尝试通过两种方式选择数据:

1. 推荐低于 MySQL 5.1 的版本(我的情况)

   SET @lat = 40;设置@lon = -10;选择  *从 mytableWHERE MBRContains(LineFromText(CONCAT('(', @lon + 10/( 111.1/cos(RADIANS(@lon))), ' ', @lat + 10/111.1, ',', @lon - 10/( 111.1/cos(RADIANS(@lat))), ' ', @lat - 10/111.1, ')' )，我的观点)

   我收到此错误:

   <块引用>

   1064 - 您的 SQL 语法有错误；检查手册对应于您的 MySQL 服务器版本以使用正确的语法在第 15 行的LIMIT 0, 30"附近

2. 推荐用于 MySQL 5.1 以上的版本

   SET @lat = 40;设置@lon = -10;选择  *从 mytableMBR包含的地方(线串(观点(@lon + 10/( 111.1/COS(RADIANS(@lat))),@lat + 10/111.1)观点(@lon - 10/( 111.1/COS(RADIANS(@lat))),@lat - 10/111.1)),我的观点)

   我收到此错误:

   <块引用>

   1064 - 您的 SQL 语法有错误；检查手册对应于您的 MySQL 服务器版本以使用正确的语法'Point ( ' at line 12

解决方案

您缺少一个括号，您没有关闭 LineFromText.

试试:

SELECT *从 mytableWHERE MBRContains(LineFromText(CONCAT('(', @lon + 10/( 111.1/cos(RADIANS(@lon))), ' ', @lat + 10/111.1, ',', @lon - 10/( 111.1/cos(RADIANS(@lat))), ' ', @lat - 10/111.1, ')' ))，我的观点)

I have a mysql MyISAM table with many rows that contain information about places, such as geo coordinates and placenames.

I want to select the nearest place from this table depending given an input latitude and longitude. I want to do this creating a column with points of the geocoordinates, using a spatial index and MBRContains to select the data.

I get an error when selecting the data. I will write down the steps that I did here:

Step 1

ALTER TABLE mytable ADD mypoint Point;

UPDATE  mytable
SET     mypoint = Point(lat, lon);

ALTER TABLE mytable MODIFY mypoint NOT NULL;

CREATE SPATIAL INDEX sx_mytable_coords ON mytable(mypoint );

Result: I see this in the mypoint column (I'm not sure if that's oke, but it's the same for all records): [GEOMETRY - 25Bytes] I see that the index was added: Name: mypoint Type: SPATIAL Field: mypoint (32) Collation: A

Step 2

I tried to select the data in two ways:

1. This is recommended for version lower than MySQL 5.1 (My case)

   SET @lat = 40;
   SET @lon = -10;
   
   
   SELECT  *
   FROM    mytable
   WHERE   MBRContains(LineFromText(CONCAT(
           '('
           , @lon + 10 / ( 111.1 / cos(RADIANS(@lon)))
           , ' '
           , @lat + 10 / 111.1
           , ','
           , @lon - 10 / ( 111.1 / cos(RADIANS(@lat)))
           , ' '
           , @lat - 10 / 111.1 
           , ')' )
           ,mypoint)
   

   I get this error:

   1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 30' at line 15

2. This is recommended for version higher than MySQL 5.1

   SET @lat = 40;
   SET @lon = -10;
   
   SELECT  *
       FROM    mytable
       WHERE   MBRContains
                       (
                       LineString
                               (
                               Point
                                       (
                                       @lon + 10 / ( 111.1 / COS(RADIANS(@lat))),
                                       @lat + 10 / 111.1
                                       ) 
                               Point
                                       (
                                       @lon - 10 / ( 111.1 / COS(RADIANS(@lat))),
                                       @lat - 10 / 111.1
                                       ) 
                               ),
                       mypoint
                       )
   

   I get this error:

   1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Point ( ' at line 12

解决方案

You have a missing bracket, you're not closing your LineFromText.

Try:

SELECT  *
FROM    mytable
WHERE   MBRContains(LineFromText(CONCAT(
    '('
    , @lon + 10 / ( 111.1 / cos(RADIANS(@lon)))
    , ' '
    , @lat + 10 / 111.1
    , ','
    , @lon - 10 / ( 111.1 / cos(RADIANS(@lat)))
    , ' '
    , @lat - 10 / 111.1 
    , ')' ))
    ,mypoint)

这篇关于使用mysql表上的空间索引选择最近的地理坐标不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！