Is it possible to insert a Geopandas GeoDataFrame
directly into a Spatialite
database with Python so that the geometry
column of the GeoDataFrame
becomes a proper geometry table in the database which can be used for spatial queries?
I tried to do it the following way. The shapefile I used for creating an example GeoDataFrame
can be downloaded from granvillegis.org (AddressPoints.zip) but any other shapefile should also be fine.
# import geopandas
import geopandas as gpd
# import sqlite/spatialite
from sqlalchemy import create_engine, event
from sqlite3 import dbapi2 as sqlite
# read shapefile into GeoDataFrame
print('reading shapefile')
gdf = gpd.GeoDataFrame.from_file('TM_WORLD_BORDERS_SIMPL-0.3.shp')
# create database engine with new sqlite database
engine = create_engine('sqlite:///TestDB.sqlite', module=sqlite)
# load spatialite extension for sqlite
@event.listens_for(engine, 'connect')
def connect(dbapi_connection, connection_rec):
dbapi_connection.enable_load_extension(True)
dbapi_connection.execute('SELECT load_extension("mod_spatialite.dll")')
# create spatialite metadata
print('creating spatial metadata...')
engine.execute("SELECT InitSpatialMetaData(1);")
# write geodataframe into spatialite database
print('writing into database...')
gdf.to_sql('WorldBorders', engine, if_exists='replace', index=False)
This gives:
Traceback (most recent call last):
File "C:\Python34\lib\site-packages\sqlalchemy-1.0.0b4-py3.4.egg\sqlalchemy\engine\base.py", line 1116, in _execute_context
File "C:\Python34\lib\site-packages\sqlalchemy-1.0.0b4-py3.4.egg\sqlalchemy\engine\default.py", line 439, in do_executemany
sqlite3.InterfaceError: Error binding parameter 2 - probably unsupported type.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "C:/xampp/htdocs/3rdPartyAnalysis-ProofOfConcept/test.py", line 27, in <module>
gdf.to_sql('WorldBorders', engine, if_exists='replace', index=False)
File "C:\Python34\lib\site-packages\pandas\core\generic.py", line 966, in to_sql
dtype=dtype)
File "C:\Python34\lib\site-packages\pandas\io\sql.py", line 538, in to_sql
chunksize=chunksize, dtype=dtype)
File "C:\Python34\lib\site-packages\pandas\io\sql.py", line 1172, in to_sql
table.insert(chunksize)
File "C:\Python34\lib\site-packages\pandas\io\sql.py", line 717, in insert
self._execute_insert(conn, keys, chunk_iter)
File "C:\Python34\lib\site-packages\pandas\io\sql.py", line 692, in _execute_insert
conn.execute(self.insert_statement(), data)
File "C:\Python34\lib\site-packages\sqlalchemy-1.0.0b4-py3.4.egg\sqlalchemy\engine\base.py", line 914, in execute
File "C:\Python34\lib\site-packages\sqlalchemy-1.0.0b4-py3.4.egg\sqlalchemy\sql\elements.py", line 323, in _execute_on_connection
File "C:\Python34\lib\site-packages\sqlalchemy-1.0.0b4-py3.4.egg\sqlalchemy\engine\base.py", line 1010, in _execute_clauseelement
File "C:\Python34\lib\site-packages\sqlalchemy-1.0.0b4-py3.4.egg\sqlalchemy\engine\base.py", line 1146, in _execute_context
File "C:\Python34\lib\site-packages\sqlalchemy-1.0.0b4-py3.4.egg\sqlalchemy\engine\base.py", line 1332, in _handle_dbapi_exception
File "C:\Python34\lib\site-packages\sqlalchemy-1.0.0b4-py3.4.egg\sqlalchemy\util\compat.py", line 188, in raise_from_cause
File "C:\Python34\lib\site-packages\sqlalchemy-1.0.0b4-py3.4.egg\sqlalchemy\util\compat.py", line 181, in reraise
File "C:\Python34\lib\site-packages\sqlalchemy-1.0.0b4-py3.4.egg\sqlalchemy\engine\base.py", line 1116, in _execute_context
File "C:\Python34\lib\site-packages\sqlalchemy-1.0.0b4-py3.4.egg\sqlalchemy\engine\default.py", line 439, in do_executemany
sqlalchemy.exc.InterfaceError: (sqlite3.InterfaceError) Error binding parameter 2 - probably unsupported type. [SQL: 'INSERT INTO "WorldBorders" ("ISO2", "NAME", geometry) VALUES (?, ?, ?)'] [parameters: (('AG', 'Antigua and Barbuda', <shapely.geometry.multipolygon.MultiPolygon object at 0x0000000006031D30>), ('DZ', 'Algeria', <shapely.geometry.polygon.Polygon object at 0x000000000722C630>), ('AZ', 'Azerbaijan', <shapely.geometry.multipolygon.MultiPolygon object at 0x000000000722C588>), ('AL', 'Albania', <shapely.geometry.polygon.Polygon object at 0x000000000722C7F0>), ('AM', 'Armenia', <shapely.geometry.polygon.Polygon object at 0x000000000722C978>), ('AO', 'Angola', <shapely.geometry.multipolygon.MultiPolygon object at 0x000000000722C898>), ('AS', 'American Samoa', <shapely.geometry.multipolygon.MultiPolygon object at 0x000000000722CA20>), ('AR', 'Argentina', <shapely.geometry.multipolygon.MultiPolygon object at 0x000000000722CB38>) ... displaying 10 of 246 total bound parameter sets ... ('GS', 'South Georgia South Sandwich Islands', <shapely.geometry.multipolygon.MultiPolygon object at 0x0000000007251C18>), ('TW', 'Taiwan', <shapely.geometry.multipolygon.MultiPolygon object at 0x0000000007251D68>))]
Apparently, the geometry
column of the GeoDataFrame
is not converted properly into a suitable Spatialite geometry format by the to_sql
method of Geopandas.
Is there any other way to insert the GeoDataFrame correctly into the database, maybe by using some conversion to another format before inserting?