Spatialite/SQLite snippets 🔗

Posted by Médéric Ribreux 🗓 In kb/snippets/

#GIS #sql #snippets


You sometimes have to do specific things on Spatialite like common requests, vacuum and other generic operations. When you are not a dedicated sysadmin, it is generally a waste of time to search information on the web to answer your problem because you'll have to do it everytime you will work with Spatiale.

In this page, I try to fix the maximum of howto information on common Spatialite/SQlite problems. It is here to help you better handle your geographic data and requests.

Shift/translate a geometric table

We want to translate the objects of a specific table to a determined point. This a kind of translation. It is sometimes useful to manually reassign the approximative placement of a non well georeferenced object (or table). For example, I used QGis to planify the moves in our work building and the only plans I've got were not georeferenced (DXF world). An import in Spatialite moves everything around (0.0; 0.0). It doesn't seems to be a problem for mapping work but it was prone to bugs in QGis composer. So I had to translate the whole tables to a place that was better covered by the SRS of the tables (Lambert93).

Simply use the ShiftCoords function like this:

-- Move the geometric objects of table "LAYER" to 434069.0; 6696787.0:
UPDATE "LAYER" SET Geometry=ShiftCoords(Geometry, 434069.0, 6696787.0);

Make a cross-table UPDATE in SQLite

The UPDATE syntax in SQlite is a bit different than PostgreSQL ones. In particular, the syntax is very different for cross-table (update a column table with the value of another one joined with the table to update).

Here is the syntax for SQLite:

-- Make a cross-table UPDATE in SQLite between table1 and table2
UPDATE table1 SET column = (SELECT value FROM table2 WHERE =;

Use Spatialite to concatenate layers

This is not a direct Spatialite trick. You have a bunch of layers that got the same structure but different areas. Your goal is to make a unique layer with all of those files. Imagine that your files are stored in a file format which cannot be updated (like MapInfo for example). You have to use Spatialite and a POSIX Shell to load the layers in a Spatialite DB and then extract the resulting layer.

# use to concatenate all of the layers from 8 different directories
# named couches_00 to couches_07:
for i in $(seq 0 7);do ogr2ogr -append -update -f SQLite test.sqlite ../couches_0$i/ -dsco SPATIALITE=YES; done
# Then you just have to extract the layer in a MapInfo file:
ogr2ogr -f MapInfo test.sqlite mae_s_049_2011