SpatiaLite は地理空間関数を追加した SQLite データベースエンジンです。
SQLite is a Database Management System (DBMS) which is simple, robust, easy to use and very lightweight. Each SQLite database is simply a file. You can freely copy it, compress it, and port it between Windows, Linux, MacOs etc.
このクイックスタートではコマンドラインと GUI アプリケーションからのデータベースの開き方を記載します。Some sample SQL queries are shown.
Spatialite-gui provides a visual interface for viewing and maintaining a spatialite database. You can easily see the structure of the tables and data contents using point and click functions, many of which construct common SQL queries, or craft your own SQL queries.
Let’s start by viewing a spatialite database, and looking at columns within a table:
File->Connecting an existing SQLite DB メニューを選択してください。
/home/user/data/spatialite を開き、 trento.sqlite を選択してください。
MunicipalHallsView テーブルで右クリックし、”Show Columns” を選択してください。
You will notice the display broken into 3 areas:
MunicipalHalls で右クリックし、”Edit table rows” を選択してください。Notice the SQL query which has been created for you in the top right pane, and results in the bottom right.:
SELECT ROWID, "PK_UID", "AREA", "PERIMETER", "COMU", "Geometry"
FROM "MunicipalHalls"
ORDER BY ROWID
Now lets try tweaking this SQL statement to get NOME and (Lat,Long) for only the NOME_PROV fields include “BRESCIA”, this time using the MunicipalHallsView. In the upper right SQL pane type:
SELECT NOME, X(Geometry) AS Longitude, Y(Geometry) AS Latitude
FROM "MunicipalHallsView"
WHERE NOME_PROV LIKE "BRESCIA";
右側の “Execute SQL” ボタンをクリックし、and see the results in the bottom right pane.
Users needing to script or automate queries will learn the advantages of working with a spatialite database from the command line interface. In this example, we will load a shapefile, and search for schools which are near highway 42.
Before working from the command line, we need to open a terminal window: LXDE Menu -> Accessories -> LXTerminal.
コンソールを開き、spatialite でサンプルデータベースを開きます。
spatialite /home/user/data/spatialite/trento.sqlite
CLI インターフェースで役立つ以下のようなコマンドがあります:
.help
.tables
.quit
Creating a new spatialite database and loading a shapefile
Let’s create a new, empty spatialite database, and load two shapefiles from the north_carolina dataset:
user@osgeo-6:~$ spatialite test.sqlite SpatiaLite version ..: 3.1.0-RC2 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 - R*Tree] - 'MbrCache' [Spatial Index - MBR cache] - 'VirtualSpatialIndex' [R*Tree metahandler] - 'VirtualFDO' [FDO-OGR interoperability] - 'SpatiaLite' [Spatial SQL - OGC] PROJ.4 version ......: Rel. 4.8.0, 6 March 2012 GEOS version ........: 3.3.3-CAPI-1.7.4 SQLite version ......: 3.7.9 Enter ".help" for instructions spatialite> spatialite> .loadshp data/north_carolina/shape/schools_wake schools utf-8 3358 spatialite> .loadshp data/north_carolina/shape/roadsmajor roads utf-8 3358Note the format of the .loadshp command: first the shapefile without the .shp extension, then the name of the new spatialite table, next the character encoding, and finally the EPSG code of the shapefile’s CRS.
Now we’ll query for schools near to highway 42.:
spatialite> SELECT s.NAMESHORT, s.ADDRNUMBER, s.ADDRROOT ...> FROM schools AS s, roads AS r ...> WHERE r.ROAD_NAME = "NC-42" AND ...> ST_Distance(s.Geometry, r.Geometry) < 1000; FUQUAY-VARINA|6600|Johnson Pond Rd WILLOW SPRINGS|6800|Dwight Rowland Rd FUQUAY-VARINA|109|N Ennis St LINCOLN HEIGHTS|307|Bridge StFinally, we output the query to a “comma separated values” text file “schools_rt42.txt” with the following commands:
spatialite> .mode csv spatialite> .output "schools_rt42.txt" spatialite> SELECT s.NAMESHORT, s.ADDRNUMBER, s.ADDRROOT ...> FROM schools AS s, roads AS r ...> WHERE r.ROAD_NAME = "NC-42" AND ...> ST_Distance(s.Geometry, r.Geometry) < 1000; spatialite>.q
次のステップとして以下のようなことをやってみるとよいでしょう。
SpatiaLite についてより詳しく学ぶには SpatiaLite project page を参照してください。
また、チュートリアル Spatialite cookbook が公開されています。