Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
MySQLのGISについてのちょっとした話
Search
hmatsu47
PRO
September 26, 2020
Technology
0
110
MySQLのGISについてのちょっとした話
FOSS4G 2020 Hokkaido 懇親会 LT
hmatsu47
PRO
September 26, 2020
Tweet
Share
More Decks by hmatsu47
See All by hmatsu47
BuriKaigi2024 にボランティアスタッフとして参加した話
hmatsu47
PRO
0
47
Aurora MySQL と Redshift の zero-ETL 統合のフィルター機能を試してみた
hmatsu47
PRO
0
19
Aurora MySQL 3.06 の ML 機能で Bedrock アクセスを試してみた
hmatsu47
PRO
0
31
RDS Data API と Aurora zero-ETL 統合と BuriKaigi2024 の話
hmatsu47
PRO
0
13
RDS Data API のその後と Aurora zero-ETL 統合のデータ転送処理の話
hmatsu47
PRO
0
38
RDS_Aurora 関連アップデート 2023 版
hmatsu47
PRO
0
69
人工無能たいたん
hmatsu47
PRO
0
61
20 世紀末の地方税理士事務所で IT 導入の 1 → 10 を頑張った話
hmatsu47
PRO
0
37
パソコン通信むかしばなし
hmatsu47
PRO
0
190
Other Decks in Technology
See All in Technology
コンパウンドスタートアップのためのスケーラブルでセキュアなInfrastructure as Codeパイプラインを考える / Scalable and Secure Infrastructure as Code Pipeline for a Compound Startup
yuyatakeyama
3
2.7k
Google Cloud の AI を支える裏側のインフラを垣間見る!
maroon1st
0
200
Hands-on / Kaname Frusawa / Cloud Compare Users Meetup 2024 at University of Tokyo on April 17
paraworld
2
470
インシデントレスポンスのライフサイクルを廻すポイントってなに / Pinpoints of Incidentresponse Lifecycle for Operation
sakaitakeshi
1
300
テストプロセスで大事にしていること #jasstnano
makky_tyuyan
0
130
PHPカンファレンス小田原2024
ysknsid25
3
660
SPI原点回帰論:事業課題とFour Keysの結節点を見出す実践的ソフトウェアプロセス改善 / DevOpsDays Tokyo 2024
visional_engineering_and_design
4
1.6k
アクセシビリティを考慮したUI/CSSフレームワーク・ライブラリ選定
yajihum
0
170
Autonomous Database Cloud 技術詳細 / adb-s_technical_detail_jp
oracle4engineer
PRO
14
35k
アプリがつくるNOT A HOTELブランド
hokuts
1
450
プロトタイピングによる不確実性の低減 / Reducing Uncertainty through Prototyping
ohbarye
3
240
NgRx Signal Store
rainerhahnekamp
0
120
Featured
See All Featured
The Cost Of JavaScript in 2023
addyosmani
14
3.8k
What's in a price? How to price your products and services
michaelherold
237
11k
Designing for Performance
lara
601
67k
Intergalactic Javascript Robots from Outer Space
tanoku
266
26k
Into the Great Unknown - MozCon
thekraken
10
980
Facilitating Awesome Meetings
lara
41
5.6k
Sharpening the Axe: The Primacy of Toolmaking
bcantrill
15
1.4k
A designer walks into a library…
pauljervisheath
199
23k
Automating Front-end Workflow
addyosmani
1355
200k
The World Runs on Bad Software
bkeepers
PRO
61
6.7k
Dealing with People You Can't Stand - Big Design 2015
cassininazir
356
22k
The Art of Programming - Codeland 2020
erikaheidi
41
12k
Transcript
None
https://qiita.com/hmatsu47 ◦ https://qiita.com/hmatsu47/items/ceb75caf46e3c761095d ◦ ◦ https://booth.pm/ja/items/2062599 2
• • • • ◦ • 3
• • • • ◦ 4
• • • • • http://isucon.net/ 5
https://qiita.com/miyauchi/items/893f12679cb21c12c454 6
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html#mysqld-8-0-20-bug 7
◦ https://nlftp.mlit.go.jp/ksj/index.html ◦ ▪ https://qiita.com/miyauchi/items/b4e810b3becf2cf07e2f https://github.com/hajime-miyauchi/shp2mysql 8
9 mysql> SHOW CREATE TABLE `n03-19_190101`\G *************************** 1. row ***************************
Table: n03-19_190101 Create Table: CREATE TABLE `n03-19_190101` ( `gid` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `n03_001` varchar(10) DEFAULT NULL, `n03_002` varchar(20) DEFAULT NULL, `n03_003` varchar(20) DEFAULT NULL, `n03_004` varchar(20) DEFAULT NULL, `n03_007` varchar(5) DEFAULT NULL, `geom` multipolygon /*!80003 SRID 4612 */ DEFAULT NULL, PRIMARY KEY (`gid`), UNIQUE KEY `gid` (`gid`) ) ENGINE=InnoDB AUTO_INCREMENT=117581 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> ALTER TABLE `n03-19_190101` MODIFY COLUMN `geom` multipolygon NOT NULL SRID 4612, ADD SPATIAL INDEX `geom_index` (`geom`); Query OK, 117580 rows affected (2 min 13.78 sec) Records: 117580 Duplicates: 0 Warnings: 0
10 mysql> EXPLAIN SELECT CONCAT(`n03_001`, `n03_003`, `n03_004`) AS `city` FROM
`n03-19_190101` WHERE ST_Intersects(`geom`, ST_GeomFromText('POINT(43.06 141.34)', 4612)); +----+-------------+---------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | n03-19_190101 | NULL | range | geom_index | geom_index | 34 | NULL | 1 | 100.00 | Using where | +----+-------------+---------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT CONCAT(`n03_001`, `n03_003`, `n03_004`) AS `city` FROM `n03-19_190101` WHERE ST_Intersects(`geom`, ST_GeomFromText('POINT(43.06 141.34)', 4612)); +-----------------------------+ | city | +-----------------------------+ | 北海道札幌市中央区 | +-----------------------------+ 1 row in set (29.60 sec)
11 mysql> EXPLAIN SELECT CONCAT(`n03_001`, `n03_003`, `n03_004`) AS `city` FROM
`n03-19_190101` WHERE ST_Intersects(`geom`, ST_GeomFromText('POINT(43.06 141.34)', 4612)); +----+-------------+---------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | n03-19_190101 | NULL | range | geom_index | geom_index | 34 | NULL | 1 | 100.00 | Using where | +----+-------------+---------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.02 sec) mysql> SELECT CONCAT(`n03_001`, `n03_003`, `n03_004`) AS `city` FROM `n03-19_190101` WHERE ST_Intersects(`geom`, ST_GeomFromText('POINT(43.06 141.34)', 4612)); Empty set (0.38 sec)
12 mysql> EXPLAIN SELECT CONCAT(`n03_001`, `n03_003`, `n03_004`) AS `city` FROM
`n03-19_190101` WHERE ST_Intersects(`geom`, ST_GeomFromText('POINT(43.06 141.34)', 4612)); +----+-------------+---------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | n03-19_190101 | NULL | range | geom_index | geom_index | 34 | NULL | 1 | 100.00 | Using where | +----+-------------+---------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.02 sec) mysql> SELECT CONCAT(`n03_001`, `n03_003`, `n03_004`) AS `city` FROM `n03-19_190101` WHERE ST_Intersects(`geom`, ST_GeomFromText('POINT(43.06 141.34)', 4612)); Empty set (0.38 sec)
13
14 mysql> SELECT CONCAT(`n03_001`, `n03_003`, `n03_004`) AS `city` FROM `n03-19_190101`
WHERE ST_Intersects(`geom`, ST_GeomFromText('POLYGON((43.05 141.33, 43.07 141.33, 43.07 141.35, 43.05 141.35, 43.05 141.33))', 4612)); +--------------------------+ | city | +--------------------------+ | 北海道札幌市北区 | +--------------------------+ 1 row in set (0.50 sec) mysql> SELECT CONCAT(`n03_001`, `n03_003`, `n03_004`) AS `city` FROM `n03-19_190101` WHERE ST_Within(ST_GeomFromText('POINT(43.06 141.34)', 4612), `geom`); Empty set (0.00 sec)
15
◦ ◦ https://fukuno.jig.jp/app/printmap/latlngmap.html https://fukuno.jig.jp/1330 16
17 mysql> ALTER TABLE `n03-19_190101` DROP INDEX `geom_index`; Query OK,
0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE `n03-19_190101` ADD SPATIAL INDEX `geom_index` (`geom`); Query OK, 0 rows affected (2 min 27.82 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT CONCAT(`n03_001`, `n03_003`, `n03_004`) AS `city` FROM `n03-19_190101` WHERE ST_Intersects(`geom`, ST_GeomFromText('POINT(43.06 141.34)', 4612)); +-----------------------------+ | city | +-----------------------------+ | 北海道札幌市中央区 | +-----------------------------+ 1 row in set (0.02 sec)
mysql> ALTER TABLE `n03-19_190101` DROP INDEX `geom_index`; Query OK, 0
rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE `n03-19_190101` ADD SPATIAL INDEX `geom_index` (`geom`); Query OK, 0 rows affected (2 min 27.82 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT CONCAT(`n03_001`, `n03_003`, `n03_004`) AS `city` FROM `n03-19_190101` WHERE ST_Intersects(`geom`, ST_GeomFromText('POINT(43.06 141.34)', 4612)); +-----------------------------+ | city | +-----------------------------+ | 北海道札幌市中央区 | +-----------------------------+ 1 row in set (0.02 sec) 18
mysql> SELECT CONCAT(`n03_001`, `n03_003`, `n03_004`) AS `city` FROM `n03-19_190101` WHERE
ST_Intersects(`geom`, ST_GeomFromText('POLYGON((43.05 141.33, 43.07 141.33, 43.07 141.35, 43.05 141.35, 43.05 141.33))', 4612)); +-----------------------------+ | city | +-----------------------------+ | 北海道札幌市北区 | | 北海道札幌市中央区 | +-----------------------------+ 2 rows in set (0.02 sec) mysql> SELECT CONCAT(`n03_001`, `n03_003`, `n03_004`) AS `city` FROM `n03-19_190101` WHERE ST_Within(ST_GeomFromText('POINT(43.06 141.34)', 4612), `geom`); +-----------------------------+ | city | +-----------------------------+ | 北海道札幌市中央区 | +-----------------------------+ 1 row in set (0.01 sec) 19
• • ◦ 20
• • • 21