在边界框内选择所有地理空间点 [英] Select all geospatial points inside a bounding box

问题描述

我在一个有40万行的MySQL数据库中有一个名为标志"的表.该表由代表英国各地不同位置的地理空间点组成.

I have a table called 'flags' in a MySQL database with 400,000 rows. This table is made up of geospatial points that represent different positions around the UK.

我正在创建的应用程序使用Google Maps.在地图上是一个按钮，应在地图上切换标志可见性.现在，我的工作就是创建一个API，该API通过边界框时会返回边界框内的所有标记(以便它们可以随后显示在地图上).

The application I'm creating uses Google Maps. On the map is a button that should toggle the flags visibility on the map. It's my job now to create an API that when passed a bounding box all flags inside the bounding box are returned (so that they can then be displayed on the map).

传递的参数是视口当前位置的东北纬度/经度和西南纬度/经度.

The passed parameters are the North East latitude/longitude and the South West latitude/longitude of the viewport's current position.

我现在需要执行一个SQL查询，该查询将返回在这组坐标(视口)内的所有地理空间点.

I now need to perform an SQL query that will return all geospatial points that are inside this sets of coordinates (the viewport).

理想地，由于要搜索的行很多，因此需要优化解决方案.但是，该应用程序确实会迫使您放大到特定级别，然后用户才能显示这些标志.

Ideally the solution needs to be optimized as there are many rows to search. The application does force you to zoom into a certain level before the user can reveal the flags, however.

标记表:

• id
• 坐标
• 名称

示例行:

1 | [GEOMETRY-25B] | Tenacy AB

1 | [GEOMETRY - 25B] | Tenacy AB

也可以使用AsText(coordinates)将坐标字段转换为文字点. X和Y函数可为您执行此操作.

The coordinates field can also be converted to a literal point by using AsText(coordinates). The X and Y functions do this for you however.

坐标列的数据类型为:POINT

The coordinates column is data type: POINT

我知道要获取点的纬度/经度，可以使用X和Y函数.例如，可以像这样检索一个点的纬度:X(coordinates)

I know that to obtain the latitude/longitude of a point you can use the X and Y functions. For example, the latitude of a point can be retrieved like this: X(coordinates)

DBMS:MySQL
DBMS版本:5.6.14

DBMS: MySQL
DBMS version: 5.6.14

推荐答案

假定geometry列中POINT数据中的x和y项以纬度和经度为单位.

Presumably the x and y items in your POINT data in your geometry column is in degrees of latitude and longitude.

要在MySQL中高效地执行此查找，您需要做一些事情.

To do this lookup efficiently in MySQL, you'll need a few things.

• MyISAM表(或MySQL 5.7及更高版本以及InnoDB或MyISAM)
• 几何列上的NOT NULL限定
• 空间索引ALTER TABLE flags ADD SPATIAL INDEX (coordinates)
• 用于创建要搜索的矩形的文本表示形式的代码
• 在SELECT语句中使用GeomFromText和MBRContains/MBRWithin函数.

• A MyISAM table (or MySQL Version 5.7 and beyond and either InnoDB or MyISAM)
• A NOT NULL qualification on your geometry column
• A spatial index ALTER TABLE flags ADD SPATIAL INDEX (coordinates)
• Code to create a textual representation of the rectangle you want to search
• Use of the GeomFromText and MBRContains / MBRWithin functions in your SELECT statement.

假设纬度/经度框是一个矩形范围为1度的矩形，其中心为温彻斯特大教堂(51.0606，-1.3131).您需要围绕该点的边界框.此MySQL查询将为跨该边界框的对角线生成LINESTRING(文本).

Suppose your lat/long box is a rectangle one degree in extent centered about Winchester Cathedral (51.0606, -1.3131). You need a bounding box around that point. This MySQL query will generate a LINESTRING (text) for a line going diagonally across that bounding box.

SELECT 
       CONCAT('LINESTRING(',
              latitude-0.5,' ',longitude-0.5,
              ',', 
              latitude+0.5 ,' ',longitude +0.5,
              ')') AS box
   FROM (
      SELECT 51.0606 AS latitude, -1.3131 AS longitude
   ) AS coord

查询可为您提供此信息:

The query gets you this:

LINESTRING(50.5606 -1.8131,51.5606 -0.8131)

您还可以使用宿主语言中的字符串处理来提供类似的文本字符串.您需要的格式是这个.

You can also use string processing in a host language to come up with a similar sort of text string. The format you need is this.

 LINESTRING(lat1 long1, lat2 long2) 

然后您可以使用它来搜索空间表，如下所示:

Then you can use it to search your spatial table as follows:

SELECT whatever, whatever 
  FROM flags
 WHERE MBRContains(
        GeomFromText( 'LINESTRING(50.5606 -1.8131,51.5606 -0.8131)' ),
        flags.coordinates)     

这将利用空间索引，并找到flags的每一行，其坐标位于该对角线的边界框之内.

This will exploit the spatial index and find every row of flags whose coordinates lie within the bounding box of that diagonal line.

以下是一些文档

如果flags表包含的行少于几十万行，则可能会发现具有纬度和经度列(FLOAT数据类型，已索引)的普通表(而非空间表)也表现良好，并且更容易实现开发和调试.

If your flags table contains fewer than a few hundred thousand rows, you may find that an ordinary table (not a spatial table) with latitude and longitude columns (FLOAT data types, indexed) performs as well and is easier to develop and debug.

我已经写了一篇关于该技术的教程. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest -loc/

I have written a tutorial on that technique. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

这篇关于在边界框内选择所有地理空间点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！