Bug #90821 Mysql 8 support spatial fail with srid 4326
Submitted: 10 May 2018 9:43 Modified: 31 May 2018 13:17
Reporter: Lee Eric Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: GIS Severity:S1 (Critical)
Version:8 OS:Any (centos 7.3)
Assigned to: CPU Architecture:x86
Tags: spatial srid 4326

[10 May 2018 9:43] Lee Eric
Description:
Steps:
INSERT se_image (rect) VALUES (ST_GeomFromText('POLYGON((-1 -2, 1 -2, 1 2, -1 2, -1 -2))',4326))

SELECT st_astext(rect) AS rect FROM se_image WHERE MBRINTERSECTS(rect, ST_GeomFromText('POLYGON((-90 -180, 90 -180, 90 180, -90 180, -90 -180))',4326));

Can't find record.

How to repeat:
CREATE TABLE `se_image` (
  `rect` POLYGON NOT NULL /*!80003 SRID 4326 */ COMMENT '存储的空间数据',
  SPATIAL KEY `sp_index` (`rect`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='图幅'

SELECT st_astext(rect) AS rect FROM se_image

INSERT se_image (rect) VALUES (ST_GeomFromText('POLYGON((-1 -2, 1 -2, 1 2, -1 2, -1 -2))',4326))

SELECT st_astext(rect) AS rect FROM se_image WHERE MBRINTERSECTS(rect, ST_GeomFromText('POLYGON((-90 -180, 90 -180, 90 180, -90 180, -90 -180))',4326));
[10 May 2018 12:02] Miguel Solorzano
Thank you for the bug report.
[31 May 2018 13:17] Norvald Ryeng
Posted by developer:
 
Hi,

Thank you for using the geography support in MySQL 8.0!

There are three things in the test case I want to comment:

1. The polygon exterior rings are specified in clockwise order. OpenGIS standards specify that exterior rings should be in counter-clockwise order, while inner rings (holes) should be in clockwise order. MySQL will automatically flip these rings, so it doesn't affect computations in MySQL. But it might in other software, so I recommend following this standard.

2. The second polygon is larger than half the globe. That may cause problems in some cases in MySQL. Individual polygons should cover less than half the globe, but several such polygons can be combined in a geometrycollection if the size is an issue. However, in this case, it doesn't affect the computations.

3. The second polygon is intended to cover the entire globe, but it doesn't. And this is where the problem with the query is. It is interpreted as an infinitely narrow polygon along the antimeridian. This happens because each jump from one pair of coordinates in the ring to the next take the shortest path between the points. The second point is lat=90 lon=-180. The third point is lat=90 long=180. These are actually the same point, so instead of going all the way from east to west, across the prime meridian, it is actually interpreted as a jump of zero distance. This is what causes the weird result. This polygon doesn't intersect the other one.

In order to get the intended result, each jump in the polygon ring must be defined so that the shortest route between those points is the intended route. This means adding more intermediate points, e.g.:

POLYGON((-90 -180, 0 -180, 90 -180, 90 -90, 90 0, 90 90, 90 180, 0 180, -90 180, -90 90, -90 0, -90 -90, -90 -180))

This will give you the desired result.

Of course, because of comment 1, I recommend specifying the polygon exterior ring in a counter-clockwise order:

POLYGON((-90 -180, -90 -90, -90 0, -90 90, -90 180, 0 180, 90 180, 90 90, 90 0, 90 -90, 90 -180, 0 -180, -90 -180))

I hope this explains what is happening.

I'm closing this ticket as not a bug.