MySQL Spatial CONTAINS shows wrong result

Question

I have a strange behavior of MySQL spatial search.

I have created a polygon in a GEOM field (Portugal bounds), then I am trying to find a point inside -- it is found ok.

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

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

SQL Code for testing is 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

Lawsuit

I upgraded to MySQL 5.6.1 & it works fine now. It forms a Proper polygon instead of a bounding box. Its also much faster that PostGre when using Spatial indexes on MyISAM table. – Bowne

Answer 1

Ok, I have fugired out that MYSQL uses bounding-boxes around the GEOMETRY.

MySQL Spatial has some substantial disadvantages. On the plus side, it does have spatial types, functions and an index. And it follows the OGC specification for geometry representations. However, the number of functions MySQL supports is very small, and as a result it is difficult to use the database for anything more complex that simple storage and retrieval-by-bounding-box use cases. Additionally, because the spatial option is implemented in the (non-transactional) MyISAM table type, it is not possible to use spatial objects within transactions.

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

It looks like a bad joke...

Going to use PostgreSQL instead.

Lawsuit

5+ for "It looks like a bad joke... Going to use PostgreSQL instead." – Davison

Not an accurate answer... The text shown is not a valid response to the question asked (even if it is posted by the OP himself). @Stilbestrol answers the question correctly and should be awarded with the correct answer! – Marino

Answer 2

CONTAINS function does not search in precise object shape. It uses bounding rectangle around your polygon. You can try ST_CONTAINS function, which is available from MySQL 5.6.1

Functions That Test Spatial Relationships Between Geometries

Stilbestrol

This is the better answer to this question! – Marino