Spatialite/SQLite snippets🔗

1. Introduction
2. Shift/translate a geometric table
3. Make a cross-table UPDATE in SQLite
4. Use Spatialite to concatenate layers

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.

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

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 table2.id = table1.id);

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 MAE_S_049_2011.tab 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/MAE_S_049_2011.tab -dsco SPATIALITE=YES; done
# Then you just have to extract the layer in a MapInfo file:
ogr2ogr -f MapInfo MAE_S_049_2011.tab test.sqlite mae_s_049_2011