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.