MySQL Spatial CONTAINS shows wrong result

Question

I have strange MySQL spatial search behavior.

I created a polygon in the GEOM field (borders in Portugal), then I try to find the point inside - it is found in order.

The next attempt is to find a point outside the polygon, but the query still returns 1 found row.

Please help, what am I doing wrong? Why does he find a point outside the polygon?

The SQL code for testing is given below:

CREATE TABLE IF NOT EXISTS `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `bounds` geometry NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; INSERT INTO `test` (`id`, `bounds`) VALUES (null, GEOMFROMTEXT('POLYGON((-8.876953125 41.8695608269946,-8.7451171875 41.9839942709356,-8.63525390625 42.0656067540572,-8.162841796875 42.1389684045809,-8.0859375 42.0248136078178,-8.23974609375 41.8859210281474,-8.15185546875 41.8204550961403,-7.899169921875 41.918628865183,-7.40478515625 41.8204550961403,-7.174072265625 41.9104534766642,-7.130126953125 42.0003251483162,-6.52587890625 41.951319946797,-6.50390625 41.6811175629065,-6.15234375 41.590796851056,-6.427001953125 41.2695495028426,-6.646728515625 41.2282490151853,-6.85546875 41.0296433871664,-6.8115234375 40.3549167507906,-7.03125 40.2334119071151,-6.866455078125 40.0213046873971,-6.9927978515625 39.687110247163,-7.53936767578125 39.6125651748163,-7.34024047851563 39.4820504550432,-7.22969055175781 39.1871628720445,-6.96533203125 39.0618491342915,-7.064208984375 38.8824811975508,-7.27294921875 38.7198047426424,-7.31689453125 38.3890334067591,-7.0751953125 38.2295504553262,-6.92550659179688 38.223077534953,-6.98799133300781 37.9994092820024,-7.20428466796875 37.9831748335134,-7.5421142578125 37.5707052423312,-7.44873046875 37.1953305828007,-7.91015625 36.985003092856,-8.32763671875 37.1252862849668,-8.63525390625 37.1340453712645,-8.98681640625 37.0025526721596,-8.7945556640625 37.6098799437471,-8.89617919921875 37.9463634508748,-8.800048828125 38.2295504553262,-8.90167236328125 38.5116391414586,-9.2230224609375 38.4320766853821,-9.23675537109375 38.6705005336435,-9.5361328125 38.7112325389523,-9.349365234375 39.364032338048,-8.8330078125 40.1956590933647,-8.822021484375 40.5096228495967,-8.61328125 41.0710691308064,-8.843994140625 41.4303718826528,-8.876953125 41.8695608269946))')); 

http://i.crisp-studio.cz/i/080e83c5bd3f3a27c3ecab9086a3.png

INSIDE:

 SELECT * FROM `test` WHERE CONTAINS(`bounds`, geomfromtext('Point(-8.050232 39.882343)')) 

OUTSIDE:

 SELECT * FROM `test` WHERE CONTAINS(`bounds`, geomfromtext('Point(-6.663208 40.05495)')) 

http://i.crisp-studio.cz/i/33b1115ef912647e82fead98abd1.png

Answer 1

Well, I guessed that MYSQL uses bounding fields around GEOMETRY.

MySQL Spatial has some significant drawbacks. On the plus side, it has spatial types, functions, and an index. And that follows the OGC for geometric representations. However, the number of MySQL functions supports is very small, and as a result, it is difficult to use the database for something more complex, easy to store and search in the bounding boxes. In addition, since the spatial option is implemented in the (non-transactional) MyISAM table type, it is not possible to use features inside the transaction.

http://workshops.opengeo.org/postgis-spatialdbtips/introduction.html

Sounds like a bad joke ...

Instead of using PostgreSQL.

Answer 2

The CONTAINS function does not search for the exact shape of the object. It uses a bounding box around your polygon. You can try the ST_CONTAINS function available from MySQL 5.6.1

Functions that check the spatial relationships between geometries