SpatiaLiteの導入

今回は、空間データを蓄積・管理できるデータベース”SpatiaLite”を導入し、シェープファイルをインポートなどの基本的な機能を確認します。

SpatiaLiteとは

SQLiteは、OracleとOracleSpatial、PostgerSQLとPostGISの関係と同じく、SQLiteの空間拡張ライブラリです。図にすると、以下のようになります。

PostGISとSpatiaLiteの空間拡張機能は、GEOS（幾何計算ライブラリ）やProj4（投影変換ライブラリ）を組み込んで実現しています。
一方でオラクルの場合は、これらをPL/SQLにて独自実装しています。

SpatiaLiteはOracleやPostGISに比べると機能的に貧弱な部分があることやSpatiaLiteのベースであるSQLiteの環境回りの理解が必要であることから、少し慣れが必要です。

機能の詳細は、以下が詳しいです。
http://adhoc.osgeo.osuosl.org/livedvd/docs/ja/overview/spatialite_overview.html

Spatialiteの最新の安定版は4.3.0a（2015.09.07）で、現在開発は停止中と思われます。

インストール

それでは、Windows PC（Windows10 Home 32bit、バージョン1903）にSpatiaLiteをインストールします。
インストール作業は、物件をダウンロードしてパスを設定するだけです。

ファイルのダウンロード

下記URLから物件をダウンロードします。
http://www.gaia-gis.it/gaia-sins/index.html

ダウンロードサイトは、以下のようになっており、”current stable version”から目的のビット数を選択します。

選択後、以下のように一覧が表示されますので、必要なファイルをダウンロードします。

必要なファイルは以下の３つです。

• mod_spatialite-4.3.0a-win-x86.7z
• spatialite-4.3.0a-win-x86.7z
• spatialite_gui-4.3.0a-win-x86.7z

今回の記事では、”mod_spatialite-4.3.0a-win-x86.7z”は使用しませんが、Pythonからの接続時には必要ですので導入します。
また、理由は後述しますが、64ビット環境であっても32ビット版”mod_spatialite-4.3.0a-win-x86.7z”の導入をお勧めします。
7-Zip未導入の方は、こちらから入手してください。

ファイルの解凍とパスの設定

ダウンロードしたファイルを任意のディレクトリに解凍します。
mod_spatialiteとその他の物件（.exe）は、ディレクトリを分けた方がよいでしょう。以下に例を示します。

D:\tool\spatialite>tree /f
D:.
│ spatialite.exe
│ spatialite_gui.exe
│
└─mod_spatialite
　　　mod_spatialite.dll
　　　sqlite3.exe
　　　zlib1.dll
　　　libfreexl-1.dll
　　　libgcc_s_dw2-1.dll
　　　libgeos_c-1.dll
　　　libgeos-3-5-0.dll
　　　libiconv-2.dll
　　　liblzma-5.dll
　　　libproj-9.dll
　　　libsqlite3-0.dll
　　　libstdc++-6.dll
　　　libxml2-2.dll

上記2か所（D:\tool\spatialite、D:\tool\spatialite\mod_spatialite）のパスを、コントロールパネルから環境変数PATHに設定すれば、インストールは完了です。

SpatiaLiteの利用形態

SpatiaLiteの利用形態には以下の３つがあります。

1. SpatiaLiteのサイトから頒布されているバイナリの利用
2. 最新版の”sqlite3.exe”＋”mod_spatialite”の利用
3. QGISに同梱されているバイナリを利用

1.の方法は、SQLiteのかなり旧いバージョン（SQLite 3.8.11.1 2015-07-29）を使うことになりますので、バージョンアップによる追加機能やバグフィックスの恩恵が得られません。一方で、SpatiaLiteとしての動作は確認済みですし、後述の”spatialite.exe”はこの方法でしか使用できません。

2.の方法は、SQLiteのバージョンアップによる追加機能やバグフィックスの恩恵が得られますが、バージョンアップの影響でSpatiaLiteの機能が動作しないリスクがあります。

3.の方法は、QGISを導入されている方限定です。公式サイトから頒布されているバージョンよりも新しいSQLite（SQLite version 3.29.0 2019-07-10）をベースにmod_spatialiteもリビルドされているようです。

２つのCLI

SpatiaLiteには、下記２つのCLIがあります。

