トグルホールディングスの政岡です。トグルアドベントカレンダー21日目の記事です。元記事はこちらです。

概要

DuckDBでPostGISのような空間関数を使用してデータの前処理をしてみます。

今回は、駅データ.jpの駅名データを使用して、位置情報を含むcsvファイルを取り扱います。複数路線が存在する駅は同じ駅名で複数地点登録されているため、これを整理します。路線情報を取り除き、単一の駅名のみを抽出することが目的です。具体的には、JR山手線の東京駅と東京メトロ銀座線の東京駅が一つの「東京駅」の代表点で一意となるようにデータを整理します。操作としては、複数地点の緯度経度座標の重心をとって、重複する駅名を取り除きます。

バージョンとディレクトリ構成

# version
duckdb v1.1.3

データは./dataディレクトリに配置します。

データインポート

今回は駅データ.jpの駅名データを使用します。

csvファイルからデータを取り込んで、列名一覧を取得してみます。 show tbl;で列名の一覧を表示できます。

show select * from './data/stations.csv';
┌────────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│  column_name   │ column_type │  null   │   key   │ default │  extra  │
│    varchar     │   varchar   │ varchar │ varchar │ varchar │ varchar │
├────────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ station_cd     │ BIGINT      │ YES     │         │         │         │
│ station_g_cd   │ BIGINT      │ YES     │         │         │         │
│ station_name   │ VARCHAR     │ YES     │         │         │         │
│ station_name_k │ VARCHAR     │ YES     │         │         │         │
│ station_name_r │ VARCHAR     │ YES     │         │         │         │
│ line_cd        │ BIGINT      │ YES     │         │         │         │
│ pref_cd        │ BIGINT      │ YES     │         │         │         │
│ post           │ VARCHAR     │ YES     │         │         │         │
│ address        │ VARCHAR     │ YES     │         │         │         │
│ lon            │ DOUBLE      │ YES     │         │         │         │
│ lat            │ DOUBLE      │ YES     │         │         │         │
│ open_ymd       │ VARCHAR     │ YES     │         │         │         │
│ close_ymd      │ VARCHAR     │ YES     │         │         │         │
│ e_status       │ BIGINT      │ YES     │         │         │         │
│ e_sort         │ BIGINT      │ YES     │         │         │         │
├────────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┤
│ 15 rows                                                    6 columns │
└──────────────────────────────────────────────────────────────────────┘

今回の目的は、路線情報を取り除き、単一の駅名のみを抽出することなので、これに必要な列を取得します。station_g_cdは駅のグループIDです。後で使用するので取り込んでおきます。

SELECT
        station_g_cd   -- 駅グループID
        ,station_name   -- 駅名
        ,address        -- 駅の住所
        ,lon            -- 駅の軽度
        ,lat            --  駅の緯度
FROM
        './data/stations.csv'
LIMIT 3;

┌──────────────┬──────────────┬─────────────────────────────────┬────────────┬───────────┐
│ station_g_cd │ station_name │             address             │    lon     │    lat    │
│    int64     │   varchar    │             varchar             │   double   │  double   │
├──────────────┼──────────────┼─────────────────────────────────┼────────────┼───────────┤
│      1110101 │ 函館         │ 北海道函館市若松町１２-１３     │ 140.726413 │ 41.773709 │
│      1110102 │ 五稜郭       │ 函館市亀田本町                  │ 140.733539 │ 41.803557 │
│      1110103 │ 桔梗         │ 北海道函館市桔梗３丁目４１-３６ │ 140.722952 │ 41.846457 │
└──────────────┴──────────────┴─────────────────────────────────┴────────────┴───────────┘

よく見るデータベースの形式になりました。

地理空間情報を取り扱う

GEOMETRY型へ変換

住所のすべて位置がlon, lat列に分離していて取り扱いづらいので、GEOMETRY型にしてみましょう。 GEOMETRY型を取り扱うには、spatial拡張が必要なのでインストールとロードもしておきます。

INSTALL spatial;
LOAD spatial;

-- GEOMETRY型へ変換
SELECT ST_Point(lon, lat) AS geom FROM './data/stations.csv' LIMIT 3;
┌──────────────────────────────┐
│             geom             │
│           geometry           │
├──────────────────────────────┤
│ POINT (140.726413 41.773709) │
│ POINT (140.733539 41.803557) │
│ POINT (140.722952 41.846457) │
└──────────────────────────────┘

無事GEOMETRY型になりました。 GEOMETRY型はGeoJSONに対応しています。lon, latのような列構造になっていると、多角形などを表現するのが非常に大変なので、GEOMETRY型にしています。

