外部のKMLデータを簡単にハンドリングしたい
オープンデータなどで自治体から公開される空間情報(GIS)では、KMLやKMZ(KMLをZIP圧縮したもの)を見かけるようになりました。
面倒なデータベースへのロード
KMLはXML形式のテキストデータです。これをデータベースに読み込むには2つの方法があります、
- コンピュタープログラムでXMLを読込み、成形した後にデータベースに取り込む
- データベースにXML型のデータとして取り込み、成形する
どちらもXMLデータを読込み加工するプロセスを取りますが、「1」の方式ではデータベース以外にプログラミングが必要です。「2」の方法では、XMLを取り込んでからは全てデータベースのSQLで記述します。
PostgreSQLにKMLデータをロード
PostgreSQLにはXML型が定義してあり、XMLデータを読込・検索・加工が可能です。XML型のテーブルを作成し、データをロードします。
STEP1_create_table.sql
--XML型のテーブルを作成する
CREATE TABLE t_kml(kml xml);
COPY文を使って、外部ファイルのKMLをテーブルに読み込みます。
(MACやLINUX環境が前提です。Windowsの場合はCygwinなどでcat,sed,trが使えるようにして下さい。)
STEP2_copy_from_kml.sql
COPY t_kml FROM
PROGRAM 'cat /tmp/od_gis_10121_kokudo.kml | sed -e ''s/xmlns=[^"]*\"/\hoge="/g'' | tr -d ''\n'' '
DELIMITER '*';
-- /tmp/od_gis_10121_kokudo.kml がkmlファイルのフルパス
kmlを読み込むコツ
- 名前空間(xmlns)の削除
- xmlの名前空間の宣言
<kml xmlns="URLなど" >
があると、XMLデータのロードに失敗することから、COPYコマンドのPROGRAM
(PostgreSQL 9.3以降)を用いてsedコマンドでxmlnsをhogeに置換しています。(なぜxmlnsがあるとNGなのかは、私の勉強不足でよく分からない)
- xmlの名前空間の宣言
- 改行記号の削除
- COPY文は改行記号で行を判別してしまいます。xml型はxmlドキュメント全体を1行のデータとして取り込む必要がありますので、改行記号が邪魔です。そこで
tr -d '\n'
でKMLデータの全ての改行記号を削除しています。
- COPY文は改行記号で行を判別してしまいます。xml型はxmlドキュメント全体を1行のデータとして取り込む必要がありますので、改行記号が邪魔です。そこで
空間データの抽出
今回は新潟市のオープンデータ国道を例に説明します。
線形状で表現されていてLineString
というタグの中に格納されています。xpath関数を用いて、xmlの中のLineString
タグを抽出してみます。
STEP3_select_LineString.sql
SELECT
unnest( line_data )::text AS line
FROM
(SELECT
xpath(E'//LineString' , kml ) AS line_data
FROM t_kml
) AS main ;
- 解説
xpath(E'//LineString' , kml )
- kml列に格納されているXMLデータからLineStringタグを抽出します
- 戻り値はxml配列型
- 解説
unnest( line_data )::text
- unnsetは配列型のデータを複数の行に変換する変換する関数です
空間分析
抽出したLineStringタグを空間情報に変換して、空間分析を実施します。
STEP4_get_legth.sql
WITH base AS (
SELECT
unnest(pm)::text AS line
FROM (SELECT xpath(E'//LineString' , kml ) AS pm FROM t_kml ) AS main
)
SELECT
SUM( ST_Length( ST_GeomFromKML( line ) ::geography ) )/1000 AS km_length , COUNT(*) AS road_link_num
FROM
base
;
- 解説 :
ST_GeomFromKML( line )
- ST_GeomFromKMLはKMLのタグを投入すると空間情報(geometry型)に変換します
- 解説 :
ST_Length
- 線分形状の長さを算出する関数です。geography型のデータは
実行結果
km_length | road_link_num |
---|---|
115.57520633318 | 35 |
実行の結果、新潟市の国道の長さは115kmであることが分かりました。