MySQL Spatial CONTAINS显示错误结果 [英] MySQL Spatial CONTAINS shows wrong result

问题描述

我的MySQL空间搜索行为很奇怪.

I have a strange behavior of MySQL spatial search.

我已经在GEOM字段(葡萄牙边界)中创建了一个多边形，然后我试图在其中找到一个点-一切正常.

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

下一个尝试是找到多边形外部的点，但查询仍返回找到的1行.

请帮助，我在做什么错?为什么在多边形外部找到一个点?

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

用于测试的SQL代码如下:

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

内部:

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

外部:

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

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

推荐答案

好吧，我认为MYSQL在GEOMETRY周围使用了边界框.

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

MySQL Spatial有一些实质性的缺点.从好的方面来说， 确实具有空间类型，功能和索引.它遵循 用于几何图形表示的OGC规范.但是，数量 MySQL支持的功能非常小，因此它是 难以将数据库用于更复杂，更简单的任何事情 存储和按边界检索用例.另外，因为 空间选项在(非事务性)MyISAM中实现 表格类型，则无法在其中使用空间对象 交易.

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

将改为使用PostgreSQL.

Going to use PostgreSQL instead.

这篇关于MySQL Spatial CONTAINS显示错误结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！