[QGIS-Developer] SpatiaLite: SpatialIndex broken after trigger

[a.furieri at lqt.it]

On Sat, 26 Sep 2020 17:40:04 +0200, Prof. Dr. Jörg Höttges wrote:
> Hi,
>
> There is a problem with the SpatialIndex and Triggers in SpatiaLite:
>
> I have defined a trigger, that automaticly creates two geometry
> objects (geop: point object, geom: circle as multiline object), when 
> a
> new dataset with attribute data is added to a table "schaechte".
>
> After execution of an INSERT command you can see in QGIS the geometry
> objects only when no Spatial Index is created (see code below).
>
> If I activate the CREATESPATIALINDEX command by uncommenting the
> respective line(s), the objects are not displayed in QGIS and the
> Spatial Index is broken after the insert executed by the trigger
> SELECT CheckSpatialIndex('schaechte', 'geom') -->  0.
>
> You can make them visible with:
> SELECT RecoverSpatialIndex()
>
> Does anybody know, why the SpatialIndex is broken after the INSERT 
> command?
>
> Here is the SQL-Code to be executed in the database window of QGIS or
> in SpatiaLiteGUI:
>
> ------------------------- <Snip> -----------------------
>

Short reply: you are attempting to push SQLite/SpatiaLite
far beyond their intended limits.

Long reply: on SQLite / SpatiaLite a Spatial Index is not
really an _INDEX_, it's just another independent table of
the very special R*Tree type, and the SQL engine itself
hasn't the sligthtests idea that the two tables are
linked together.

the implementation of Spatial Index on SpatiaLite
critically depends on AFTER INSERT and AFTER UPDATE
Triggers that are responsible for keeping the two
tables in the expected synchonized state.

adding a further AFTER INSERT Trigger that introduces
so heavy manipulation on the Geometry itself simply
causes chaos (I strongly suspect because there is
nothing ensuring that all Triggers will fire in a
logically consistent sequence).

-------------------------------------

a possible workaround for circumventing this issue
could be using the intermediation of a writable view.

--
-- creating the main table
--
CREATE TABLE schaechte (
     pk INTEGER PRIMARY KEY,
     schnam TEXT,
     xsch REAL,
     ysch REAL,
     sohlhoehe REAL,
     deckelhoehe REAL,
     durchm REAL
);

--
-- adding Geometries and Spatial Indices
--
SELECT AddGeometryColumn('schaechte','geop',25832,'POINT',2);
SELECT AddGeometryColumn('schaechte','geom',25832,'MULTIPOLYGON',2);
SELECT CreateSpatialIndex('schaechte', 'geop');
SELECT CreateSpatialIndex('schaechte', 'geom');

--
-- creating the convenience View
--
CREATE VIEW schaechte_vw AS SELECT * FROM schaechte;

--
-- transforming the View into a Writable View
--
CREATE TRIGGER IF NOT EXISTS create_missing_geoobject_schaechte
            INSTEAD OF INSERT ON schaechte_vw FOR EACH ROW
   WHEN
     new.geom IS NULL AND
     new.geop IS NULL
   BEGIN
     INSERT INTO schaechte
       (pk, schnam, xsch, ysch, sohlhoehe, deckelhoehe, durchm, geop, 
geom)
     VALUES (
       new.pk, new.schnam, new.xsch, new.ysch, new.sohlhoehe,
       new.deckelhoehe, new.durchm, MakePoint(new.xsch, new.ysch, 
25832),
       CastToMultiPolygon(MakePolygon(MakeCircle(new.xsch, new.ysch,
         coalesce(new.durchm / 1000, 1), 25832)))
     );
   END;

--
-- populating the convenience View
--
INSERT INTO schaechte_vw (schnam, xsch, ysch, sohlhoehe, deckelhoehe) 
VALUES
('D110036', 388798.830197, 5709945.16474, 79.51, 82.56),
('D110073', 388749.988968, 5709812.89315, 82.77, 85.47),
('D110074', 388709.61619, 5709930.66496, 80.82, 83.49),
('D110075', 388813.978304, 5709854.59264, 81.16, 84.09),
('D110076', 388809.473765, 5709888.00614, 80.49, 83.61);


As you can easily check this time the Spatial Indices
are correctly synchronized as expected.

final remark: the QGIS-devel mailing list seems to be a
very odd place for posting questions about SQLite and
SpatiaLite that obviously have nothing to do with
QGIS itself; I've read your post just by pure chance.
next time please send your posts about SpatiaLite on
the appropriate discussion group:
https://groups.google.com/forum/#!forum/spatialite-users

Best regards,
Sandro Furieri
(creator and maintainer of SpatiaLite)