Bug #69538 Spatial geometry function returns wrong result
Submitted: 21 Jun 2013 8:17 Modified: 21 May 2015 14:39
Reporter: Vaclav Novotny Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: GIS Severity:S2 (Serious)
Version:5.6.12 x64, 5.6.21 OS:Microsoft Windows (Win7 x64)
Assigned to: CPU Architecture:Any
Tags: Spatial functions, ST_Touches
Triage: Needs Triage: D2 (Serious)

[21 Jun 2013 8:17] Vaclav Novotny
Description:
I have problem with functions for testing spatial relationships between geometric objects. I believe, that it is a bug.

I need test, if given point lies in or touches polygon. I use ST_WITHIN and ST_TOUCHES, but ST_TOUCHES gives me always "0", even if it should return 1.

I tried other spatial functions and find another unexpected behavior with function ST_OVERLAPS, which returns always "1". 

How to repeat:
With MySQL 5.6 and higher perform query:

SELECT ST_TOUCHES(GEOMFROMTEXT('POINT(1 0)'), GEOMFROMTEXT('POLYGON((0 0,2 0,2 4,0 6,0 0))'))

It's obvious, that poit (1 0) lies on boundary (0 0, 2 0), but this query returns "0". It returns "1" when I run that test for poit (0 0), which is vertex of used polygon, so this function works for vertexes, but not for boundaries of polygon.

Another problem, I found, is with ST_Overlaps. When run for example query:

SELECT ST_OVERLAPS(GEOMFROMTEXT('POINT(100 0)'), GEOMFROMTEXT('POLYGON((0 0,2 0,2 4,0 6,0 0))'))

Here i get "1", even if it's obvious, that poit (100 0) is far away from polygon.

Suggested fix:
None
[30 Jun 2013 9:40] Vaclav Novotny
Workaround:
increase size of poitn (make it a circle) in sufficient scale to maintain accuracy.

Use mysql function BUFFER(g, d) for increasing and then test relationship with function ST_CROSSES.

Example:

SET @point = BUFFER(GEOMFROMTEXT('POINT(1 0)'), 0.0001);
SET @poly = GEOMFROMTEXT('POLYGON((0 0,2 0,2 4,0 6,0 0))');
SELECT ST_CROSSES( @point, @poly);
[21 Jul 2014 19:06] Sveta Smirnova
Thank you for the report.

Verified as described. While we can discuss if definition of ST_TOUCHES ("Two geometries spatially touch if the interiors of the geometries do not intersect, but the boundary of one of the geometries intersects either the boundary or the interior of the other. ") means that only points 0 0, 2 0, 2 4 and 0 6 can touch, but it is certain that 100 0 cannot overlap the polygon.
[21 May 2015 14:39] Paul Dubois
Noted in 5.7.8, 5.8.0 changelogs.

ST_Within() and ST_Touches() could return incorrect results for some
arguments.
[21 May 2015 14:43] Paul Dubois
Issues were resolved by use of Boost.Geometry for GIS algorithms.