Given a Spatialite database, holding a table with a geometry
column and some other attribute columns. I want to query that table in a Python script and get a GeoJSON FeatureCollection string in return which stores one GeoJSON Feature per result row, each feature being composed of the geometry from the geometry
column and all the other columns as attributes.
Connecting to a SQLite database with Spatialite extensions and doing a query in Python:
import sqlite3
# open database file
conn = sqlite3.connect('MyDatabase.sqlite')
# load spatialite extensions. make sure that mod_spatialite.dll is somewhere in your system path.
conn.enable_load_extension(True)
conn.execute('SELECT load_extension("mod_spatialite.dll")')
# get a cursor
cursor = conn.cursor()
# compose a query
query = """
SELECT
geometry,
firstAttribute,
secondAttribute,
thirdAttribute,
FROM
MyTable
;
"""
# execute query
cursor.execute(query)
# get results
results = cursor.fetchall()
However, results
is now a list of tuples (one tuple per row) filled with strings (I never understood why this could be useful ever…), i.e.
[(b'\x00\x01\xe6\x10\x00\x00\xde\xe4\xb7\xe8d\xa9\x95\xbf\xf9\xf5Cl\xb0\xbcI@\xde\xe4\xb7\xe8d\xa9\x95\xbf\xf9\xf5Cl\xb0\xbcI@|\x01\x00\x00\x00\xde\xe4\xb7\xe8d\xa9\x95\xbf\xf9\xf5Cl\xb0\xbcI@\xfe', 'value1', 'value2', 'value3'), ...]
The geometry
column is given as binary (as stored in the database), however, getting it as GeoJSON representation would be possible by changing the query from SELECT geometry ... FROM ...
to SELECT AsGeoJSON(geometry) ... FROM ...
.
Still, this is miles away from the format I want to achieve in the end.
Any suggestions how this could be done elegantly without having to split the tuple into smallest parts and recomposing them manually to the desired format?
Best Answer
Okay, got it meanwhile. There are probably different ways to do this but this one works fine. Apart from access to your Spatialite database via Python's
sqlite3
module and the Spatialite extension, you'll need the geojson module (simply install withpip install geojson
).For the sake of completeness, let's create a new Spatialite database first and fill it with an example row of data. The comments explain in detail what is going on.
Now, the next step is to overwrite the
row_factory
of Python'ssqlite3
module to return query results as lists of dictionaries (with one dictionary per result row) instead of list of tuples which is the default behaviour.Now, define your desired database query, execute it and fetch the results as a list of dictionaries (one dictionary per results row):
We will now iterate over the results rows, create a single GeoJSON feature for each row and store it in a predefined list. The
geometry
column of your database table (or whatever it is called) can be directly returned as a GeoJSON string by putting it as argument to Spatialite'sAsGeoJSON
function in the query. This GeoJSON string is now part of the returned dictionary of each row and can be accessed via the indexAsGeoJSON(geometry)
(or whatever the column was called). It can then be given to theloads
function of thegeojson
module which will create a serialized GeoJSON geometry of that string.After deleting the
AsGeoJSON(geometry)
entry from the row dictionary, what stays is a dictionary holding all the other column values as key-value pairs except for the geometry. Fortunately, thegeojson
module can create aGeoJSON Feature
by passing aGeoJSON Geometry
and a dictionary of properties. Each single feature is then appended to the predefined list.Finally, the
FeatureCollection
constructor of thegeojson
module accepts a list of singleGeoJSON Features
to unite them into aFeatureCollection
:Finally, dump the created
FeatureCollection object
into a string:That's it! Note that the resulting string will not be pretty printed which I do not mind since I am passing it on to a Leaflet/JavaScript function:
If you want it pretty-printed, pass the
indent
argument to thegeojson.dumps
method:which gives: