- 8.0.17 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 --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv=/tmp/ 2>&1 & - create user with mysql_native_password CREATE USER 'bug96311'@'%' IDENTIFIED WITH mysql_native_password BY 'mysql123'; grant all on *.* to 'bug96311'@'%'; - 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 C:\Work\BugsHome\release-1911-x64-gdal-3-0-0-mapserver-7-4-0\bin\gdal\apps> C:\Work\BugsHome\release-1911-x64-gdal-3-0-0-mapserver-7-4-0\bin\gdal\apps> C:\Work\BugsHome\release-1911-x64-gdal-3-0-0-mapserver-7-4-0\bin\gdal\apps> C:\Work\BugsHome\release-1911-x64-gdal-3-0-0-mapserver-7-4-0\bin\gdal\apps> C:\Work\BugsHome\release-1911-x64-gdal-3-0-0-mapserver-7-4-0\bin\gdal\apps> - mysql> select count(*) from h27ka23_utf8; +----------+ | count(*) | +----------+ | 14211 | +----------+ 1 row in set (0.01 sec) use geotest; ALTER TABLE h27ka23_utf8 DROP INDEX SHAPE; ALTER TABLE h27ka23_utf8 MODIFY SHAPE GEOMETRY NOT NULL SRID 4612; ALTER TABLE h27ka23_utf8 ADD SPATIAL INDEX (SHAPE); mysql> mysql> use geotest; Database changed mysql> ALTER TABLE h27ka23_utf8 DROP INDEX SHAPE; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE h27ka23_utf8 MODIFY SHAPE GEOMETRY NOT NULL SRID 4612; Query OK, 14211 rows affected (0.47 sec) Records: 14211 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE h27ka23_utf8 ADD SPATIAL INDEX (SHAPE); Query OK, 0 rows affected (9.14 sec) Records: 0 Duplicates: 0 Warnings: 0 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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 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 /*!80003 SRID 4612 */, `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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 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((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 geotest.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 geotest.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 geotest.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) ); mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest.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.02 sec) mysql> SELECT OGR_FID, x_code, y_code FROM geotest.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 | | 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 | | 1312 | 136.86208 | 35.16955 | | 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 | | 1258 | 136.86256 | 35.17471 | | 1259 | 136.86159 | 35.17436 | +---------+-----------+----------+ 23 rows in set (3.23 sec) mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest.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 | 13970 | 100.00 | Using where | +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) mysql> SELECT OGR_FID, x_code, y_code FROM geotest.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 | | 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 (2.23 sec) - [ST_Within] EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest.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 geotest.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 geotest.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 geotest.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) ); mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest.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) ); +----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ | 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 geotest.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) ); +---------+-----------+----------+ | 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.02 sec) mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest.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) ); +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 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 | 13970 | 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((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 | +---------+-----------+----------+ | 1261 | 136.86081 | 35.17253 | | 1283 | 136.86401 | 35.17154 | | 1284 | 136.86397 | 35.17083 | +---------+-----------+----------+ 3 rows in set (2.08 sec) - [ST_Overlaps] EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest.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 geotest.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 geotest.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 geotest.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) ); mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest.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) ); +----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+ | 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 geotest.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) ); +---------+-----------+----------+ | 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 | | 1312 | 136.86208 | 35.16955 | | 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 | | 1258 | 136.86256 | 35.17471 | | 1259 | 136.86159 | 35.17436 | +---------+-----------+----------+ 20 rows in set (3.20 sec) mysql> EXPLAIN SELECT OGR_FID, x_code, y_code FROM geotest.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) ); +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 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 | 13970 | 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((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 | | 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 (2.22 sec) mysql> show variables like '%version%'; +--------------------------+------------------------------+ | Variable_name | Value | +--------------------------+------------------------------+ | immediate_server_version | 999999 | | innodb_version | 8.0.17 | | original_server_version | 999999 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1,TLSv1.1,TLSv1.2 | | version | 8.0.17 | | version_comment | MySQL Community Server - GPL | | version_compile_machine | x86_64 | | version_compile_os | linux-glibc2.12 | | version_compile_zlib | 1.2.11 | +--------------------------+------------------------------+ 11 rows in set (0.00 sec) mysql> system cat docs/INFO_SRC commit: a5b2e523c881ab8c68fd0adb478a6232dd69e522 date: 2019-06-25 11:50:40 +0200 build-date: 2019-06-25 12:24:04 +0200 short: a5b2e52 branch: mysql-8.0.17-release MySQL source 8.0.17