• sqlite3.exe
• spatialite.exe

私は、両者の違いを理解するのに時間がかかりました。
まず、”sqlite3.exe”からSpatiaLiteを使用する場合は、以下のSQLを発行しSpatiaLiteの初期化を行う必要があります。

sqlite>SELECT load_extension(‘mod_spatialite’);
sqlite>SELECT InitSpatialMetaData(1); — このSQLはデータベース新規作成時のみ必要

1行目のSQLは、SpatiaLiteモジュール（実体はGEOS等のdll群）の読み込みが行われます。
2行目のSQLにてSpatiaLiteの動作に必要なテーブルやビューなどが作成されます。このSQL発行後、データベースファイルのサイズは5.6MB程度となります。

一方の”spatialite.exe”は、SQLiteにSpatiaLiteの機能があらかじめ組み込まれている実行モジュールと考えるとよいでしょう。
”spatialite.exe”は、前述の初期化SQLを発行することなくSpatiaLiteの機能が使用できます。それに加えて、”.”から始まるSpatiaLite専用のコマンドも使用できます。
以下に”.help”の一覧を示しますが、かなり便利なコマンドもあります。

本記事では、”spatialite.exe”を使って検証作業を進めます。

CLIを使った動作確認

SpatiaLiteのコマンドラインインタフェース（CLI）を使って、点ジオメトリを持つテーブルの定義とレコードの挿入、空間検索までを実施してみましょう。基本的には、こちらのCook Bookを参考にしています。

CLIの起動

コマンドプロンプトからspatialite.exeを起動します。引数として保存するファイル名を指定します。（未指定の場合は、メモリー上での操作となります。）
以下に起動時の画面イメージを示します。

D:\Work\20200430>spatialite test.sqlite
SpatiaLite version ..: 4.3.0a Supported Extensions:
– ‘VirtualShape’ [direct Shapefile access]
– ‘VirtualDbf’ [direct DBF access]
– ‘VirtualXL’ [direct XLS access]
– ‘VirtualText’ [direct CSV/TXT access]
– ‘VirtualNetwork’ [Dijkstra shortest path]
– ‘RTree’ [Spatial Index – RTree] – ‘MbrCache’ [Spatial Index – MBR cache] – ‘VirtualSpatialIndex’ [RTree metahandler]
– ‘VirtualElementary’ [ElemGeoms metahandler]
– ‘VirtualXPath’ [XML Path Language – XPath]
– ‘VirtualFDO’ [FDO-OGR interoperability]
– ‘VirtualGPKG’ [OGC GeoPackage interoperability]
– ‘VirtualBBox’ [BoundingBox tables]
– ‘SpatiaLite’ [Spatial SQL – OGC]
PROJ.4 version ……: Rel. 4.9.1, 04 March 2015
GEOS version ……..: 3.5.0-CAPI-1.9.0 r4084
LWGEOM version ……: 2.1.7
TARGET CPU ……….: mingw32
the SPATIAL_REF_SYS table already contains some row(s)
SQLite version ……: 3.8.11.1
Enter “.help” for instructions
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
spatialite>

SpatiaLiteのバージョンや関連ライブラリのバージョンが表示され、SQLを投入可能なプロンプトが表示されます。

CLIにはSQL以外の特殊コマンドがあります。オラクルでは”SET”や”DESC”、PostgreSQLでは”\d”や”\t”などです。
SpatiaLiteの特殊コマンドは、”.”（ドット）から始まります。例えば、テーブル一覧を表示する場合は”.tables”、CLIを終了したい場合は、”.exit”と入力します。

spatialite> .tables
ElementaryGeometries vector_layers
SpatialIndex vector_layers_auth
geom_cols_ref_sys vector_layers_field_infos
geometry_columns vector_layers_statistics
geometry_columns_auth views_geometry_columns
geometry_columns_field_infos views_geometry_columns_auth
geometry_columns_statistics views_geometry_columns_field_infos
geometry_columns_time views_geometry_columns_statistics
spatial_ref_sys virts_geometry_columns
spatial_ref_sys_all virts_geometry_columns_auth
spatial_ref_sys_aux virts_geometry_columns_field_infos
spatialite_history virts_geometry_columns_statistics
sql_statements_log
spatialite> .exit
D:\Work\20200430>

