12.17.9.1 & nbsp; các chức năng quan hệ không gian sử dụng hình dạng đối tượng Thông số kỹ thuật OpenGIS xác định các chức năng sau để kiểm tra mối quan hệ giữa hai giá trị hình học g1 và g2, sử dụng các hình dạng đối tượng chính xác. Các giá trị trả về 1 và 0 biểu thị đúng và sai, tương ứng, ngoại trừ ST_Distance(), trả về các giá trị khoảng cách. St_contains (________ 3, g2) Trả về 1 hoặc 0 để cho biết liệu g1 có chứa hoàn toàn g2 hay không. Điều này kiểm tra mối quan hệ ngược lại là mysql> SET @g1 = Point(1,1), @g2 = Point(2,2);
mysql> SELECT ST_Equals(@g1, @g1), ST_Equals(@g1, @g2);
+---------------------+---------------------+
| ST_Equals(@g1, @g1) | ST_Equals(@g1, @g2) |
+---------------------+---------------------+
| 1 | 0 |
+---------------------+---------------------+
0. Crosses (________ 3, g2) mysql> SET @g1 = Point(1,1), @g2 = Point(2,2);
mysql> SELECT ST_Equals(@g1, @g1), ST_Equals(@g1, @g2);
+---------------------+---------------------+
| ST_Equals(@g1, @g1) | ST_Equals(@g1, @g2) |
+---------------------+---------------------+
| 1 | 0 |
+---------------------+---------------------+
3 và mysql> SET @g1 = Point(1,1), @g2 = Point(2,2);
mysql> SELECT ST_Equals(@g1, @g1), ST_Equals(@g1, @g2);
+---------------------+---------------------+
| ST_Equals(@g1, @g1) | ST_Equals(@g1, @g2) |
+---------------------+---------------------+
| 1 | 0 |
+---------------------+---------------------+
4 là từ đồng nghĩa. Để biết thêm thông tin, xem mô tả của mysql> SET @g1 = Point(1,1), @g2 = Point(2,2);
mysql> SELECT ST_Equals(@g1, @g1), ST_Equals(@g1, @g2);
+---------------------+---------------------+
| ST_Equals(@g1, @g1) | ST_Equals(@g1, @g2) |
+---------------------+---------------------+
| 1 | 0 |
+---------------------+---------------------+
3. ST_CROSSES (________ 3, g2) Thuật ngữ giao nhau về mặt không gian biểu thị mối quan hệ không gian giữa hai hình học đã cho có các thuộc tính sau:spatially crosses denotes a spatial relation between two
given geometries that has the following properties: Hàm này trả về 1 hoặc 0 để cho biết liệu g1 có vượt qua không gian g2 hay không. Nếu g1 là Description:
ST_Intersects() is very slow on MySQL 8.0. (Using Spatial index is slower than not using Spatial index on MySQL 8.0)
[Example]
- ST_Intersects() with Spatial index : 5.11 sec
- ST_Intersects() without Spatial index : 3.84 sec
----------------------------------------
mysql> SHOW VARIABLES LIKE 'version';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| version | 8.0.17 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| 1 | SIMPLE | h27ka23_utf8 | NULL | range | SHAPE | SHAPE | 34 | NULL | 1 | 100.00 | Using where |
+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+---------+-----------+----------+
| OGR_FID | x_code | y_code |
+---------+-----------+----------+
| 1162 | 136.86591 | 35.17593 |
<<snip>>
+---------+-----------+----------+
23 rows in set (5.11 sec)
mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | h27ka23_utf8 | NULL | ALL | NULL | NULL | NULL | NULL | 12834 | 100.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+---------+-----------+----------+
| OGR_FID | x_code | y_code |
+---------+-----------+----------+
| 1038 | 136.86393 | 35.17555 |
<<snip>>
+---------+-----------+----------+
23 rows in set (3.84 sec)
----------------------------------------
[Note]
- I think "ST_Intersects() = ST_Within() + ST_Overlaps()". Therefore, I checked ST_Within() and ST_Overlaps() also.
- ST_Within() is fast. It is no problem.
- ST_Overlaps() is slow. It is a problem.
[Example]
- ST_Within() with Spatial index : 0.00 sec
- ST_Within() without Spatial index : 3.02 sec
- ST_Overlaps() with Spatial index : 4.70 sec
- ST_Overlaps() without Spatial index : 3.20 sec
----------------------------------------
How to repeat:
1. Import dump file. * I'll upload later.
2. Execute following SQLs.
----------------------------------------
[ST_Intersects]
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
----------------------------------------
[ST_Within]
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
----------------------------------------
[ST_Overlaps]
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
----------------------------------------
Suggested fix:
Improve performance of ST_Intersects() and ST_Overlaps().1 hoặc Description:
ST_Intersects() is very slow on MySQL 8.0. (Using Spatial index is slower than not using Spatial index on MySQL 8.0)
[Example]
- ST_Intersects() with Spatial index : 5.11 sec
- ST_Intersects() without Spatial index : 3.84 sec
----------------------------------------
mysql> SHOW VARIABLES LIKE 'version';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| version | 8.0.17 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| 1 | SIMPLE | h27ka23_utf8 | NULL | range | SHAPE | SHAPE | 34 | NULL | 1 | 100.00 | Using where |
+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+---------+-----------+----------+
| OGR_FID | x_code | y_code |
+---------+-----------+----------+
| 1162 | 136.86591 | 35.17593 |
<<snip>>
+---------+-----------+----------+
23 rows in set (5.11 sec)
mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | h27ka23_utf8 | NULL | ALL | NULL | NULL | NULL | NULL | 12834 | 100.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+---------+-----------+----------+
| OGR_FID | x_code | y_code |
+---------+-----------+----------+
| 1038 | 136.86393 | 35.17555 |
<<snip>>
+---------+-----------+----------+
23 rows in set (3.84 sec)
----------------------------------------
[Note]
- I think "ST_Intersects() = ST_Within() + ST_Overlaps()". Therefore, I checked ST_Within() and ST_Overlaps() also.
- ST_Within() is fast. It is no problem.
- ST_Overlaps() is slow. It is a problem.
[Example]
- ST_Within() with Spatial index : 0.00 sec
- ST_Within() without Spatial index : 3.02 sec
- ST_Overlaps() with Spatial index : 4.70 sec
- ST_Overlaps() without Spatial index : 3.20 sec
----------------------------------------
How to repeat:
1. Import dump file. * I'll upload later.
2. Execute following SQLs.
----------------------------------------
[ST_Intersects]
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
----------------------------------------
[ST_Within]
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
----------------------------------------
[ST_Overlaps]
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
----------------------------------------
Suggested fix:
Improve performance of ST_Intersects() and ST_Overlaps().2 hoặc nếu g2 là Description:
ST_Intersects() is very slow on MySQL 8.0. (Using Spatial index is slower than not using Spatial index on MySQL 8.0)
[Example]
- ST_Intersects() with Spatial index : 5.11 sec
- ST_Intersects() without Spatial index : 3.84 sec
----------------------------------------
mysql> SHOW VARIABLES LIKE 'version';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| version | 8.0.17 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| 1 | SIMPLE | h27ka23_utf8 | NULL | range | SHAPE | SHAPE | 34 | NULL | 1 | 100.00 | Using where |
+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+---------+-----------+----------+
| OGR_FID | x_code | y_code |
+---------+-----------+----------+
| 1162 | 136.86591 | 35.17593 |
<<snip>>
+---------+-----------+----------+
23 rows in set (5.11 sec)
mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | h27ka23_utf8 | NULL | ALL | NULL | NULL | NULL | NULL | 12834 | 100.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+---------+-----------+----------+
| OGR_FID | x_code | y_code |
+---------+-----------+----------+
| 1038 | 136.86393 | 35.17555 |
<<snip>>
+---------+-----------+----------+
23 rows in set (3.84 sec)
----------------------------------------
[Note]
- I think "ST_Intersects() = ST_Within() + ST_Overlaps()". Therefore, I checked ST_Within() and ST_Overlaps() also.
- ST_Within() is fast. It is no problem.
- ST_Overlaps() is slow. It is a problem.
[Example]
- ST_Within() with Spatial index : 0.00 sec
- ST_Within() without Spatial index : 3.02 sec
- ST_Overlaps() with Spatial index : 4.70 sec
- ST_Overlaps() without Spatial index : 3.20 sec
----------------------------------------
How to repeat:
1. Import dump file. * I'll upload later.
2. Execute following SQLs.
----------------------------------------
[ST_Intersects]
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
----------------------------------------
[ST_Within]
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
----------------------------------------
[ST_Overlaps]
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
----------------------------------------
Suggested fix:
Improve performance of ST_Intersects() and ST_Overlaps().4 hoặc Description:
ST_Intersects() is very slow on MySQL 8.0. (Using Spatial index is slower than not using Spatial index on MySQL 8.0)
[Example]
- ST_Intersects() with Spatial index : 5.11 sec
- ST_Intersects() without Spatial index : 3.84 sec
----------------------------------------
mysql> SHOW VARIABLES LIKE 'version';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| version | 8.0.17 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| 1 | SIMPLE | h27ka23_utf8 | NULL | range | SHAPE | SHAPE | 34 | NULL | 1 | 100.00 | Using where |
+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+---------+-----------+----------+
| OGR_FID | x_code | y_code |
+---------+-----------+----------+
| 1162 | 136.86591 | 35.17593 |
<<snip>>
+---------+-----------+----------+
23 rows in set (5.11 sec)
mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | h27ka23_utf8 | NULL | ALL | NULL | NULL | NULL | NULL | 12834 | 100.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+---------+-----------+----------+
| OGR_FID | x_code | y_code |
+---------+-----------+----------+
| 1038 | 136.86393 | 35.17555 |
<<snip>>
+---------+-----------+----------+
23 rows in set (3.84 sec)
----------------------------------------
[Note]
- I think "ST_Intersects() = ST_Within() + ST_Overlaps()". Therefore, I checked ST_Within() and ST_Overlaps() also.
- ST_Within() is fast. It is no problem.
- ST_Overlaps() is slow. It is a problem.
[Example]
- ST_Within() with Spatial index : 0.00 sec
- ST_Within() without Spatial index : 3.02 sec
- ST_Overlaps() with Spatial index : 4.70 sec
- ST_Overlaps() without Spatial index : 3.20 sec
----------------------------------------
How to repeat:
1. Import dump file. * I'll upload later.
2. Execute following SQLs.
----------------------------------------
[ST_Intersects]
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
----------------------------------------
[ST_Within]
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
----------------------------------------
[ST_Overlaps]
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
----------------------------------------
Suggested fix:
Improve performance of ST_Intersects() and ST_Overlaps().5, giá trị trả về là Description:
ST_Intersects() is very slow on MySQL 8.0. (Using Spatial index is slower than not using Spatial index on MySQL 8.0)
[Example]
- ST_Intersects() with Spatial index : 5.11 sec
- ST_Intersects() without Spatial index : 3.84 sec
----------------------------------------
mysql> SHOW VARIABLES LIKE 'version';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| version | 8.0.17 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| 1 | SIMPLE | h27ka23_utf8 | NULL | range | SHAPE | SHAPE | 34 | NULL | 1 | 100.00 | Using where |
+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+---------+-----------+----------+
| OGR_FID | x_code | y_code |
+---------+-----------+----------+
| 1162 | 136.86591 | 35.17593 |
<<snip>>
+---------+-----------+----------+
23 rows in set (5.11 sec)
mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | h27ka23_utf8 | NULL | ALL | NULL | NULL | NULL | NULL | 12834 | 100.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+---------+-----------+----------+
| OGR_FID | x_code | y_code |
+---------+-----------+----------+
| 1038 | 136.86393 | 35.17555 |
<<snip>>
+---------+-----------+----------+
23 rows in set (3.84 sec)
----------------------------------------
[Note]
- I think "ST_Intersects() = ST_Within() + ST_Overlaps()". Therefore, I checked ST_Within() and ST_Overlaps() also.
- ST_Within() is fast. It is no problem.
- ST_Overlaps() is slow. It is a problem.
[Example]
- ST_Within() with Spatial index : 0.00 sec
- ST_Within() without Spatial index : 3.02 sec
- ST_Overlaps() with Spatial index : 4.70 sec
- ST_Overlaps() without Spatial index : 3.20 sec
----------------------------------------
How to repeat:
1. Import dump file. * I'll upload later.
2. Execute following SQLs.
----------------------------------------
[ST_Intersects]
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
----------------------------------------
[ST_Within]
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
----------------------------------------
[ST_Overlaps]
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
----------------------------------------
Suggested fix:
Improve performance of ST_Intersects() and ST_Overlaps().6. mysql> SET @g1 = Point(1,1), @g2 = Point(2,2);
mysql> SELECT ST_Equals(@g1, @g1), ST_Equals(@g1, @g2);
+---------------------+---------------------+
| ST_Equals(@g1, @g1) | ST_Equals(@g1, @g2) |
+---------------------+---------------------+
| 1 | 0 |
+---------------------+---------------------+
3 và mysql> SET @g1 = Point(1,1), @g2 = Point(2,2);
mysql> SELECT ST_Equals(@g1, @g1), ST_Equals(@g1, @g2);
+---------------------+---------------------+
| ST_Equals(@g1, @g1) | ST_Equals(@g1, @g2) |
+---------------------+---------------------+
| 1 | 0 |
+---------------------+---------------------+
4 là từ đồng nghĩa.
St_Disjoint (________ 3, g2) Trả về 1 hoặc 0 để cho biết liệu g1 có phân tách không gian từ (không giao nhau) g2 hay không.
ST_DISTANCE (________ 3, g2) Trả về khoảng cách giữa g1 và g2. mysql> SET @g1 = Point(1,1);
mysql> SET @g2 = Point(2,2);
mysql> SELECT ST_Distance(@g1, @g2);
+-----------------------+
| ST_Distance(@g1, @g2) |
+-----------------------+
| 1.4142135623730951 |
+-----------------------+
St_equals (________ 3, g2) Trả về 1 hoặc 0 để cho biết liệu g1 có bằng không gian với g2 hay không. mysql> SET @g1 = Point(1,1), @g2 = Point(2,2);
mysql> SELECT ST_Equals(@g1, @g1), ST_Equals(@g1, @g2);
+---------------------+---------------------+
| ST_Equals(@g1, @g1) | ST_Equals(@g1, @g2) |
+---------------------+---------------------+
| 1 | 0 |
+---------------------+---------------------+
St_inters (________ 3, g2) Trả về 1 hoặc 0 để cho biết liệu g1 có giao nhau về mặt không gian g2 hay không. ST_Overlaps (________ 3, g2) Hai hình học chồng chéo về mặt không gian nếu chúng giao nhau và giao điểm của chúng dẫn đến một hình học có cùng một chiều nhưng không bằng một trong hai hình học đã cho.spatially overlap if they intersect and their intersection results in a geometry of the same dimension but not equal to either of
the given geometries. Hàm này trả về 1 hoặc 0 để cho biết liệu g1 có chồng chéo về mặt không gian g2 hay không. ST_Touches (________ 3, g2) Hai hình học chạm vào không gian nếu nội thất của chúng không giao nhau, nhưng ranh giới của một trong các hình học giao nhau với ranh giới hoặc bên trong của cái kia.spatially touch if their interiors do not intersect, but the
boundary of one of the geometries intersects either the boundary or the interior of the other. Hàm này trả về 1 hoặc 0 để cho biết liệu g1 có chạm không gian g2 hay không. ST_Distance()3 và ST_Distance()4 là từ đồng nghĩa. St_within (________ 3, g2) Trả về 1 hoặc 0 để cho biết liệu g1 có phải là không gian trong g2 hay không. Điều này kiểm tra mối quan hệ ngược lại là ST_Distance()9. Chạm (________ 3, g2) ST_Distance()3 và ST_Distance()4 là từ đồng nghĩa. Để biết thêm thông tin, xem mô tả của ST_Distance()3.
Description:
ST_Intersects() is very slow on MySQL 8.0. (Using Spatial index is slower than not using Spatial index on MySQL 8.0)
[Example]
- ST_Intersects() with Spatial index : 5.11 sec
- ST_Intersects() without Spatial index : 3.84 sec
----------------------------------------
mysql> SHOW VARIABLES LIKE 'version';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| version | 8.0.17 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| 1 | SIMPLE | h27ka23_utf8 | NULL | range | SHAPE | SHAPE | 34 | NULL | 1 | 100.00 | Using where |
+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+---------+-----------+----------+
| OGR_FID | x_code | y_code |
+---------+-----------+----------+
| 1162 | 136.86591 | 35.17593 |
<<snip>>
+---------+-----------+----------+
23 rows in set (5.11 sec)
mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | h27ka23_utf8 | NULL | ALL | NULL | NULL | NULL | NULL | 12834 | 100.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
+---------+-----------+----------+
| OGR_FID | x_code | y_code |
+---------+-----------+----------+
| 1038 | 136.86393 | 35.17555 |
<<snip>>
+---------+-----------+----------+
23 rows in set (3.84 sec)
----------------------------------------
[Note]
- I think "ST_Intersects() = ST_Within() + ST_Overlaps()". Therefore, I checked ST_Within() and ST_Overlaps() also.
- ST_Within() is fast. It is no problem.
- ST_Overlaps() is slow. It is a problem.
[Example]
- ST_Within() with Spatial index : 0.00 sec
- ST_Within() without Spatial index : 3.02 sec
- ST_Overlaps() with Spatial index : 4.70 sec
- ST_Overlaps() without Spatial index : 3.20 sec
----------------------------------------
How to repeat:
1. Import dump file. * I'll upload later.
2. Execute following SQLs.
----------------------------------------
[ST_Intersects]
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
----------------------------------------
[ST_Within]
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
----------------------------------------
[ST_Overlaps]
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
SELECT OGR_FID, x_code, y_code FROM geotest2.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((35.175 136.860, 35.170 136.860, 35.170 136.865, 35.175 136.865, 35.175 136.860))', 4612) );
----------------------------------------
Suggested fix:
Improve performance of ST_Intersects() and ST_Overlaps(). |
