mod_spatialiteのススメ

この記事はFOSS4G Advent Calendar 2017の23日目の投稿です。

みなさまこんにちは。

今年は、Pythonなどで空間処理を行うのにmod_spatialite使ってみたらいかがでしょうか、というオハナシです。
全然新しくない（むしろ古いか…）ものの紹介ですすみません。（検索したらホイホイ出てくるよね…）

はじめに

ローカル環境で利用できる軽量のRDBMSであるSQLiteの地理空間拡張がSpatiaLiteです。

名だたるPostGISと同じような処理がお手軽に利用できますよ、というメリットがあるわけですが、SQLiteでは変数を使用したSQLを認識できない（他のRDBMSはどうだかわかりません…）ため、バッチ処理などのために変数を使用してSQLを生成するには外部言語からSpatiaLiteへアクセスしたいということになります。
（ちなみに、次期バージョンのSpatiaLite4.5では変数を利用できるようになるよというアナウンスがあります。ただ、例えばデータに応じてJOINの数を変化させるなどの構造自体が変化するクエリは対応できないと思われます。）

というわけで、そんな要望に応えるのがこの記事で紹介する「mod_spatialite」というわけです。

いきなり脱線：pyspatialite？

pythonからSpatiaLiteの機能を利用できるモジュールとして、pyspatialiteがあります。

QGISをインストールすると、例えばPythonコンソールやWindowsユーザーならOSGeo4W-shellからpythonを起動するなどして、import pyspatialiteとすれば、pyspatialiteモジュールが読み込めます。

それなら、このpyspatialiteを使えばいいのでは？　…と思いきや、利用できない空間関数がいろいろあったりします。

しかもこの事実がこれまでイマイチQGIS開発コミュニティと共有されていなかったらしく、つい先日、SpatiaLiteユーザーのMLに

QGIS3に移行するのにpyspatialiteのメンテナンスやってほしいんだけど、誰かいないかな？

てな書き込みがあったのですが、その反応は

え、まだそんなん使ってるんかい。
そんなんもう古いからmod_spatialite使いなはれ（超意訳）

というものでした^^;
cf.: pyspatialite needs a new maintainer 2017/09/29

（余談ですが、このMLではancient（太古の）、outdated/obsolete（廃れた）、cold deadなどの語句がよく登場します。要は投稿者の使っているバージョンが古いから新しいの使ったらいいよという回答につながっていくパターンです。）

mod_spatialite？

mod_spatialiteは、SQLiteから読み込む拡張機能の一つです。
上で触れたpyspatialiteがPythonのモジュールであるのに対して、mod_spatialiteはSQLiteから拡張機能を読み込みます。そのため、SQLiteデータベースへの接続があれば、Pythonに限らず任意の言語から利用できるということになります。
Pythonの場合sqlite3は標準モジュールなので、あとはmod_spatialiteを追加するだけで（gdal/ogrなど他のFOSS4Gがなくても）空間関数が利用できるようになります。

それでは、mod_spatialiteの入手と接続をやってみます。
（以下、OSGeo4Wの利用環境での例です。）

入手

公式サイト（The Gaia-SINS federated project home-page）から「MS Windows binaries」の「current stable version」にある32bitまたは64bitのリンクを開きます。

表示されるファイルリストから、「mod_spatialite-なんちゃら」をダウンロードします。（7zで圧縮されてるのでなんとかして展開してください。）

展開したら「mod_spatialite.dll」が入っていると思います。これが本体。
とりあえずここでは、
C:\hoge\mod_spatialite
というディレクトリ下にmod_spatialite.dllとその他のファイルを置きます。

Pythonで読み込んでみる

OSGeo4W-shellからpythonと打ち込んで、Pythonを起動します。
その後、以下のコマンドを実行します。

import os  # osモジュールの読み込み
import sqlite3  # sqlite3モジュールの読み込み
path_mod_spatialite = r'C:\hoge\mod_spatialite'  # mod_spatialite.dllの入っているフォルダ
os.environ['PATH'] = '{};{}'.format(path_mod_spatialite, os.environ['PATH'])  # 環境変数に追加
con = sqlite3.connect(':memory:')  # メモリ上に仮想データベースを生成
con.enable_load_extension(True)  # SQLite拡張機能の読み込みを有効にする（デフォルトはFalse）
con.execute("SELECT load_extension('mod_spatialite');")  # SQLiteデータベースに拡張機能を読み込み
print(con.execute("SELECT spatialite_version();").fetchone()[0])  # SpatiaLiteのバージョンを表示

