Having changes to SpatiaLite table column to automatically update GIS layer

Question

If the length of a line is changed (direct SQL), I would like the layer to reflect the new line length and also update the label for the feature.

How can that be accomplished?

It appears that PostgreSQL Triggers can send a notification to QGIS, but I cannot find the equivalent for SpatiaLite.

Your reply leads me to wonder if my application is properly using Spatialite. I have a windows application that I would like to use QGIS as the graphical frontend interface. The application has many non-geometry tables of data. Only a few of the tables have data that I would like exposed to QGIS.

One of the tables (named conduits), contains the column recid, flowrate, velocity, slope, upinvert, downinvert. On the QGIS side, the user can create a linestring spatialite table (named qisreach) with a column named Id. I have joined the conduits table with the qisreach table using recid = id.

It is the joined fields that I would like to present in the label attribute of the qisreach layer. The problem that I have is notifying QGIS that one of the joined columns (flowrate, velocity, slope, upinvert, downinvert) has been updated so that the gisreach layer will be updated.

I considered writing an update trigger against the conduit table, to update the gisreach table but since the columns are joined, I don't know how what to update in the gisreach table. I have tried just updating the entire geometry column with itself:

CREATE TRIGGER IF NOT EXISTS conduit_Update
AFTER UPDATE
ON conduitTable
BEGIN
    UPDATE gisreach SET geometry = geometry;
END;

But I get an SQL error no such function:RTreeAlign

Answer 1

You can use triggers in SpatiaLite too. In fact, you probably already are, and just didn't notice it.

Using the command line client:

spatialite> CREATE TABLE lines (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, linename TEXT NOT NULL, linelength DOUBLE);
spatialite> SELECT AddGeometryColumn('lines', 'the_geom', 4326, 'LINESTRING', 'XY', 1);
1

That created triggers to keep everything sane, which you can see in the database:

spatialite> SELECT * FROM sqlite_master WHERE type = 'trigger' and tbl_name = 'lines';
trigger|ggi_lines_the_geom|lines|0|CREATE TRIGGER "ggi_lines_the_geom" BEFORE INSERT ON "lines"
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'lines.the_geom violates Geometry constraint [geom-type or SRID not allowed]')
WHERE (SELECT geometry_type FROM geometry_columns
WHERE Lower(f_table_name) = Lower('lines') AND Lower(f_geometry_column) = Lower('the_geom')
AND GeometryConstraints(NEW."the_geom", geometry_type, srid) = 1) IS NULL;
END
trigger|ggu_lines_the_geom|lines|0|CREATE TRIGGER "ggu_lines_the_geom" BEFORE UPDATE OF "the_geom" ON "lines"
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'lines.the_geom violates Geometry constraint [geom-type or SRID not allowed]')
WHERE (SELECT geometry_type FROM geometry_columns
WHERE Lower(f_table_name) = Lower('lines') AND Lower(f_geometry_column) = Lower('the_geom')
AND GeometryConstraints(NEW."the_geom", geometry_type, srid) = 1) IS NULL;
END
trigger|tmu_lines_the_geom|lines|0|CREATE TRIGGER "tmu_lines_the_geom" AFTER UPDATE ON "lines"
FOR EACH ROW BEGIN
UPDATE geometry_columns_time SET last_update = strftime('%Y-%m-%dT%H:%M:%fZ', 'now')
WHERE Lower(f_table_name) = Lower('lines') AND Lower(f_geometry_column) = Lower('the_geom');
END
trigger|tmi_lines_the_geom|lines|0|CREATE TRIGGER "tmi_lines_the_geom" AFTER INSERT ON "lines"
FOR EACH ROW BEGIN
UPDATE geometry_columns_time SET last_insert = strftime('%Y-%m-%dT%H:%M:%fZ', 'now')
WHERE Lower(f_table_name) = Lower('lines') AND Lower(f_geometry_column) = Lower('the_geom');
END
trigger|tmd_lines_the_geom|lines|0|CREATE TRIGGER "tmd_lines_the_geom" AFTER DELETE ON "lines"
FOR EACH ROW BEGIN
UPDATE geometry_columns_time SET last_delete = strftime('%Y-%m-%dT%H:%M:%fZ', 'now')
WHERE Lower(f_table_name) = Lower('lines') AND Lower(f_geometry_column) = Lower('the_geom');
END

It isn't necessary to follow all of that, just to know that is how SpatiaLite keeps geometries sane.

Let create some data. At this stage, we don't have any additional triggers, but those integrity checks are in place.

spatialite> INSERT INTO lines (id, linename, linelength, the_geom) VALUES (NULL, 'line 1', 1.4, GeomFromText('LINESTRING(1.0 1.0, 2.0 2.0)', 4326));
spatialite> SELECT id, linename, linelength, AsEWKT(the_geom) FROM lines;
1|line 1|1.4|SRID=4326;LINESTRING(1 1,2 2)

Because we don't have any triggers yet, if we change the geometry, the line length will be wrong, as you can see here, and which is the problem you identified above.

