PythonでSpatiaLiteを作成

この記事について

PythonでSpatialiteを作成した時のメモ

実行環境

OS Windows7-64bit
Python 2.7.10

DLLダウンロード

ここからWindows版バイナリをダウンロード

• libspatialite-win-x86-2.3.1.zip
• libiconv-win-x86-1.9.2.zip
• proj-win-x86-4.6.1.zip

DLLコピー

zip展開すると各binフォルダ内にDLLがあるのでパスが通ったフォルダへコピー
* libspatialite-1.dll
* libiconv2.dll
* libproj-0.dll
※DBの作成だけであればgeos*.dllは必要ないようです

スクリプト

POINT,LINESTRING,POLYGONを格納したSpatialiteを作成する簡単なスクリプト
作成されたsample.spatialiteはQGISなどのGISツールで参照出来ます

sample.py

# -*- coding: utf-8 -*- 
import sys
import os
import sqlite3

if __name__ == "__main__":
  conn = sqlite3.connect("sample.spatialite")
  if conn:
    print 'connect success'
  else:
    print 'connect failes'
    sys.exit()
  conn.enable_load_extension(True)
  conn.execute('SELECT load_extension("libspatialite-1.dll")')
  conn.execute('SELECT InitSpatialMetaData()')
  conn.execute("INSERT INTO spatial_ref_sys (srid, auth_name, auth_srid, ref_sys_name, proj4text) VALUES (4326, 'epsg', 4326, 'WGS 84', '+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs')")

  #POINT
  conn.execute('CREATE TABLE "point" ("OBJECTID" INTEGER PRIMARY KEY AUTOINCREMENT)')
  conn.execute('Select AddGeometryColumn ("point", "Geometry", 4326, "POINT", 2)')
  conn.execute('INSERT INTO point (Geometry) VALUES(GeomFromText("POINT(139.69 35.679)",4326))')

  #LINESTRING
  conn.execute('CREATE TABLE "line" ("OBJECTID" INTEGER PRIMARY KEY AUTOINCREMENT)')
  conn.execute('Select AddGeometryColumn ("line", "Geometry", 4326, "LINESTRING", 2)')
  conn.execute('INSERT INTO line (Geometry) VALUES(GeomFromText("LINESTRING(139.69 35.68, 139.691 35.681, 139.692 35.68)",4326))')

  #POLYGON
  conn.execute('CREATE TABLE "polygon" ("OBJECTID" INTEGER PRIMARY KEY AUTOINCREMENT)')
  conn.execute('Select AddGeometryColumn ("polygon", "Geometry", 4326, "POLYGON", 2)')
  #Exterior CCW - Interior:CW
  conn.execute('INSERT INTO polygon (Geometry) VALUES(GeomFromText("POLYGON((139.69 35.682, 139.69 35.681, 139.691 35.681, 139.691 35.682, 139.69 35.682),(139.6902 35.6818, 139.6908 35.6818, 139.6908 35.6812, 139.6902 35.6812, 139.6902 35.6818))",4326))')

  conn.commit()
  conn.close()

その他

• OGC:Simple Feature Access - Part 1: Common Architecture
  http://www.opengeospatial.org/standards/sfa

• spatialite sql一覧
  https://github.com/azavea/acs-alchemist/blob/master/csharp/Azavea.NijPredictivePolicing.Common/init_spatialite.sql

• ジオメトリはXYタイプのみ
  Spatialite(2.3.1)はXYZに対応していないようです
  定義すると下記のエラーになります
  AddGeometryColumn() error: argument 5 [dimension] current version only accepts dimension=2