MySQL:空间查询以查找纬度/经度点是否位于给定边界内 [英] MySQL: Spatial Query to find whether a latitude/longitude point is located within a given boundary

问题描述

我正在使用Google Map搜索功能.这样做的目的是找出一个(地理位置)点是否位于多边形内，如下图所示?

I'm working on google map search functionality. The vision of this is to find whether a (geolocation) point is located within a polygon or not as shown in the illustration below?

我正在使用具有空间扩展功能的mysql 5.6.20，我知道它具有内置的有用的几何函数，因此可以直接从数据库中查询经过地理编码的位置.

I'm using mysql 5.6.20 with Spatial extension, I know it has useful geometry functions built-in, so I will be allowed to query geocoded locations from a database directly.

我的目标是使自己熟悉地理空间功能，因此我编写了一个实验sql.

My aim was to familiarize myself with geospatial functions, so I wrote up an experimental sql.

给出了点地理位置:POINT(100.52438735961914 13.748889613522605)

given a point geolocation: POINT(100.52438735961914 13.748889613522605)

，要测试的多边形或边界为: POLYGON(100.49503326416016 13.766897133254545,100.55940628051758 13.746555203977,100.56266784667969 13.72170897580617,100.48885345458984 13.739051587150175)

and a polygon or boundary to be tested with was: POLYGON(100.49503326416016 13.766897133254545,100.55940628051758 13.746555203977,100.56266784667969 13.72170897580617,100.48885345458984 13.739051587150175)

这是我的sql示例:

SELECT ST_Within(
ST_GEOMFROMTEXT('POINT(100.52438735961914 13.748889613522605)'),
ST_GEOMFROMTEXT('POLYGON(100.49503326416016 13.766897133254545,
100.55940628051758 13.746555203977,100.56266784667969 13.72170897580617,
100.48885345458984 13.739051587150175)'))
As geoFenceStatus

但是发出命令后，我得到的回报似乎如下:

but after issuing the command, what I got in return seemed to be as follows:

geoFenceStatus
===============
null

我不太确定为什么它会返回"null"值.因为功能文档中指出，如果点位于给定的多边形内，则应返回"1"

I'm so unsure why it returned me 'null' value. since it was indicated in function documentation that this should return '1' in case a point resides within a given polygon

任何建议将不胜感激，如何正确设置我的sql.

any advice would be appreciated, how to get my sql right.

推荐答案

错误消息不是很清楚，但是您的问题是您的多边形无效.它有两个问题:

The error message isn't very clear but your issue is that you have an invalid Polygon. There are two problems with it:

1. 您必须在末尾重复第一个点-这实际上是将其与LINESTRING区别开来，即它是封闭的.

1. You have to repeat the first point at the end -- this is to differentiate it from a LINESTRING, essentially, ie, it is closed.

POLYGONS以双括号(即POLYGON((x1 y1, x2 y2.......xn yn, x1 y1)))开头和结尾.这是为了允许在多边形内部使用单个括号集来描绘内圈.

POLYGONS start with and end with double parenthesis, ie, POLYGON((x1 y1, x2 y2.......xn yn, x1 y1)). This is to allow for inner rings to be delineated with single parenthesis sets inside the polygon.

有关更多信息，请参见维基百科WKT文章.

See the wikipedia WKT article for more information.

您应该发现，如果您将查询写为:

You should find that if you write you query as:

SELECT ST_Within(ST_GEOMFROMTEXT('POINT(100.52438735961914 13.748889613522605)'),
             ST_GEOMFROMTEXT('POLYGON((100.49503326416016 13.766897133254545, 100.55940628051758 13.746555203977,100.56266784667969 13.72170897580617, 100.48885345458984 13.739051587150175,
             100.49503326416016 13.766897133254545))'))
As geoFenceStatus

然后应该工作.

这篇关于MySQL:空间查询以查找纬度/经度点是否位于给定边界内的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！