MySQL Bugs: #68091: ST_Overlaps and ST_Intersects give erroneous results

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

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.

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;

Thank you for the bug report. Reproducible as described on 5.6.11.

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?

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?

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';

Noted in 5.7.6 changelog.

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