試用 SpatiaLite（一）：安裝及測試

根據維基百科的介紹：
SpatiaLite is a spatial extension to SQLite, providing vector geodatabase functionality. It is similar to PostGIS, Oracle Spatial, and SQL Server with spatial extensions, although SQLite/SpatiaLite aren't based on client-server architecture: they adopt a simpler personal architecture. i.e. the whole SQL engine is directly embedded within the application itself: a complete database simply is an ordinary file which can be freely copied (or even deleted) and transferred from one computer/OS to a different one without any special precaution.

SpatiaLite extends SQLite's existing spatial support to cover the OGC's SFS specification. It isn't necessary to use SpatiaLite to manage spatial data in SQLite, which has its own implementation of R-tree indexes and geometry types, but in order to do advanced spatial queries and support multiple map projections, SpatiaLite is needed.

亦根據其網頁介紹：
SpatiaLite is an open source library intended to extend the SQLite core to support fully fledged Spatial SQL capabilities.
SQLite is intrinsically simple and lightweight:

- a single lightweight library implementing the full SQL engine
- standard SQL implementation: almost complete SQL-92
- no complex client/server architecture
- a whole database simply corresponds to a single monolithic file (no size limits)
- any DB-file can be safely exchanged across different platforms, because the internal architecture is universally portable
- no installation, no configuration

SpatiaLite is smoothly integrated into SQLite to provide a complete and powerful Spatial DBMS (mostly OGC-SFS compliant).
Using SQLite + SpatiaLite you can effectively deploy an alternative open source Spatial DBMS roughly equivalent to PostgreSQL + PostGIS.

安裝 SpatiaLite 前，需先安裝 SQLite 3、PROJ.4、GEOS、FreeXL。先下載它們的原始碼，解壓後在各自的資料夾執行以下指令，大致就可以了：

./configure
make
sudo make install
sudo ldconfig

然後再下載最新的 libspatialite 原始碼，解壓後在資料夾執行以下指令：

./configure --enable-geocallbacks --enable-lwgeom
make
sudo make install
sudo ldconfig

--enable-geocallbacks --enable-lwgeom 是為了使用 R* index 和 lwgeom 的一些功能。（--enable-lwgeom 只限於安裝 PostgreSQL 和 PostGIS 後才能提供，相關資料）

安裝後執行 sqlite，輸入以下指令便能初始化 Spatial 的資料。：

sqlite> .load libspatialite.so
sqlite> SELECT InitSpatialMetaData();

除了 sqlite，網站上亦提供了工具，例如 spatialite CLI tools，可供直接使用，而不用手動載入 libspatialite.so 和初始化 Spatial 的資料。

以下指令可供查看安裝的版本，除了 GeosAdvanced 外，其他功能亦已開啟，可用的函數可在此頁查閱：

spatialite> select spatialite_version();
4.0.0
spatialite> select proj4_version();
Rel. 4.8.0, 6 March 2012
spatialite> select geos_version();
3.3.6-CAPI-1.7.6
spatialite> select lwgeom_version();
2.0.2
spatialite> select HasIconv();
1
spatialite> select HasMathSQL();
1
spatialite> select HasGeoCallbacks();
1
spatialite> select HasProj();
1
spatialite> select HasGeos();
1
spatialite> select HasGeosAdvanced();
1
spatialite> select HasGeosTrunk();
0
spatialite> select HasLwGeom();
1
spatialite> select HasEpsg();
1
spatialite> select HasFreeXL();
1

例子：
輸入由 WGS84 為基準的經緯度（4326 是 WGS84 2D 的 EPSG CRS SRID 編號）：

spatialite> select AsText(MakePoint(114.1689,22.4518,4326));
POINT(114.1689 22.4518)

將它轉換為 WGS84 UTM 50N 格網座標（32650 是 WGS84 / UTM zone 50N 的 EPSG CRS SRID 編號）：

spatialite> select AsText(ST_Transform(MakePoint(114.1689,22.4518,4326),32650));
POINT(208621.605201 2485587.067636)

