Creating a minimal SpatiaLite database with Python

When writing a test for datasette-leaflet-freedraw I realized I didn't have a simple tiny recipe for creating an in-memory SpatiaLite database in Python. I came up with this:

import sqlite3
SPATIALITE = "/usr/local/lib/mod_spatialite.dylib"
db = sqlite3.connect(":memory:")
db.enable_load_extension(True)
db.execute("SELECT load_extension(?)", [SPATIALITE])
db.execute("SELECT InitSpatialMetadata(1)")
db.execute("CREATE TABLE places_spatialite (id integer primary key, name text)")
db.execute(
    "SELECT AddGeometryColumn('places_spatialite', 'geometry', 4326, 'POINT', 'XY');"
)
# Then to add a spatial index:
db.execute(
    "SELECT CreateSpatialIndex('places_spatialite', 'geometry');"
)

Datasette and sqlite-utils both have find_spatialite() utility functions. Here's how to call the Datasette one as a one-liner:

% python -c 'import datasette.utils; print(datasette.utils.find_spatialite())'
/usr/local/lib/mod_spatialite.dylib

I also remembered I have this script: build_small_spatialite_db.py

Related

• sqlite Saving an in-memory SQLite database to a file in Python - 2023-04-08
• spatialite Viewing GeoPackage data with SpatiaLite and Datasette - 2022-12-11
• spatialite KNN queries with SpatiaLite - 2021-05-16
• gis Natural Earth in SpatiaLite and Datasette - 2022-03-04
• sqlite Geospatial SQL queries in SQLite using TG, sqlite-tg and datasette-sqlite-tg - 2023-09-25
• sqlite Loading SQLite extensions in Python on macOS - 2023-01-07
• sqlite Using LD_PRELOAD to run any version of SQLite with Python - 2020-06-17
• python Using the sqlite3 Python module in Pyodide - Python WebAssembly - 2021-10-18
• sqlite Geopoly in SQLite - 2023-01-04
• overture-maps Exploring the Overture Maps places data using DuckDB, sqlite-utils and Datasette - 2023-07-27

Created 2021-12-17T15:57:29-08:00 · Edit