外部のKMLデータを簡単にハンドリングしたい

オープンデータなどで自治体から公開される空間情報（GIS）では、KMLやKMZ（KMLをZIP圧縮したもの）を見かけるようになりました。

面倒なデータベースへのロード

KMLはXML形式のテキストデータです。これをデータベースに読み込むには２つの方法があります、

コンピュタープログラムでXMLを読込み、成形した後にデータベースに取り込む
データベースにXML型のデータとして取り込み、成形する

どちらもXMLデータを読込み加工するプロセスを取りますが、「１」の方式ではデータベース以外にプログラミングが必要です。「２」の方法では、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ファイルのフルパス

ｋｍｌを読み込むコツ

名前空間(xmlns)の削除
- ｘｍｌの名前空間の宣言<kml xmlns="URLなど" >があると、XMLデータのロードに失敗することから、COPYコマンドのPROGRAM(PostgreSQL 9.3以降）を用いてsedコマンドでxmlnsをhogeに置換しています。（なぜxmlnsがあるとNGなのかは、私の勉強不足でよく分からない）
改行記号の削除
- COPY文は改行記号で行を判別してしまいます。xml型はxmlドキュメント全体を１行のデータとして取り込む必要がありますので、改行記号が邪魔です。そこでtr -d '\n'でKMLデータの全ての改行記号を削除しています。

空間データの抽出

今回は新潟市のオープンデータ国道を例に説明します。
線形状で表現されていて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

実行の結果、新潟市の国道の長さは１１５ｋｍであることが分かりました。

KMLデータを一気にPostGISで処理する