在MySQL中获取接近纬度的多边形 [英] Get polygons close to a lat,long in MySQL

问题描述

有人知道一种从点到给定距离内获取MySQL数据库中所有多边形的方法吗?实际距离不是那么重要，因为它是稍后为找到的每个多边形计算的，但是对接近"的多边形进行该计算将是一个巨大的优化.

我看过MBR并包含函数，但是问题在于某些多边形不包含在围绕该点绘制的边界框中，因为它们很大，但是它们的某些顶点仍然很近. >

有什么建议吗?

解决方案

慢速版本(无空间索引):

SELECT  *
FROM    mytable
WHERE   MBRIntersects(mypolygon, LineString(Point(@X - @distance, @Y - @distance), Point(@X + @distance, @Y + @distance))

要使用空间索引，需要对表格进行规范化处理，以便将每个多边形顶点存储在其自己的记录中.

然后在包含顶点坐标的字段上创建SPATIAL INDEX，然后发出以下查询:

SELECT  DISTINCT polygon_id
FROM    vertices
WHERE   MBRContains(vertex, LineString(Point(@X - @distance, @Y - @distance), Point(@X + @distance, @Y + @distance))

如果将UTM坐标存储在数据库中而不是纬度和经度，那么事情会容易得多.

Does anyone know of a way to fetch all polygons in a MySQL db within a given distance from a point? The actual distance is not that important since it's calculated for each found polygon later, but it would be a huge optimization to just do that calculation for the polygons that are "close".

I've looked at the MBR and contains functions but the problem is that some of the polygons are not contained within a bounding box drawn around the point since they are very big, but some of their vertices are still close.

Any suggestions?

解决方案

A slow version (without spatial indexes):

SELECT  *
FROM    mytable
WHERE   MBRIntersects(mypolygon, LineString(Point(@X - @distance, @Y - @distance), Point(@X + @distance, @Y + @distance))

To make use of the spatial indexes, you need to denormalize your table so that each polygon vertex is stored in its own record.

Then create the SPATIAL INDEX on the field which contains the coordinates of the vertices and just issue this query:

SELECT  DISTINCT polygon_id
FROM    vertices
WHERE   MBRContains(vertex, LineString(Point(@X - @distance, @Y - @distance), Point(@X + @distance, @Y + @distance))

The things will be much more easy if you store UTM coordinates in your database rather than latitude and longitude.

这篇关于在MySQL中获取接近纬度的多边形的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！