Loading spatialite tables into geopandas

loading spatialite data into GeoDataFrames.md

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.

the_pyspatialite_way.py

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

the_pysqlite2_way.py

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

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