中間テーブルの生成

ここでstationテーブルにデータを格納しておきます。 CREATE TABLE [table name]で新しいテーブルを作成できます。

CREATE OR REPLACE TABLE station AS
SELECT
    station_g_cd    -- 駅グループID
    ,station_name   -- 駅名
    ,address        -- 駅の住所
    ,ST_Point(lon, lat) AS geom
FROM
    './data/stations.csv';

地理空間関数を使用する

まずは、駅名が重複していることを確認します。試しに駅名の重複が11個以上のものを集めてみます。

SELECT
    t.num
    ,station_name
FROM (
    SELECT
        count(station_name) as num
        ,station_name
    FROM
        station
    GROUP BY
        station_name
    ) t
WHERE
    t.num >= 11;                                                                                    
┌───────┬──────────────┐
│  num  │ station_name │
│ int64 │   varchar    │
├───────┼──────────────┤
│    11 │ 横浜         │
│    11 │ 渋谷         │
│    11 │ 県庁前       │
│    16 │ 市役所前     │
│    12 │ 東京         │
│    13 │ 新宿         │
└───────┴──────────────┘

東京駅に集中してみます。

SELECT
        station_g_cd
        ,station_name
        ,address
        ,geom
FROM
        station
WHERE regexp_matches(station_name, '^東京$');

┌──────────────┬──────────────┬───────────────────────────────┬──────────────────────────────┐
│ station_g_cd │ station_name │            address            │             geom             │
│    int64     │   varchar    │            varchar            │           geometry           │
├──────────────┼──────────────┼───────────────────────────────┼──────────────────────────────┤
│      1130101 │ 東京         │ 東京都千代田区丸の内一丁目    │ POINT (139.766103 35.681391) │
│      1130101 │ 東京         │ 東京都千代田区丸の内一丁目9-1 │ POINT (139.766103 35.681391) │
│      1130101 │ 東京         │ 東京都千代田区丸の内一丁目    │ POINT (139.766103 35.681391) │
│      1130101 │ 東京         │ 東京都千代田区丸の内一丁目    │ POINT (139.766103 35.681391) │
...

station_g_cdは同一の駅に与えられるIDなので、この列でグループ化して緯度経度の重心をとってみます。複数の点が一つの点に集約されました。

SELECT
    ST_Centroid(ST_Collect(ARRAY_AGG(geom))) AS centroid
FROM
    station
WHERE
    regexp_matches(station_name, '^東京$');

┌───────────────────────────────────────────────┐
│                   centroid                    │
│                   geometry                    │
├───────────────────────────────────────────────┤
│ POINT (139.76598674999994 35.681421166666674) │
└───────────────────────────────────────────────┘

上記の操作をすべてまとめてすべてのデータに適用すると以下のようになります。

CREATE OR REPLACE TABLE station_centroid AS
SELECT
    station_g_cd
    ,station_name
    ,ST_Centroid(
        ST_Collect(ARRAY_AGG(
            ST_Point(lon, lat)
            )
        )
    ) AS geom
FROM
    './data/stations.csv'
GROUP BY
    station_g_cd, station_name;

東京駅で確認してみると、緯度経度の重心で集約されているのがわかります。

 SELECT * FROM station_centroid
  WHERE regexp_matches(station_name, '^東京$');
┌──────────────┬──────────────┬───────────────────────────────────────────────┐
│ station_g_cd │ station_name │                     geom                      │
│    int64     │   varchar    │                   geometry                    │
├──────────────┼──────────────┼───────────────────────────────────────────────┤
│      1130101 │ 東京         │ POINT (139.76598674999994 35.681421166666674) │
└──────────────┴──────────────┴───────────────────────────────────────────────┘

データエクスポート

最後にこれをcsv形式で出力します。

COPY station_centroid TO './data/station_centroid.csv' (HEADER, DELIMITER ',');

GeoJSON形式で出力することも可能です。

COPY station_centroid TO './data/station_centroid.geojson'
WITH (FORMAT GDAL, DRIVER 'GeoJSON');

まとめ

DuckDBを使用することで簡単にSQLでデータの前処理を行うことができました。SQL使いたいけど、PostgreSQLの環境構築するのは面倒だなというときに是非活用してみてください。

toggle Engineer Blog

トグルホールディングス株式会社のエンジニアブログでは、私たちの技術的な挑戦やプロジェクトの裏側、チームの取り組みをシェアします。

データの前処理はDuckDBでー位置情報データも取り扱うー

概要