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:
None 
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:18] Jeremy Cole
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.
[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)