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:Microsoft Windows (MS Windows NT 5.1 (XP))
Assigned to: Ramil Kalimullin CPU Architecture:Any

[8 Apr 2003 5:15] Stepan Rybar
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)
[8 Apr 2003 5:57] Stepan Rybar
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
[8 Apr 2003 6:25] Stepan Rybar
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
[8 Apr 2003 7:15] Ramil Kalimullin
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
[8 Apr 2003 7:56] Stepan Rybar
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
[8 Apr 2003 21:41] Ramil Kalimullin
I hope it will be available in 5.0 branch in 1-2 months.
Thanks for your interest.