SpatiaLiteのCLIを起動するとデータベースファイルが作成されますが、空間拡張に必要な26種類のテーブルやビューが同時に作成されます。

テーブルの作成とジオメトリカラムの追加

では、SQLを発行してテーブルを作成しましょう。
1つのSQL文でテーブルを作成したいところですが、SpatiaLiteの場合は、GEOMETRYカラムなしのテーブルを作成てから、別のSQLでGEOMETRYカラムを追加する必要があります。

spatialite> create table test (id integer ,name text );
spatialite> select AddGeometryColumn (‘test’ ,’geometry’ , 4326 ,’POINT’ ,’XY’);
1
spatialite>

上記の例では、テーブル名”test”を作成し、SRID=4326（WGS84緯度経度）のカラム”geometry”を追加しています。

データの挿入

作成したテーブル”test”に、データを挿入しましょう。
ここでは、東京、札幌、大阪の3つの点データを挿入しています。
空間データは、PostGISと同様、”ST_”から始まる関数を使います。以下では、”ST_GeomFromText”関数を使って、WKT形式で座標を設定しています。

spatialite> select * from test;
spatialite> insert into test(id ,name ,geometry) values(1 ,’Tokyo’ ,ST_GeomFromText(‘POINT(139.74853 35.64091)’ ,4326));
spatialite> insert into test(id ,name ,geometry) values(2 ,’Sapporo’ ,ST_GeomFromText(‘POINT(141.35 43.067)’ ,4326));
spatialite> insert into test(id ,name ,geometry) values(3 ,’Osaka’ ,ST_GeomFromText(‘POINT(135.501 34.6912)’ ,4326));
spatialite> select * from test;
id|name|geometry
1|Tokyo|
2|Sapporo|
3|Osaka|

ジオメトリカラムの内容を表示したい場合は、単純にselectしただけでは表示されませんので、AsText関数でテキスト化して表示ます。

spatialite> select id,name,AsText(geometry) from test;
id|name|AsText(geometry)
1|Tokyo|POINT(139.74853 35.64091)
2|Sapporo|POINT(141.35 43.067)
3|Osaka|POINT(135.501 34.6912)

空間関数の利用

CLIの最後として、東京から札幌、大阪の直線距離を表示してみましょう。

spatialite> select id,name,ST_Distance(ST_Transform(geometry,3100), ST_Transform(ST_GeometryFromText(‘POINT(139.74853 35.64091)’,4326),3100))/1000 as dist from test;
id|name|dist
1|Tokyo|0.0
2|Sapporo|835.591879332877
3|Osaka|401.386365495631

上記SQLでは、ST_Transform関数にて緯度経度を平面座標系（UTM51、SRID=3100）に投影し、ST_Distance関数で距離を計算しています。

空間拡張系の関数は、概ねPostGISと互換性があるようです。詳細は、以下を確認してください。

[blogcard url=”http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.2.0.html”]

シェープファイルのインポート

次に、”spatialite_gui”を使ってシェープファイルをインポートしてみましょう。
”spatialite_gui”は、エクスプローラからファイル”spatialite_gui.exe”をダブルクリックするなどして起動します。起動直後の画面は以下となります。

次に、先ほど作成したデータベース”test.sqlite”へ接続します。接続は、メニュー[Files]、[Connecting an existing SQLite DB]を選択し、表示されるファイル選択ダイアログにてデータベースファイルを指定します。
なお、”spatialite_gui”は、すべての機能で日本語のファイルやディレクトリ名はＮＧですので注意してください。

接続したデータベースに、シェープファイルをインポートします。インポートは、メニュー[Files]、[Advanced]、[Load Shapefile]を選択し、表示されるファイル選択ダイアログからシェープファイルを指定します。

シェープファイルを指定すると、インポート条件を指定する以下の画面が表示されますので、SRIDや空間索引の作成の要否を指定して”OK”ボタンを押下します。

成功すると、以下のようなメッセージダイアログが表示されます。
今回は、以前の記事で使用したNatural Earthの世界地図をインポートしました。

インポートはこれで完了です。

”spatialite_gui”には便利な機能がありますが、代表的なものにジオメトリカラムのプレビュー表示機能があります。
以下のように、ジオメトリカラムの右クリックメニューから[Map Preview]を選択することで、”Map Preview”画面が表示されます。

