| Bug #24659 | GIS contains() etc., only uses MBR | ||
|---|---|---|---|
| Submitted: | 28 Nov 2006 16:18 | Modified: | 1 Aug 2012 10:59 |
| Reporter: | Jeremy Cole (Basic Quality Contributor) (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: GIS | Severity: | S4 (Feature request) |
| Version: | 4.0,4.1,5.0, 5.1 | OS: | Any (All) |
| Assigned to: | CPU Architecture: | Any | |
[28 Nov 2006 16:20]
Jeremy Cole
Hmm, while there's a subcategory for such fascinating and well-used things as "Maria Storage Engine", there isn't one for "Spatial" or "GIS". Another oversight?
[29 Nov 2006 6:14]
Valeriy Kravchuk
Thank you for a problem report. As this is documented limitation (http://dev.mysql.com/doc/refman/5.1/en/functions-that-test-spatial-relationships-between-g...), I think, this is a reasonable feature request, but not a bug formally.
[1 Aug 2012 10:59]
Alexander Barkov
This problem was fixed in 5.6 with introduction of ST_Contains()
function which works on the actual shapes (not on the MBRs):
mysql> SELECT ST_CONTAINS(GEOMFROMTEXT("POLYGON((0 2, 2 0, 0 -2, -2 0, 0
2))"), GEOMFROMTEXT("POINT(1.9 1.9)")) AS cnt;
+------+
| cnt |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
Description: (Brian Aker asked me to create this bug. Yes, I know it's documented. :)) The current set of GIS functions only use the MBR of the geometry, not the actual geometry when doing comparisons. This makes them very nearly useless, and confusing for users. How to repeat: SELECT CONTAINS( GEOMFROMTEXT("POLYGON((0 2, 2 0, 0 -2, -2 0, 0 2))"), GEOMFROMTEXT("POINT(1.9 1.9)") ) as only_uses_mbr; This returns 1, even though (1.9 1.9) is not inside the above polygon: mysql> SELECT -> CONTAINS( -> GEOMFROMTEXT("POLYGON((0 2, 2 0, 0 -2, -2 0, 0 2))"), -> GEOMFROMTEXT("POINT(1.9 1.9)") -> ) as only_uses_mbr; +---------------+ | only_uses_mbr | +---------------+ | 1 | +---------------+ Suggested fix: Implement the proper behaviour for all GIS functions. (The current behaviour can be used as an optimization method for finding the correct rows in the first place.) I would suggest also having e.g. MBRContains() in addition to a properly working Contains(), so that the faster, cheaper option is available. This may still be useful if, for instance, the polygons in question are very large, and you only need to know which of them is most *likely* to contain your point.