MySQL Bugs: #249: Spatial extension (GIS) returns wrong result for "touches" function

Bug #249	Spatial extension (GIS) returns wrong result for "touches" function
Submitted:	8 Apr 2003 5:15	Modified:	8 Apr 2003 21:41
Reporter:	Stepan Rybar	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1	OS:	Windows (MS Windows NT 5.1 (XP))
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
I have five polygons. Four of them touches each other at least in one point [x,y] = [12,8]. But when I run query 

select id from counties where touches(geometry, (select geometry from counties where id = 1)); 

only rest two polygons are in the result. I think, three polygons should be in the result. Polygon with id = 2 is missing in the result. 

Stepan 

How to repeat:
1) create table 
# Host: localhost
# Database: test
# Table: 'counties'
# 
CREATE TABLE `counties` (
  `id` int(11) NOT NULL default '0',
  `attribute` varchar(100) NOT NULL default '',
  `geometry` geometry default NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM CHARSET=latin1; 

2) add data into table (at least first two polygons)
mysql> select id, attribute, astext(geometry) from counties;
+----+-----------+------------------------------------------------------------+
| id | attribute | astext(geometry)                                           |
+----+-----------+------------------------------------------------------------+
|  1 | 10        | POLYGON((0 0,11 0,15 4,12 8,0 8,0 0))                      |
|  2 | 20        | POLYGON((11 0,20 0,20 8,12 8,15 4,11 0))                   |
|  3 | 30        | POLYGON((12 8,20 8,20 20,12 20,12 8))                      |
|  4 | 40        | POLYGON((0 8,12 8,12 20,0 20,0 8),(3 9,7 9,7 13,3 13,3 9)) |
|  5 | 50        | POLYGON((3 9,7 9,7 13,3 13,3 9))                           |
+----+-----------+------------------------------------------------------------+
5 rows in set (0.00 sec) 

3) run query 
mysql> select id from counties where touches(geometry, (select geometry from counties where id = 1));
+----+
| id |
+----+
|  3 |
|  4 |
+----+
2 rows in set (0.01 sec)

If it will be helpful, I created an picture of all these polygons with coordinates for better overview. It is 16 kB PNG file avaiable on "http://www.sweb.cz/xrybs01/MySQLBug249.png" or on request by mail. 

Stepan

Another problem with the same data, polygons 4 a 5 and function "contains". Polygon 4 has interior ring, which is fully covered by polygon 5. When I run query 

mysql> select id from counties where contains(geometry, (select geometry from counties where id = 5));

it result in 

+----+
| id |
+----+
|  4 |
|  5 |
+----+
2 rows in set (0.00 sec)

althought I guess only polygon 5 shlould appear. Hope I correctly understand spatial relationship. 

Stepan

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Thank you for your report, but this is expected behavior.
MySQL supports only MBR-based spatial fuctions.
Please take a look at
http://www.mysql.com/documentation/mysql/bychapter/manual_GIS_spatial_extensions_in_MySQL.html#GIS_functions_that_test_spatial_relationships_between_geometries

Yes, but MBR based functions should start with MBR..... These functons are already in MySQL 4.1. But I was confused, that functions without MBR as a prefix in their names behave in the same way as MBR functions. This was an error according to me. Anyway, in which version is planned to add nonMBR spatial relation functions? 

Thank You 

Stepan

I hope it will be available in 5.0 branch in 1-2 months.
Thanks for your interest.