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:
CREATE TABLE schaechte (
pk INTEGER PRIMARY KEY,
schnam TEXT,
xsch REAL,
ysch REAL,
sohlhoehe REAL,
deckelhoehe REAL,
durchm REAL
);
SELECT AddGeometryColumn('schaechte','geop',25832,'POINT',2);
SELECT AddGeometryColumn('schaechte','geom',25832,'MULTIPOLYGON',2);
-- SELECT CreateSpatialIndex('schaechte', 'geop');-- geop objects are visible
SELECT CreateSpatialIndex('schaechte', 'geom');-- geom objects are not visible
CREATE TRIGGER IF NOT EXISTS create_missing_geoobject_schaechte
AFTER INSERT ON schaechte FOR EACH ROW
WHEN
new.geom IS NULL AND
new.geop IS NULL
BEGIN
UPDATE schaechte SET geop =
MakePoint(new.xsch, new.ysch, 25832)
WHERE schaechte.pk = new.pk;
UPDATE schaechte SET geom =
CastToMultiPolygon(MakePolygon(MakeCircle(new.xsch, new.ysch,
coalesce(new.durchm / 1000, 1), 25832)))
WHERE schaechte.pk = new.pk;
END;
INSERT INTO schaechte (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);
Regards,
Jörg Höttges
_______________________________________________
QGIS-Developer mailing list
[email protected]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
