| Bug #71076 | st_intersects works improperly | ||
|---|---|---|---|
| Submitted: | 4 Dec 2013 9:53 | Modified: | 24 Feb 2015 16:03 |
| Reporter: | Max Y | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: GIS | Severity: | S2 (Serious) |
| Version: | 5.6.14-62.0-log Percona Server (GPL), Re, 5.6.15 | OS: | Linux (Linux version 3.2.0-4-amd64 (debian-kernel@lists.debian.org) (gcc version 4.6.3 (Debian 4.6.3-14) ) ) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | spatial, st_intersects | ||
[4 Dec 2013 10:08]
Valeriy Kravchuk
Surely it's not about Percona server only or Linux version, repeatable with MySQL 5.6.15 also:
C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -uroot -proot -P3314 test
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.6.15-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> select geomfromtext("POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))") into @a;
Query OK, 1 row affected (0.16 sec)
mysql> select geomfromtext('linestring(-2 -2, 12 7)') into @l;
Query OK, 1 row affected (0.00 sec)
mysql> select st_intersects(@a, @l);
+-----------------------+
| st_intersects(@a, @l) |
+-----------------------+
| 0 |
+-----------------------+
1 row in set (0.03 sec)
mysql>
mysql> select geomfromtext('linestring(5 5, 15 4)') into @l;
Query OK, 1 row affected (0.00 sec)
mysql> select st_intersects(@a, @l);
+-----------------------+
| st_intersects(@a, @l) |
+-----------------------+
| 0 |
+-----------------------+
1 row in set (0.00 sec)
mysql>
mysql> select geomfromtext('linestring(7 6, 15 4)') into @l;
Query OK, 1 row affected (0.00 sec)
mysql> select st_intersects(@a, @l);
+-----------------------+
| st_intersects(@a, @l) |
+-----------------------+
| 0 |
+-----------------------+
1 row in set (0.00 sec)
mysql>
mysql> select geomfromtext('linestring(6 2, 12 1)') into @l;
Query OK, 1 row affected (0.00 sec)
mysql> select st_intersects(@a, @l);
+-----------------------+
| st_intersects(@a, @l) |
+-----------------------+
| 0 |
+-----------------------+
1 row in set (0.00 sec)
[4 Dec 2013 10:37]
MySQL Verification Team
Hello Max, Thank you for the bug report. Verified as described. Thanks, Umesh
[24 Feb 2015 16:03]
Paul DuBois
Noted in 5.7.6 changelog. ST_Intersects() sometimes incorrectly calculated the result for intersections of LineString and Polygon.
Description: st_intersects behaves improperly in some cases when calculating intersections of linestring an polygon. All of the following intersections should result to 1 instead of 0: mysql> select geomfromtext("POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))") into @a; mysql> select geomfromtext('linestring(-2 -2, 12 7)') into @l; mysql> select st_intersects(@a, @l); +-----------------------+ | st_intersects(@a, @l) | +-----------------------+ | 0 | +-----------------------+ 1 row in set (0.00 sec) mysql> select geomfromtext('linestring(5 5, 15 4)') into @l; Query OK, 1 row affected (0.01 sec) mysql> select st_intersects(@a, @l); +-----------------------+ | st_intersects(@a, @l) | +-----------------------+ | 0 | +-----------------------+ 1 row in set (0.00 sec) mysql> select geomfromtext('linestring(7 6, 15 4)') into @l; Query OK, 1 row affected (0.00 sec) mysql> select st_intersects(@a, @l); +-----------------------+ | st_intersects(@a, @l) | +-----------------------+ | 0 | +-----------------------+ mysql> select geomfromtext('linestring(6 2, 12 1)') into @l; Query OK, 1 row affected (0.00 sec) mysql> select st_intersects(@a, @l); +-----------------------+ | st_intersects(@a, @l) | +-----------------------+ | 0 | +-----------------------+ How to repeat: select geomfromtext("POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))") into @a; select geomfromtext('linestring(-2 -2, 12 7)') into @l; select st_intersects(@a, @l); select geomfromtext('linestring(5 5, 15 4)') into @l; select st_intersects(@a, @l); select geomfromtext('linestring(7 6, 15 4)') into @l; select st_intersects(@a, @l); select geomfromtext('linestring(6 2, 12 1)') into @l; select st_intersects(@a, @l);