2023-10-12
【Redisメモ・5】位置情報関数GEOADD・GEORADUSを使う 【Redisメモ・6】GEORADIUS_RO関数をNode.jsから呼ぶ
にて、全国駅データを登録しRedisを使って任意の緯度経度から近い駅を抽出した。
これと全く同じ事をPostgreSQLを使って実現してみる。PostgreSQLのインストールはこちら。
1、PostGISのインストール
PostgreSQLで位置情報型GEOGRAPHY,GEOMETRYを使うにはPostGISという拡張ライブラリをインストールして使う。
apt install postgis
2、データベース作成と関数使用宣言
まず一般ユーザーで普通にデータベース作成
createdb -T template0 -O ippanuser -E UTF-8 ekidb
CREATE EXTENSIONでpostgis使用を付与するが、これはスーパーユーザーでないとできない。
psql -U adminuser ekidb # スーパーユーザで接続する ekidb=# CREATE EXTENSION postgis; CREATE EXTENSION ekidb=# \q
3、駅テーブル作成とインデックス
以降は、一般ユーザーで操作する。
psql -U ippanuser -- 駅テーブル作成 CREATE TABLE ekipos (name VARCHAR(512) PRIMARY KEY, geom GEOGRAPHY(POINT, 4326) NOT NULL); -- GEOインデックスを貼る CREATE INDEX i_ekipos_geom ON ekipos USING GIST (geom);
4326は世界測地系を指定。これはデフォルトなので省略可能。
4、駅データの一括登録
たとえば新宿、新大久保を登録するには以下のようにする。
INSERT INTO ekipos (name, geom) VALUES ('新宿', ST_GeographyFromText('SRID=4326;POINT(139.700464 35.689729)')); INSERT INTO ekipos (name, geom) VALUES ('新大久保', ST_GeographyFromText('SRID=4326;POINT(139.700261 35.700875)'));
あとはこちらのstation_tsv_to_redis.phpをちょこっと変えてPostgreSQL版の上記INSERTを出力するようにする。
PHP | station_tsv_to_postgresql.php | GitHub Source |
<?php date_default_timezone_set('Asia/Tokyo'); $file = new SplFileObject('station20151215free.txt', 'r'); $file->setFlags(SplFileObject::READ_CSV | SplFileObject::SKIP_EMPTY | SplFileObject::READ_AHEAD); $file->setCsvControl("\t"); foreach ($file as $line) { $fields[] = $line; } $n = count($fields); $i = 9; // fields[9]からが実データ for( ; $i < $n; $i++ ){ // fields[i][10]=経度,[11]=緯度,[3]=駅名,[7]=都道府県番号 // 駅名は同一駅名があるため、駅名-都道府県番号 という感じで登録する。 $pref_no = $fields[$i][7]; if(strlen($pref_no) <= 1){ $pref_no = "0" . $pref_no; } echo "INSERT INTO ekipos (name, geom) VALUES (" . "E'" . $fields[$i][3] . "-" . $pref_no . "', " . //駅名-都道府県番号でなく行番号-駅名で登録する場合 //"E'" . ($i - 9 + 1) . "-" . $fields[$i][3] . "', " . "ST_GeographyFromText('SRID=4326;POINT(" . $fields[$i][10] . " " . $fields[$i][11] . ")'" . "));\n"; }
station20151215free.txtを設置したディレクトリで実行、出力をファイルに落とします。
php station_tsv_to_postgresql.php > station_tsv_to_postgresql.out.txt head -n 3 station_tsv_to_postgresql.out.txt INSERT INTO ekipos (name, geom) VALUES (E'函館-01', ST_GeographyFromText('SRID=4326;POINT(140.726413 41.773709)')); INSERT INTO ekipos (name, geom) VALUES (E'五稜郭-01', ST_GeographyFromText('SRID=4326;POINT(140.733539 41.803557)')); INSERT INTO ekipos (name, geom) VALUES (E'桔梗-01', ST_GeographyFromText('SRID=4326;POINT(140.722952 41.846457)')); tail -n 3 station_tsv_to_postgresql.out.txt INSERT INTO ekipos (name, geom) VALUES (E'出光美術館-40', ST_GeographyFromText('SRID=4326;POINT(130.965292 33.947792)')); INSERT INTO ekipos (name, geom) VALUES (E'ノーフォーク広場-40', ST_GeographyFromText('SRID=4326;POINT(130.964254 33.955973)')); INSERT INTO ekipos (name, geom) VALUES (E'関門海峡めかり-40', ST_GeographyFromText('SRID=4326;POINT(130.967347 33.960627)'));
これで全国分の駅名経度緯度GEOADD命令がstation_tsv_to_postgresql.out.txtに入りました。ではインサートします。
psql -U ippanuser -f station_tsv_to_postgresql.out.txt ekidb ... psql:station_tsv_to_postgresql.out.txt:7599: ERROR: duplicate key value violates unique constraint "ekipos_pkey" DETAIL: Key (name)=(新宿-13) already exists. ...
重複エラーがたくさん出ますが、理由はRedisの時と同じで駅名も都道府県も同じ=新宿のようなターミナル駅=JR新宿、丸の内線新宿、があれば後者はエラーになります。
緯度経度が極めて近い後者は今回の用途では不要なものなので、エラーは無視して構いません。
SELECT COUNT(*) FROM ekipos; count ------- 9210 (1 row)
Redisのときと全く同じ駅数がしっかり登録されたことが確認できました。
5、検索実行
では、イオンスタイル入間から半径5km以内の駅を近い順に20個検索してみます。以下のようにします。
SELECT name, ST_X(geom::geometry) AS longitude, ST_Y(geom::geometry) AS latitude, ST_Distance('SRID=4326;POINT(139.394872 35.822019)', geom) AS distance FROM ekipos WHERE ST_DWithin(geom, ST_Geograp hyFromText('SRID=4326;POINT(139.394872 35.822019)'), 5000.0) ORDER BY distance LIMIT 20; name | longitude | latitude | distance ---------------+------------+-----------+--------------- 武蔵藤沢-11 | 139.412736 | 35.820963 | 1618.55278664 入間市-11 | 139.390294 | 35.842904 | 2353.94189341 狭山ヶ丘-11 | 139.416975 | 35.810445 | 2374.69603638 稲荷山公園-11 | 139.39842 | 35.845112 | 2582.28061472 入曽-11 | 139.427304 | 35.832481 | 3152.08537677 仏子-11 | 139.360115 | 35.83769 | 3589.75772324 狭山市-11 | 139.413015 | 35.856936 | 4206.73664685 小手指-11 | 139.438016 | 35.800535 | 4570.19158489 元加治-11 | 139.345316 | 35.84058 | 4928.5575168 (9 rows)
直線距離の結果にRedisと若干のずれはありますが、同じ結果が得られました。
次に、イオンスタイル入間から日本で最も遠い駅を遠い順に5つ出すには以下。
SELECT name, ST_X(geom::geometry) AS longitude, ST_Y(geom::geometry) AS latitude, ST_Distance('SRID=4326;POINT(139.394872 35.822019)', geom) AS distance FROM ekipos WHERE ST_DWithin(geom, ST_Geograp hyFromText('SRID=4326;POINT(139.394872 35.822019)'), 3000000.0) ORDER BY distance DESC LIMIT 5; name | longitude | latitude | distance ---------------+------------+-----------+------------------ 赤嶺-47 | 127.660348 | 26.193289 | 1545339.41848242 那覇空港-47 | 127.652214 | 26.206515 | 1544836.37333821 小禄-47 | 127.666853 | 26.196455 | 1544637.50847977 奥武山公園-47 | 127.675252 | 26.200714 | 1543717.51608979 壺川-47 | 127.678344 | 26.205927 | 1543085.77082237 (5 rows)
無事、Redisと同じ結果が返りました。distanceはメートルで表現されています。
※本記事内容の無断転載を禁じます。
ご連絡は以下アドレスまでお願いします★
さくらインターネットでPython MecabをCGIから使う
さくらインターネットのPHPでAnalytics-G4 APIを使う
インクルードパスの調べ方
【Git】特定ファイルを除外する.gitignore
【Ubuntu/Debian】NVIDIA関係のドライバを自動アップデートさせない
【Python】Spacyを使用して文章から出発地と目的地を抜き出す
HomeBrewでApache2を入れて自動起動つきで動かしPHPモジュールと連携する
macOSに標準付属のApacheを自動起動つきで動かす
HomeBrewでPostgreSQLを入れて自動起動つきで動かす
Windows版Google Driveが使用中と言われアンインストールできない場合
【C/C++】小数点以下の切り捨て・切り上げ・四捨五入
進研ゼミチャレンジタッチをAndroid端末化する
Windows11+WSL2でUbuntuを使う【2】ブリッジ接続+固定IPの設定
Googleスプレッドシートで図形をコピーして使いまわすには
【Linux】iconv/libiconvをソースコードからインストール
【Apache】サーバーに同時接続可能なクライアント数を調整する
Pythonで処理にかかった時間を計測するには
Windows11のコマンドプロンプトでテキストをコピーする