Skip to content

Instantly share code, notes, and snippets.

@perrygeo
Last active October 1, 2023 16:57
  • Star 7 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save perrygeo/868135514d2518257bbb to your computer and use it in GitHub Desktop.
Loading spatialite tables into geopandas

Loading spatialite tables into GeoPandas GeoDataFrames

This little trick derives from the fact that the from_postgis class method is not really specific to postgis at all; it will work with sqlalchmey or dbapi2 connections. However, there are some peculiarities with spatialite that prevent this from being as simple as one might hope. There are two options:

  • The pysqlite2 driver works great for vanilla sqlite3 databases but spatialite requires loading an extension. In order to load extensions, you need to install a patched version and do some manual loading of the shared library. (see the_pysqlite2_way.py)

  • A better alternative is to use pyspatialite but installation is also a bit funky. I had to install from the current git master instead of the pypi version. The extension is loaded automatically. (see the_pyspatialite_way.py)

Either way, you have to explicitly wrap your geometry column(s) in Hex(ST_AsBinary(...)) in order to run the geometries through shapely.wkb.loads which is used internally in from_postgis.

from __future__ import print_function
import geopandas as gpd
# Must used recent master (pypi version appears broken as of 8/18/2014)
# pip install "git+https://github.com/lokkju/pyspatialite.git@94a4522e58#pyspatialite"
from pyspatialite import dbapi2 as spatialite
if __name__ == "__main__":
dbpath = 'cities.sqlite'
geom_col = 'geom'
con = spatialite.connect(dbpath)
# SQL must wrap the geometry in hex(st_asbinary(...))
sql = "SELECT name, Hex(ST_AsBinary(GEOMETRY)) as geom FROM cities;"
df = gpd.GeoDataFrame.from_postgis(sql, con, geom_col=geom_col)
print(df.head())
from __future__ import print_function
import geopandas as gpd
# Must used patched version that allows extension loading
# pip install "git+https://github.com/Nextdoor/pysqlite.git@v2.6.0_patched#egg=pysqlite2"
from pysqlite2 import dbapi2 as sqlite
if __name__ == "__main__":
# $ locate libspatialite.so
shared_lib = '/usr/lib/x86_64-linux-gnu/libspatialite.so'
dbpath = 'cities.sqlite'
# Create connection and load spatialite extension
con = sqlite.connect(dbpath)
con.enable_load_extension(True)
con.execute("select load_extension('{0}');".format(shared_lib))
# SQL must wrap the geometry in hex(st_asbinary(...))
sql = "SELECT name, st, med_age, Hex(ST_AsBinary(GEOMETRY)) as geom FROM cities;"
df = gpd.GeoDataFrame.from_postgis(sql, con, geom_col="geom")
print(df.head())
@blu3r4d0n
Copy link

fwiw, the native sqlite3 library is able to handle loading extensions and it works just fine with spatialite! 😃

@snajme
Copy link

snajme commented Oct 1, 2023

thanks for sharing. I wish they would maintain the pyspatialite not sure why they did not.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment