Mysql函数MBRContains不正确 [英] Mysql function MBRContains is not accurate

问题描述

我有以下多边形(在图像中您可以看到其覆盖的区域)

I have the following POLYGON (in the image you can see the area it covers)

POLYGON((-74.05100448502202 4.7239278424321,-74.05092938316898 4.7241416902206,-74.04830618275201 4.7237460717602,-74.04643668306903 4.7234306460692,-74.04635688735101 4.7234105978214,-74.04636526925401 4.7233310730989,-74.046191260944 4.72327293317,-74.04579027069599 4.7232007594583,-74.04141290558402 4.7214258184083,-74.03746201170497 4.7197791822891,-74.03565688503801 4.7189879401666,-74.033484295736 4.7180897723398,-74.03098447693401 4.7170526009038,-74.028731840457 4.7161167561787,-74.02852820211899 4.7150714370973,-74.026398371001 4.6877232674918,-74.02558060109601 4.6874859863574,-74.02454587610401 4.686797564651,-74.024665108676 4.6863189291555,-74.025470986757 4.6857975214267,-74.02585246812498 4.6846813784365,-74.02580479605103 4.6834369175226,-74.01962984798399 4.684922743491,-74.028472839649 4.6765444849623,-74.032273278366 4.6775012677607,-74.03825980124901 4.6799297676049,-74.048215993474 4.6850422042295,-74.05718496514402 4.6867981911917,-74.05100448502202 4.7239278424321))

当我执行MBRIntersect，MBRContains和Within函数时，它们返回绿色标记在多边形内部，但不是(如您在图像中所见).我正在执行下一个句子以获取该信息:

When I execute MBRIntersect, MBRContains and Within functions they return that the green marker is inside of the polygon, but it is not (as you can see in the image). I'm executing the next sentence to get that:

SET @g1 = ST_GeomFromText('POLYGON((-74.05100448502202 4.7239278424321,-74.05092938316898 4.7241416902206,-74.04830618275201 4.7237460717602,-74.04643668306903 4.7234306460692,-74.04635688735101 4.7234105978214,-74.04636526925401 4.7233310730989,-74.046191260944 4.72327293317,-74.04579027069599 4.7232007594583,-74.04141290558402 4.7214258184083,-74.03746201170497 4.7197791822891,-74.03565688503801 4.7189879401666,-74.033484295736 4.7180897723398,-74.03098447693401 4.7170526009038,-74.028731840457 4.7161167561787,-74.02852820211899 4.7150714370973,-74.026398371001 4.6877232674918,-74.02558060109601 4.6874859863574,-74.02454587610401 4.686797564651,-74.024665108676 4.6863189291555,-74.025470986757 4.6857975214267,-74.02585246812498 4.6846813784365,-74.02580479605103 4.6834369175226,-74.01962984798399 4.684922743491,-74.028472839649 4.6765444849623,-74.032273278366 4.6775012677607,-74.03825980124901 4.6799297676049,-74.048215993474 4.6850422042295,-74.05718496514402 4.6867981911917,-74.05100448502202 4.7239278424321))', 4326);
SELECT MBRContains(@g1, ST_PointFromText('POINT(-74.051585 4.680108)', 4326)) g1, 
st_distance(ST_PointFromText('POINT(-74.051585 4.680108)', 4326), @g1) distance

我得到

g1      distance
1   |   0.005489581062607619

但是我在期待

g1      distance
0   |   0.005489581062607619

我尝试了以下情况:

• 保存分配4326 SRID的几何.
• 使用其他功能，得到相同的响应.

我正在使用5.7.14 MySQL版本

I'm Using 5.7.14 MySQL version

我在做什么错了?

我解决了这个问题，只是确认没有距离.但是，为什么从那个函数中得到这个结果呢?

I worked around this issue just validating that there's no distance. But, why am I getting this result from that functions?

推荐答案

我现在没有可使用的MySQL.因此，我首先尝试在SQL Server Spatial中复制您的查询.

I don't have a MySQL to play with right now. So i first try to duplicate your query in a SQL Server Spatial.

DECLARE @g1 geometry
DECLARE @h1 geometry
SET @g1= geometry::STGeomFromText('POLYGON((-74.05100448502202 4.7239278424321,-74.05092938316898 4.7241416902206,-74.04830618275201 4.7237460717602,-74.04643668306903 4.7234306460692,-74.04635688735101 4.7234105978214,-74.04636526925401 4.7233310730989,-74.046191260944 4.72327293317,-74.04579027069599 4.7232007594583,-74.04141290558402 4.7214258184083,-74.03746201170497 4.7197791822891,-74.03565688503801 4.7189879401666,-74.033484295736 4.7180897723398,-74.03098447693401 4.7170526009038,-74.028731840457 4.7161167561787,-74.02852820211899 4.7150714370973,-74.026398371001 4.6877232674918,-74.02558060109601 4.6874859863574,-74.02454587610401 4.686797564651,-74.024665108676 4.6863189291555,-74.025470986757 4.6857975214267,-74.02585246812498 4.6846813784365,-74.02580479605103 4.6834369175226,-74.01962984798399 4.684922743491,-74.028472839649 4.6765444849623,-74.032273278366 4.6775012677607,-74.03825980124901 4.6799297676049,-74.048215993474 4.6850422042295,-74.05718496514402 4.6867981911917,-74.05100448502202 4.7239278424321))', 4326);
SET @h1 = geometry::STGeomFromText('POINT(-74.051585 4.680108)', 4326)
SELECT @g1.STContains(@h1) contain, @g1.STDistance(@h1) distance

结果就是您所期望的:

contain      distance
0   |   0.005489581062607675

这是其中的原因:

根据您对所需内容的描述，我使用的是 STContains ，而不是 MBRContains . MBRContains函数首先在多边形上创建一个最小边界矩形，然后使用该新的多边形特征来进行包含接合.在您的示例中，该点确实落在多边形的MBR中，因此，这就是为什么您的MySQL结果不是您期望的那样. STContains是您要寻找的正确功能.

I am using STContains not MBRContains based on your description of what you are looking for. MBRContains function first create a Minimal Bounding Rectangle over your polygon, and use that new polygon feature do the contain judegement. In your example, the point does fall into the MBR of your polygon so thats why your MySQL result is not what you expect. And STContains is the right function you are looking for.

官方参考: Mysql空间链接

这篇关于Mysql函数MBRContains不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！