MySQL Bugs: #34609: Query for points within a polygon is returning points that are outside of it

Bug #34609	Query for points within a polygon is returning points that are outside of it
Submitted:	15 Feb 2008 18:52	Modified:	22 Feb 2008 15:10
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: GIS	Severity:	S2 (Serious)
Version:	4.1.20, 5.0.x, 5.1.23	OS:	Any
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
At least one spatial query for points within a polygon is returning points that are well outside of that polygon:

mysql> select astext(map_point) from points where ( contains(GeomFromText('Polyg
on((39.09889382148975 -74.68677520751953, 39.0930319292927 -74.63802337646484, 3
9.029585766893106 -74.6685791015625, 39.0421195689398 -74.71389770507812, 39.098
89382148975 -74.68677520751953))'),map_point));
+-----------------------------+
| astext(map_point)           |
+-----------------------------+
| POINT(39.0984 -74.713491)   |
| POINT(39.098255 -74.713217) |
| POINT(39.09849 -74.713663)  |
| POINT(39.098885 -74.712646) |
| POINT(39.097613 -74.713776) |
| POINT(39.098328 -74.713354) |
| POINT(39.097665 -74.713885) |
| POINT(39.098312 -74.713324) |
| POINT(39.098423 -74.713534) |
| POINT(39.098219 -74.713148) |
| POINT(39.098238 -74.713184) |
+-----------------------------+
11 rows in set (0.01 sec)

How to repeat:
Load mysqldump of a test table named points (will be uploaded later). 

Run:

select astext(map_point) from points where ( contains(GeomFromText('Polygon((39.09889382148975 -74.68677520751953, 39.0930319292927 -74.63802337646484, 39.029585766893106 -74.6685791015625, 39.0421195689398 -74.71389770507812, 39.09889382148975 -74.68677520751953))'),map_point));

Suggested fix:
Implement Contains() properly for polygons?

Actually, all versions of MySQL are affected:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test < c:\tmp\work\points.sql

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.23-rc-community-debug MySQL Community Server - Debug (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select astext(map_point) from points where ( contains(GeomFromText('Polyg
on((39.09889382148975 -74.68677520751953, 39.0930319292927 -74.63802337646484, 3
9.029585766893106 -74.6685791015625, 39.0421195689398 -74.71389770507812, 39.098
89382148975 -74.68677520751953))'),map_point));
+-----------------------------+
| astext(map_point)           |
+-----------------------------+
| POINT(39.0984 -74.713491)   |
| POINT(39.098255 -74.713217) |
| POINT(39.09849 -74.713663)  |
| POINT(39.098885 -74.712646) |
| POINT(39.097613 -74.713776) |
| POINT(39.098328 -74.713354) |
| POINT(39.097665 -74.713885) |
| POINT(39.098312 -74.713324) |
| POINT(39.098423 -74.713534) |
| POINT(39.098219 -74.713148) |
| POINT(39.098238 -74.713184) |
+-----------------------------+
11 rows in set (0.08 sec)

triage: need e/r values to asses target

As we state in the manual:
http://dev.mysql.com/doc/refman/5.1/en/functions-that-test-spatial-relationships-between-g...
spatial relation functions in the released versions aren't fully implemented,so the 'Contains' function works in fact as MBRContains. So that points there while not inside the polygon, still gets into it's MBR.
The proper fuction implementations are in a separate tree by now, but anyone interested is welcome to try. Look here for details:
http://forums.mysql.com/read.php?23,159205,159205#msg-159205