使用MySQL在表格中搜索多边形中的点 [英] Search a table for Point in Polygon using MySQL

问题描述

我已经在MySQL 5.5中创建了一个表(municipal_border)，该表具有一些边界.

I have create a table (municipal_border), in MySQL 5.5, that holds some boundaries.

CREATE TABLE `municipal_border` (
  `boundary` polygon NOT NULL,
  `municipalID` int(10) NOT NULL,
) ENGINE=InnoDB

字段MunicipalID不是唯一的.

The field municipalID is not unique.

我正在使用下面的代码测试点是否属于多边形.

I'm using the code below to test if a point belongs into a polygon.

set @r = (SELECT municipal_border.boundary FROM municipal_border WHERE municipalID=9001);
set @p = GeomFromText('POINT(24.1621 41.0548)');
select if(contains(@r, @p), 'yes', 'no');

第一个设置为@r = ...的语句仅返回一行，我专门选择了它进行测试. 效果很好.

The first statement set @r = ... returns just one row and I selected it specially for testing. It works just great.

我想做的是搜索整个表(换句话说，从SQL问题中删除WHERE部分)，然后找到该点在哪个多边形中.

What I want to do is to search the whole table (erasing, in other words, the WHERE part from the SQL question) and find in which polygon the point is in.

推荐答案

一夜睡眠后，我发现了以下解决方案.

After a night sleep I found the following solution.

set @p = GeomFromText('POINT(23.923739342824817 38.224714465253733)');
select municipalID FROM ecovis.municipal_border
where ST_Contains(municipal_border.boundary, @p);

它适用于MySQL 5.6.1，其中

It is working for MySQL 5.6.1 where ST_ prefix function have been implemented. Although I haven't any measurments from a classical approach (x-ray algorithm) I believe that is quite fast. It needs 0.17 seconds to locate a point in 2700 polygons with some polygons having well more than 1,500 points.

这篇关于使用MySQL在表格中搜索多边形中的点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！