”Map Preview”画面は、以下のようなイメージです。

QGISからSpatiaLite上のデータ表示

最後に、作成したSpatialiteデータベースのデータをQGISを使って表示しましょう。
表示は、QGISのブラウザ画面から”SpatiaLite”を右クリックし、”新しい接続”選択し、表示されるファイル選択ダイアログからSpatiaLiteデータベースファイルを選択します。

接続先SpatiaLiteのテーブルが表示されますので、ダブルクリックすると地図表示されます。以下は、ラベルを表示した例です。

SpatiaLite導入時の注意点

今回の記事を作成にあたり、主にCLIについていくつか問題点を確認しましたのでまとめます。

”オマケ”SQLite問題

私の環境で、sqlite3.exeを起動しSpatiaLiteモジュール読み込み時（”SELECT load_extension(‘mod_spatialite’);”の発行時）に下記エラーとなる現象が発生しました。

この原因は、sqlite3.exeとmpd_spatialite.dllのバージョンによる問題のようです。
具体的には、私の環境に導入していたGDALにもsqlite3.exeが同梱されており、GDAL側のsqlite3.exeが実行された場合にこの現象が発生していました。
対策としては、コマンドラインから”which sqlite3”を実行するなどして、他にSQLiteが存在しないか確認し、存在する場合はPATHの順番を入替えて目的外のsqlite3.exeが起動されないようにします。

SQLiteは、exeファイル1つで動作しサイズも1MB未満と大変小さく、ライセンス的にもオープン（著作権なし）であることから、他のプロダクトに同梱されやすくなっていることが、今回の問題の背景にあると思います。

64ビット版mod_spatialiteの問題

環境構築時に説明しましたが、mod_spatialiteは、64ビット環境であっても32ビット版の導入をお勧めします。64ビット版を導入しSQLiteの最新版から”SELECT load_extension(‘mod_spatialite’);”を発行するとエラーとなります。
この問題は、単純にSQLite最新版のCLI（”sqlite3.exe”）が32ビット版のみの提供となっていることが原因だと思います。ただ、OpenJUMPの”SpatiaLiteプラグイン”でも同様のエラーが発生するので、ちょっと混乱しています。

”sqlite3.exe”の日本語文字化け問題

SpatiaLite同梱の”sqlite3.exe”（SQLite 3.8.11.1 2015-07-29）では、日本語の文字化けが発生します。

この問題は、コマンドラインから文字コードをUTF-8に設定することで解決します。具体的には、コマンドラインから文字コードを設定（”chcp 65001”）し、sqlite3.exeを起動すると文字化けが解決します。
ただし、この方法は日本語入力（where句での日本語条件指定など）には対応できません。SQLを文字化けして認識するためCLIが正しく動作しなくなります。

この問題は、SpatiaLite同梱のSQLiteのバージョンではマルチバイト対応が不十分だったことが原因と思われます。
この問題は、以下のいずれかの方法で回避できます。

• ”sqlite3.exe”のバージョンを新しくする
• ”spatialite.exe”を使用しキャラクタセットを指定する

なお、”spatialite_gui.exe”ではこの問題は発生しません。

まとめ

ここまで、SpatiaLiteについて以下を確認しました。

• SpatiaLiteの導入方法
• CLIによるテーブルの作成、データ挿入、検索
• GUIによるシェープファイルのインポート
• QGISによる表示確認

SpatiaLiteは、データベースサーバーなどの大掛かりな仕組みを使わずに地図データを1ファイルに収容して持ち運べるというメリットがあると思います。
逆に、”軽い”、”フリー”といったメリットから、多くのプロダクトに同梱され”多バージョン同居問題”が発生することも確認できたと思います。

SpatiaLiteは、開発が止まっているものの製品としての完成度は高いことが実感できました。早速、実業務でも（ツールとして）使おうと思います。

今回はSpatiaLiteの導入について書きましたが、Dockerを使ったPostGISやオラクルの導入についても記事にしていますので、参考にしてください。

[blogcard url=”https://takamoto.biz/gis/qgis17/”]

[blogcard url=”https://takamoto.biz/other/docker/”]

[blogcard url=”https://takamoto.biz/gis/oraspatial1/”]