spatialite> UPDATE lines SET the_geom=GeomFromText('LINESTRING(1.0 1.0, 2.0 3.0)', 4326);
spatialite> SELECT id, linename, linelength, AsEWKT(the_geom) FROM lines;
1|line 1|1.4|SRID=4326;LINESTRING(1 1,2 3)

Of course we can get SpatiaLite to recalculate it for us, with something like:

spatialite> UPDATE lines SET linelength=ST_Length(the_geom);
spatialite> SELECT id, linename, linelength, AsEWKT(the_geom) FROM lines;
1|line 1|2.23606797749979|SRID=4326;LINESTRING(1 1,2 3)

So lets create a trigger to do that when things get updated:

spatialite> CREATE TRIGGER lines_length_update AFTER UPDATE OF the_geom ON lines 
   ...> BEGIN
   ...>     UPDATE lines SET linelength=ST_Length(NEW.the_geom) WHERE lines.ROWID = NEW.ROWID;
   ...> END;

Its also possible to do this on newly added linestrings (any new row):

spatialite> CREATE TRIGGER lines_length_insert AFTER INSERT ON lines 
   ...> BEGIN
   ...>     UPDATE lines SET linelength=ST_Length(NEW.the_geom) WHERE lines.ROWID = NEW.ROWID;
   ...> END;

Now lets check that:

spatialite> UPDATE lines SET the_geom=GeomFromText('LINESTRING(1.0 1.0, 2.0 2.0)', 4326);
spatialite> SELECT id, linename, linelength, AsEWKT(the_geom) FROM lines;
1|line 1|1.4142135623731|SRID=4326;LINESTRING(1 1,2 2)

spatialite> INSERT INTO lines (id, linename, the_geom) VALUES (NULL, 'line 2', GeomFromText('LINESTRING(2.0 2.0, 4.0 4.0)', 4326));
spatialite> SELECT id, linename, linelength, AsEWKT(the_geom) FROM lines;
1|line 1|1.4142135623731|SRID=4326;LINESTRING(1 1,2 2)
2|line 2|2.82842712474619|SRID=4326;LINESTRING(2 2,4 4)

So it looks like that worked OK.

However there might be a better way using a View - whether it works for you depends on your needs, which I won't claim to understand.

The key point to the view is that if all the information in a column can be derived from other things, maybe you don't need to have that column in the database. So if the name of the line and the length are just functions of other things, you can just produce them query time, and not have to try to keep track of them during update. Its part of the "DRY" - don't repeat yourself - principle.

Here is an example.

spatialite> CREATE TABLE lines_base (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT);
spatialite> SELECT AddGeometryColumn('lines_base', 'the_geom', 4326, 'LINESTRING', 'XY', 1);
1 
spatialite> INSERT INTO lines_base (id, the_geom) VALUES (NULL, GeomFromText('LINESTRING(1.0 1.0, 2.0 2.0)', 4326));
spatialite> SELECT id, AsEWKT(the_geom) FROM lines_base;
1|SRID=4326;LINESTRING(1 1,2 2)

So far its like the previous example, I've just left out the name and length columns.

Now here comes the view.

spatialite> CREATE VIEW lines (id, linename, linelength, the_geom) AS SELECT id, "line "||id, ST_LENGTH(the_geom), the_geom FROM lines_base;

Now you can treat that view like it was a table, and sqlite and spatialite will produce the derived values:

spatialite> SELECT id, linename, linelength, AsEWKT(the_geom) FROM lines;
1|line 1|1.4142135623731|SRID=4326;LINESTRING(1 1,2 2)

If you want to make a change, you do that on the writable table, not on the view:

spatialite> UPDATE lines_base SET the_geom=GeomFromText('LINESTRING(1.0 1.0, 2.0 3.0)', 4326);

And it gets automatically reflected in the view:

spatialite> SELECT id, linename, linelength, AsEWKT(the_geom) FROM lines;
1|line 1|2.23606797749979|SRID=4326;LINESTRING(1 1,2 3)

Views are not writable (e.g. you can't INSERT or UPDATE) in SQLite. However you can emulate it using a trigger, which is described in https://www.gaia-gis.it/fossil/libspatialite/wiki?name=writable-view . I would suggest avoiding that as a starting point though - its difficult to get all the corner cases right. Just do the UPDATEs on the real data, and know you can VIEW the results later.

Answer 2

Found a solution, not perfect, but doable.

The issue is refreshing the labels of a spatialite layer where the labels are from columns joined with a non-geometry table.

I have found that in the join, there is an option to Cache join layer in memory. DO NOT select that.

To update the Labels in the spatialite layer, all that is necessary is to select the Data Dependencies option in Properties and create a dependency on the joined, non-geometry table. Once the dependency is created, changes in the non-geometry table joined fields will be updated in the spatialite layer.

There appears to be a catch, the layer must be redrawn, I have been rolling the mouse zoom up and down the update the layer, but it is preferable to closing the project and reopening.