POSTED BY
2023-10-12

【Redisメモ・５】位置情報関数GEOADD・GEORADUSを使う
【Redisメモ・６】GEORADIUS_RO関数をNode.jsから呼ぶ

にて、全国駅データを登録しRedisを使って任意の緯度経度から近い駅を抽出した。
これと全く同じ事をPostgreSQLを使って実現してみる。PostgreSQLのインストールはこちら。

１、PostGISのインストール

PostgreSQLで位置情報型GEOGRAPHY,GEOMETRYを使うにはPostGISという拡張ライブラリをインストールして使う。

apt install postgis

２、データベース作成と関数使用宣言

まず一般ユーザーで普通にデータベース作成

createdb -T template0 -O ippanuser -E UTF-8 ekidb

CREATE EXTENSIONでpostgis使用を付与するが、これはスーパーユーザーでないとできない。

psql -U adminuser ekidb # スーパーユーザで接続する
ekidb=＃ CREATE EXTENSION postgis;
CREATE EXTENSION
ekidb=＃ \q

３、駅テーブル作成とインデックス

以降は、一般ユーザーで操作する。

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は世界測地系を指定。これはデフォルトなので省略可能。

４、駅データの一括登録

たとえば新宿、新大久保を登録するには以下のようにする。

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 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の時と同じで駅名も都道府県も同じ＝新宿のようなターミナル駅＝ＪＲ新宿、丸の内線新宿、があれば後者はエラーになります。
緯度経度が極めて近い後者は今回の用途では不要なものなので、エラーは無視して構いません。

SELECT COUNT(*) FROM ekipos;
 count
-------
  9210
(1 row)

Redisのときと全く同じ駅数がしっかり登録されたことが確認できました。

５、検索実行

では、イオンスタイル入間から半径５ｋｍ以内の駅を近い順に２０個検索してみます。以下のようにします。

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と若干のずれはありますが、同じ結果が得られました。

次に、イオンスタイル入間から日本で最も遠い駅を遠い順に５つ出すには以下。

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はメートルで表現されています。