Create SpatiaLite in Python

About this article

Notes on creating SpatiaLite in Python

Execution environment

OS Windows7-64bit Python 2.7.10

DLL download

[Here] Download the Windows version binary from [link-1] [link-1]:https://www.gaia-gis.it/spatialite-2.3.1/binaries.html

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

DLL copy

When you unzip it, there is a DLL in each bin folder, so copy it to a folder that passes the path

• libspatialite-1.dll
• libiconv2.dll
• libproj-0.dll
• It seems that geos * .dll is not necessary if you just create a DB.

script

A simple script to create SpatiaLite containing POINT, LINESTRING, POLYGON The created sample.spatialite can be referenced with GIS tools such as QGIS

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()

Other

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

• Spatialite sql list https://github.com/azavea/acs-alchemist/blob/master/csharp/Azavea.NijPredictivePolicing.Common/init_spatialite.sql

• Geometry is XY type only Spatialite (2.3.1) doesn't seem to support XYZ If defined, the following error will occur AddGeometryColumn() error: argument 5 [dimension] current version only accepts dimension=2