Bug #68091 ST_Overlaps and ST_Intersects give erroneous results

Submitted: 15 Jan 2013 23:59 Modified: 24 Feb 2015 16:00
Reporter: Luke Townsend Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: GIS Severity:S1 (Critical)
Version:5.6 OS:Windows
Assigned to: Norvald Ryeng CPU Architecture:Any
Tags: Spatial Overlaps Intersects

[15 Jan 2013 23:59] Luke Townsend
Description:
ST_Overlaps returns 1 (true) for 2 polygons that only share a boundary whereas ST_Intersects returns 0 (false). This should never happen for ANY two polygons by definition.

How to repeat:
create 2 simple polygons that share a boundary.
eg

insert into tbl_polygon (id, geom) values 
('POLY1',GeomFromText('POLYGON((0 0,0 10,10 10,10 0,0 0))'));
insert into tbl_polygon (id, geom) values 
('POLY2',GeomFromText('POLYGON((0 0,0 -10,10 -10,10 0,0 0))'));

now run some basic tests:
select area(t.geom) from tbl_polygon t 
 where t.id like 'POLY%'; -- expect to see 100 for both.

select touches(t.geom, p.geom) 
  from tbl_polygon t, tbl_polygon p 
 where t.id = 'POLY1' and p.id = 'POLY2'; --expected to be true, is true

select intersects(t.geom, p.geom) 
  from tbl_polygon t, tbl_polygon p 
 where t.id = 'POLY1' and p.id = 'POLY2'; --expected to be true, is true

select overlaps(t.geom, p.geom) 
  from tbl_polygon t, tbl_polygon p 
 where t.id = 'POLY1' and p.id = 'POLY2'; --expected to be false, is false

so far so good.

select st_touches(t.geom, p.geom) 
  from tbl_polygon t, tbl_polygon p 
 where t.id = 'POLY1' and p.id = 'POLY2'; --expected to be true, is true

select st_intersects(t.geom, p.geom) 
  from tbl_polygon t, tbl_polygon p 
 where t.id = 'POLY1' and p.id = 'POLY2'; --expected to be true, is false!!!

select st_overlaps(t.geom, p.geom) 
  from tbl_polygon t, tbl_polygon p 
 where t.id = 'POLY1' and p.id = 'POLY2'; --expected to be false, is true!!!

Suggested fix:
not sure. 
I haven't tested the linux version, only the windows RC (5.6.9)
I did test it with and without a spatial index. Made no difference.
[16 Jan 2013 1:59] Luke Townsend
The SQL to create the table used in the example:

CREATE TABLE `tbl_polygon` (
  `id` varchar(10) DEFAULT NULL,
  `geom` geometry DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
[16 Jan 2013 5:51] Erlend Dahl
Thank you for the bug report. Reproducible as described on 5.6.11.
[13 Jun 2014 23:46] Nikolaos Priggouris
Hi,
what is the status with this bug? It seems that it still exists in MySQL v5.6.15 (windows 64bit). ST_overlaps returns always 1 no matter whether there is overlapping or not of two polygons. This is really critical blocking issue since in my use case I have to check that the elements of a DB table consisting of multipolygons do no overlap. Can you provide feedback whether this bug has been fixed in any version of mysql?  If not is there a patch or workaround for it?
[14 Jun 2014 8:30] Erlend Dahl
This bug is still present, as far as I can see, both in the 5.6 codeline and the 5.7 milestones.

There is currently on-going work to refactor the MySQL GIS implementation in 5.7, see

http://mysqlserverteam.com/why-boost-geometry-in-mysql/

Norvald, is there any workaround we can offer for the time being?
[16 Jun 2014 7:53] Norvald Ryeng
These workarounds should be semantically equivalent to the failing queries, and they return the correct result for the given test data:

select st_intersects(t.geom, p.geom) OR st_touches(t.geom, p.geom) from tbl_polygon t, tbl_polygon p where t.id = 'POLY1' and p.id = 'POLY2';

select st_overlaps(t.geom, p.geom) AND NOT st_touches(t.geom, p.geom) from tbl_polygon t, tbl_polygon p where t.id = 'POLY1' and p.id = 'POLY2';
[24 Feb 2015 16:00] Paul DuBois
Noted in 5.7.6 changelog.

ST_Overlap() returned 1 and ST_Intersects() returned 0 for two
polygons that shared only a boundary.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.054 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.