SpatiaLiteのバージョンが表示されれば正解です。

上のコードでは、3-4行目で環境変数を追加した上で、それ以下の行でデータベースへの接続と、mod_spatialiteの読み込みを行っています。
ポイントは6行目で、sqlite3の拡張機能を読み込む設定はデフォルトでは無効になっているため、あらかじめこれを有効にした上で拡張機能を有効にする必要があります。
また、新たなデータベースへの接続を行う際にはその都度この設定を行うことになるので、おまじない関数としてまとめておくと楽ちんです。

SpatiaLiteデータベースを生成する

SpatiaLiteデータベースファイルは、いくつかのメタテーブルやトリガーが設定されたSQLiteデータベースなのですが、このデータベースファイルを生成してみます。

既にmod_spatialiteへのパスが通っているものとして、以下を実行してみます。

path_db = r'C:\hoge\test.sqlite'  # 作成するデータベースファイル
open(path_db, 'w').close()  # データベースファイルを生成
con = sqlite3.connect(path_db)  # データベースファイルへの接続オブジェクトを生成
con.enable_load_extension(True)  # SQLite拡張機能の読み込みを有効にする（デフォルトはFalse）
con.execute("SELECT load_extension('mod_spatialite');")  # SQLiteデータベースに拡張機能を読み込み
con.execute("SELECT InitSpatialMetaData(1);")  # メタデータを生成

すると、新たに生成したtest.sqliteには、内部に必要なメタデータなどが格納され、サイズが5,440KBとなります。
例えば、ここまで実行した上でSpatiaLiteへshpファイルをインポートするImportSHPを実行すれば、shpファイルのデータをSpatiaLiteに変換して格納することができます。

shpファイルを読み込んでみる

国土数値情報から、平成29年度版の行政界（北海道）のデータを入手して、C:\hoge\shps\へshpファイル群を保存します。
そして、以下を実行してみます。（ImportSHPでは読み込むファイル名の拡張子を除外しなければなりません。）

con.execute("SELECT ImportSHP('C:\hoge\shps\N03-17_01_170101', 'gyouseikai', 'CP932', 4612);")

たぶん「no such function: ImportSHP」と怒られると思います。
これは、SpatiaLiteがデフォルトでは外部データの入出力にセキュリティの観点から制限をかけているため、関数が利用できなくなっていることが原因です。

そこで、pythonをexit()で終了し、OSGeo4W-shellから環境変数（SPATIALITE_SECURITY）を追加します。

set SPATIALITE_SECURITY=relaxed

その上で再度pythonに入り、一連の処理を再度実行します。

import os  # osモジュールの読み込み
import sqlite3  # sqlite3モジュールの読み込み
path_mod_spatialite = r'C:\hoge\mod_spatialite'  # mod_spatialite.dllの入っているフォルダ
os.environ['PATH'] = '{};{}'.format(path_mod_spatialite, os.environ['PATH'])  # 環境変数に追加

path_db = r'C:\hoge\test.sqlite'  # 作成するデータベースファイル
open(path_db, 'w').close()  # データベースファイルを生成
con = sqlite3.connect(path_db)  # データベースファイルへの接続オブジェクトを生成
con.enable_load_extension(True)  # SQLite拡張機能の読み込みを有効にする（デフォルトはFalse）
con.execute("SELECT load_extension('mod_spatialite');")  # SQLiteデータベースに拡張機能を読み込み
con.execute("SELECT InitSpatialMetaData(1);")  # メタデータを生成

con.execute("SELECT ImportSHP('C:\hoge\shps\N03-17_01_170101', 'gyouseikai', 'CP932', 4612);")

con.close()  # やることが終わったら明示的に接続オブジェクトは切断しておくべし。

「Inserted 9606 rows into 'gyouseikai' from SHAPEFILE」と表示され、データのインポートができました。
しかし、、先人の言葉のとおり、SpatialiteのShapeファイルへの執着はただごとではありません。

おわりに

うーん、いまいちメリットが伝わらないかもしれませんね…汗（ごめんなさい）

• ディレクトリ内のshpを全部まとめてインポートして統合する　とか
• 繰り返しばかりの吐き気を催すクエリを生成する　とか
• ユーザー定義関数的なものをpythonで定義して利用する　とか

そんな使い方ができます。

そして、オンメモリで仮想データベースを作成する:memory:を利用すれば「WKTで与えたジオメトリをもとに空間処理した結果をWKTで取り出す」みたいなこともできます。

とりあえずこんなところでした。
ではみなさま、よいクリスマスとよいお年を。