- 5.7.27 rm -rf 96311 bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/96311 --log-error-verbosity=3 bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/96311 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/96311/log.err --log-error-verbosity=3 --secure-file-priv="" --character-set-server=utf8 2>&1 & - create user with mysql_native_password CREATE USER 'bug96311'@'%' IDENTIFIED WITH mysql_native_password BY 'mysql123'; grant all on *.* to 'bug96311'@'%'; - create schema CREATE DATABASE geotest CHARACTER SET utf8 COLLATE utf8_general_ci; - import stuff, same as 8.0.17 - I don't have required privileges to install gdal on hod03, installed on local Win10, MySQL Server is hosted on hod03 - Download http://download.gisinternals.com/sdk/downloads/release-1911-x64-gdal-3-0-0-mapserver-7-4-0.zip - Extract and set below path(may vary as per your env) set path=%PATH%;C:\Work\BugsHome\release-1911-x64-gdal-3-0-0-mapserver-7-4-0\bin\gdal\apps set path=%PATH%;C:\Work\BugsHome\release-1911-x64-gdal-3-0-0-mapserver-7-4-0\bin set PROJ_LIB=C:\Work\BugsHome\release-1911-x64-gdal-3-0-0-mapserver-7-4-0\bin\proj6\share C:\Work\BugsHome\release-1911-x64-gdal-3-0-0-mapserver-7-4-0\bin\gdal\apps>ogr2ogr.exe -f "ESRI Shapefile" -lco ENCODING=UTF-8 -oo ENCODING=CP932 h27ka23_utf8.shp h27ka23.shp - This might take a long time if server is hosted remotely C:\Work\BugsHome\release-1911-x64-gdal-3-0-0-mapserver-7-4-0\bin\gdal\apps>ogr2ogr.exe -f "MySQL" MySQL:"geotest,host=hod03.no.oracle.com,user=bug96311,password=mysql123,port=3333" h27ka23_utf8.shp - mysql> select count(*) from h27ka23_utf8; +----------+ | count(*) | +----------+ | 14211 | +----------+ 1 row in set (0.01 sec) show create table geometry_columns\G show create table h27ka23_utf8\G mysql> show create table geometry_columns\G *************************** 1. row *************************** Table: geometry_columns Create Table: CREATE TABLE `geometry_columns` ( `F_TABLE_CATALOG` varchar(256) DEFAULT NULL, `F_TABLE_SCHEMA` varchar(256) DEFAULT NULL, `F_TABLE_NAME` varchar(256) NOT NULL, `F_GEOMETRY_COLUMN` varchar(256) NOT NULL, `COORD_DIMENSION` int(11) DEFAULT NULL, `SRID` int(11) DEFAULT NULL, `TYPE` varchar(256) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> show create table h27ka23_utf8\G *************************** 1. row *************************** Table: h27ka23_utf8 Create Table: CREATE TABLE `h27ka23_utf8` ( `OGR_FID` int(11) NOT NULL AUTO_INCREMENT, `SHAPE` geometry NOT NULL, `key_code` varchar(11) DEFAULT NULL, `pref` varchar(2) DEFAULT NULL, `city` varchar(3) DEFAULT NULL, `s_area` varchar(6) DEFAULT NULL, `pref_name` varchar(12) DEFAULT NULL, `city_name` varchar(15) DEFAULT NULL, `s_name` varchar(96) DEFAULT NULL, `kigo_e` varchar(3) DEFAULT NULL, `hcode` decimal(4,0) DEFAULT NULL, `area` double(19,3) DEFAULT NULL, `perimeter` double(19,3) DEFAULT NULL, `h27kaxx_` decimal(6,0) DEFAULT NULL, `h27kaxx_id` decimal(6,0) DEFAULT NULL, `ken` varchar(2) DEFAULT NULL, `ken_name` varchar(12) DEFAULT NULL, `sityo_name` varchar(22) DEFAULT NULL, `gst_name` varchar(15) DEFAULT NULL, `css_name` varchar(14) DEFAULT NULL, `kihon1` varchar(4) DEFAULT NULL, `dummy1` varchar(1) DEFAULT NULL, `kihon2` varchar(2) DEFAULT NULL, `keycode1` varchar(9) DEFAULT NULL, `keycode2` varchar(9) DEFAULT NULL, `area_max_f` varchar(1) DEFAULT NULL, `kigo_d` varchar(2) DEFAULT NULL, `n_ken` varchar(2) DEFAULT NULL, `n_city` varchar(3) DEFAULT NULL, `kigo_i` varchar(1) DEFAULT NULL, `moji` varchar(96) DEFAULT NULL, `kbsum` decimal(4,0) DEFAULT NULL, `jinko` decimal(10,0) DEFAULT NULL, `setai` decimal(10,0) DEFAULT NULL, `x_code` double(19,5) DEFAULT NULL, `y_code` double(19,5) DEFAULT NULL, `kcode1` varchar(7) DEFAULT NULL, UNIQUE KEY `OGR_FID` (`OGR_FID`), SPATIAL KEY `SHAPE` (`SHAPE`) ) ENGINE=InnoDB AUTO_INCREMENT=14212 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ------------------------------------------------------------------------------------------ [ST_Intersects] EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); ------------------------------------------------------------------------------------------ [ST_Within] EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); [ST_Overlaps] EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); mysql> SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 4612) ); ERROR 3033 (HY000): Binary geometry function st_intersects given two geometries of different srids: 1 and 4612, which should have been identical. https://bugs.mysql.com/bug.php?id=79282 - mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); +----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ | 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 | 4 | 100.00 | Using where | +----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); +---------+-----------+----------+ | OGR_FID | x_code | y_code | +---------+-----------+----------+ | 1162 | 136.86591 | 35.17593 | | 1252 | 136.86624 | 35.17181 | | 1072 | 136.86620 | 35.17126 | | 1143 | 136.86618 | 35.17055 | | 1225 | 136.86614 | 35.16993 | | 1280 | 136.86472 | 35.17391 | | 1283 | 136.86401 | 35.17154 | | 1284 | 136.86397 | 35.17083 | | 1038 | 136.86393 | 35.17555 | | 1309 | 136.86352 | 35.17032 | | 1308 | 136.86348 | 35.16964 | | 1281 | 136.86409 | 35.17282 | | 1163 | 136.86384 | 35.17432 | | 1258 | 136.86256 | 35.17471 | | 1312 | 136.86208 | 35.16955 | | 1259 | 136.86159 | 35.17436 | | 1282 | 136.86219 | 35.17164 | | 1261 | 136.86081 | 35.17253 | | 1310 | 136.86043 | 35.16981 | | 1164 | 136.86062 | 35.17164 | | 1260 | 136.85991 | 35.17435 | | 1189 | 136.85927 | 35.17138 | | 1188 | 136.85919 | 35.17259 | +---------+-----------+----------+ 23 rows in set (0.00 sec) mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 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 | 15003 | 100.00 | Using where | +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Intersects(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); +---------+-----------+----------+ | OGR_FID | x_code | y_code | +---------+-----------+----------+ | 1038 | 136.86393 | 35.17555 | | 1072 | 136.86620 | 35.17126 | | 1143 | 136.86618 | 35.17055 | | 1162 | 136.86591 | 35.17593 | | 1163 | 136.86384 | 35.17432 | | 1164 | 136.86062 | 35.17164 | | 1188 | 136.85919 | 35.17259 | | 1189 | 136.85927 | 35.17138 | | 1225 | 136.86614 | 35.16993 | | 1252 | 136.86624 | 35.17181 | | 1258 | 136.86256 | 35.17471 | | 1259 | 136.86159 | 35.17436 | | 1260 | 136.85991 | 35.17435 | | 1261 | 136.86081 | 35.17253 | | 1280 | 136.86472 | 35.17391 | | 1281 | 136.86409 | 35.17282 | | 1282 | 136.86219 | 35.17164 | | 1283 | 136.86401 | 35.17154 | | 1284 | 136.86397 | 35.17083 | | 1308 | 136.86348 | 35.16964 | | 1309 | 136.86352 | 35.17032 | | 1310 | 136.86043 | 35.16981 | | 1312 | 136.86208 | 35.16955 | +---------+-----------+----------+ 23 rows in set (0.88 sec) mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); +----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ | 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 | 4 | 100.00 | Using where | +----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); +---------+-----------+----------+ | OGR_FID | x_code | y_code | +---------+-----------+----------+ | 1283 | 136.86401 | 35.17154 | | 1284 | 136.86397 | 35.17083 | | 1261 | 136.86081 | 35.17253 | +---------+-----------+----------+ 3 rows in set (0.01 sec) mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 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 | 15003 | 100.00 | Using where | +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Within(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); +---------+-----------+----------+ | OGR_FID | x_code | y_code | +---------+-----------+----------+ | 1261 | 136.86081 | 35.17253 | | 1283 | 136.86401 | 35.17154 | | 1284 | 136.86397 | 35.17083 | +---------+-----------+----------+ 3 rows in set (0.85 sec) mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); +----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ | 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 | 4 | 100.00 | Using where | +----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); +---------+-----------+----------+ | OGR_FID | x_code | y_code | +---------+-----------+----------+ | 1162 | 136.86591 | 35.17593 | | 1252 | 136.86624 | 35.17181 | | 1072 | 136.86620 | 35.17126 | | 1143 | 136.86618 | 35.17055 | | 1225 | 136.86614 | 35.16993 | | 1280 | 136.86472 | 35.17391 | | 1038 | 136.86393 | 35.17555 | | 1309 | 136.86352 | 35.17032 | | 1308 | 136.86348 | 35.16964 | | 1281 | 136.86409 | 35.17282 | | 1163 | 136.86384 | 35.17432 | | 1258 | 136.86256 | 35.17471 | | 1312 | 136.86208 | 35.16955 | | 1259 | 136.86159 | 35.17436 | | 1282 | 136.86219 | 35.17164 | | 1310 | 136.86043 | 35.16981 | | 1164 | 136.86062 | 35.17164 | | 1260 | 136.85991 | 35.17435 | | 1189 | 136.85927 | 35.17138 | | 1188 | 136.85919 | 35.17259 | +---------+-----------+----------+ 20 rows in set (0.00 sec) mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 35.175))', 1) ); +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 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 | 15003 | 100.00 | Using where | +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT OGR_FID, x_code, y_code FROM geotest.h27ka23_utf8 IGNORE INDEX(SHAPE) WHERE ST_Overlaps(SHAPE,ST_GeomFromText('POLYGON((136.860 35.175, 136.860 35.170, 136.865 35.170, 136.865 35.175, 136.860 '> 35.175))', 1) ); +---------+-----------+----------+ | OGR_FID | x_code | y_code | +---------+-----------+----------+ | 1038 | 136.86393 | 35.17555 | | 1072 | 136.86620 | 35.17126 | | 1143 | 136.86618 | 35.17055 | | 1162 | 136.86591 | 35.17593 | | 1163 | 136.86384 | 35.17432 | | 1164 | 136.86062 | 35.17164 | | 1188 | 136.85919 | 35.17259 | | 1189 | 136.85927 | 35.17138 | | 1225 | 136.86614 | 35.16993 | | 1252 | 136.86624 | 35.17181 | | 1258 | 136.86256 | 35.17471 | | 1259 | 136.86159 | 35.17436 | | 1260 | 136.85991 | 35.17435 | | 1280 | 136.86472 | 35.17391 | | 1281 | 136.86409 | 35.17282 | | 1282 | 136.86219 | 35.17164 | | 1308 | 136.86348 | 35.16964 | | 1309 | 136.86352 | 35.17032 | | 1310 | 136.86043 | 35.16981 | | 1312 | 136.86208 | 35.16955 | +---------+-----------+----------+ 20 rows in set (0.89 sec)