Oscar David Oscar David - 2 months ago 15
MySQL Question

Mysql function MBRContains is not accurate

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

Polygon area

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))


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


and I'm getting

g1 distance
1 | 0.005489581062607619


But I was expecting

g1 distance
0 | 0.005489581062607619


I have try the following cases:


  • Save geometry assigning 4326 SRID.

  • Using other functions, getting same response.



I'm Using 5.7.14 MySQL version

What am I doing wrong?

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

Answer

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

And the result is what you may expect:

contain      distance
0   |   0.005489581062607675

Here is the reason behind this:

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.

Official reference: Mysql spatial Link

Comments