將它轉換為 HK1980 格網座標（2326 是 Hong Kong 1980 Grid System 的 EPSG CRS SRID 編號）：

spatialite> select AsText(ST_Transform(MakePoint(114.1689,22.4518,4326),2326));
POINT(835447.180293 834705.40192)

利用格網座標計算大埔中心至九龍坑山的距離：

spatialite> select ST_Distance(MakePoint(208838.738969, 2488246.942923), MakePoint(208621.605201, 2485587.067636));
2668.72321824495

利用經緯度計算大埔中心至九龍坑山的距離：

spatialite> select ST_Length(MakeLine(MakePoint(114.17052, 22.475837,4326), MakePoint(114.1689,22.4518,4326)), 1);
2666.99235712016

計算大埔中心至九龍坑山的方位角：

spatialite> select Degrees(ST_Azimuth(MakePoint(114.1689,22.4518,4326), MakePointZ(114.17052, 22.475837, 437.639187, 4326)));
3.85568120514838

輸出九龍坑山三角網測站的 KML：

spatialite> select AsKml("Cloudy Hill", "description", MakePointZ( 835614.056, 837367.172, 440.8, 2326));
<Placemark><name>Cloudy Hill</name><description>description</description><Point><coordinates>114.170519962613,22.47583709798935,437.6391865937039</coordinates></Point></Placemark>

點的 Union：

spatialite> select astext(ST_Union(MakePoint(114.17052, 22.475837,4326), MakePoint(114.1689,22.4518,4326)));
MULTIPOINT(114.17052 22.475837, 114.1689 22.4518)

創建有地理數據的表，先是一般格式的欄：

spatialite> CREATE TABLE TestTable(
id INTEGER PRIMARY KEY AUTOINCREMENT,
Name TEXT NOT NULL);

創建該地理數據欄，儲存以 WGS84 為基準的點：

spatialite> SELECT AddGeometryColumn('TestTable', 'Geometry', 4326, 'POINT', 'XY');
1

加入 R* index，以加快檢索：

spatialite> SELECT CreateSpatialIndex('TestTable', 'Geometry');
1

加入點數據：

spatialite> insert into TestTable (Name, Geometry) VALUES ("a", MakePoint(114.1689,22.4518,4326));
spatialite> insert into TestTable (Name, Geometry) VALUES ("b", MakePoint(114.17052,22.475837,4326));

列出數據（Geometry 未能直接顯示）：

spatialite> select * from TestTable;
1|a|
2|b|

列出數據：

spatialite> select id, Name, AsText(Geometry) from TestTable;
1|a|POINT(114.1689 22.4518)
2|b|POINT(114.17052 22.475837)

利用 R* index 查出在 22.4518N 114.1689E, 22.4520N 114.1690E 內的點：

spatialite> SELECT  id, Name, AsText(Geometry) FROM TestTable WHERE ROWID IN (SELECT pkid FROM idx_TestTable_Geometry WHERE pkid MATCH RTreeIntersects(114.1689,22.4518,114.1690,22.4520));
1|a|POINT(114.1689 22.4518)

將點數據轉成 GeoHash（可以文字方式儲存於一般資料庫，以方便查詢某一點附近的其他點）：

spatialite> SELECT Name, GeoHash(Geometry) FROM TestTable;
a|wecptzpr0ny5c1eeemw3
b|wecpy587jztypffhy099

參考資料：
The Gaia-SINS federated projects home-page
SpatiaLite
spatialite-tools
SpatiaLite 4.0.0 SQL functions reference list （可查閱各樣函數的資料）
using SpatiaLite （Spatialite 教學）
EPSG SRID 資料庫
另一 EPSG SRID 資料庫
另一 EPSG SRID 資料庫
另一 EPSG SRID 資料庫
有地球座標系統、地圖投射等的教學資料
Wikipedia - GeoHash
Geohash.org
Javascript module for encoding and decoding geohashes - Demo