Bug report #18520
Updating a geometry by trigger in geopackage db
Status: | Closed | ||
---|---|---|---|
Priority: | High | ||
Assignee: | Alessandro Pasotti | ||
Category: | Data Provider/OGR | ||
Affected QGIS version: | 2.18.17 | Regression?: | No |
Operating System: | Windows 7 | Easy fix?: | No |
Pull Request or Patch supplied: | No | Resolution: | invalid |
Crashes QGIS or corrupts data: | No | Copied to github as #: | 26408 |
Description
Hello,
I have 2 layers (luminaires and cables) in a geopackage db, and I've defined a trigger in order to update the position of the line when a point is moved (the line should follow the position of the point). The problem is that after the trigger acts, the line moved by the trigger disappears. In fact when I look at the db, the geometry of that line seems to be correct, but the line is not drawn in QGIS.
I've done exactly the same oprations with a spatialite db and it works correctly. The problem is only with a geopackqage db.
You'll find attached the files used (a geopackage version with the problem and a spatialite version that works correctly), and you can see a video that shows you the problem at this address:
https://sigmoe.fr/problem-qgis-geopackage-trigger
password for the video: sgmqgisbug!180323
Same problem with v3.00.
The trigger used is :
CREATE TRIGGER luminaires_upd_updcablesgeom
AFTER UPDATE
ON luminaires
BEGIN
UPDATE cables SET
geom = ST_SetStartPoint(geom, NEW.geom),
observations = 'point départ changé'
WHERE cables."lum départ" = OLD."numéro";
UPDATE cables SET
geom = ST_SetEndPoint(geom, NEW.geom),
observations = 'point final changé'
WHERE cables."lum arrivée" = OLD."numéro";
END;
History
#1 Updated by Alessandro Pasotti over 6 years ago
- Pull Request or Patch supplied changed from Yes to No
- Crashes QGIS or corrupts data changed from Yes to No
#2 Updated by Alessandro Pasotti over 6 years ago
- Category changed from Data Provider/SpatiaLite to Data Provider/OGR
#3 Updated by Alessandro Pasotti over 6 years ago
- Status changed from Open to Feedback
As Even suggested:
I think I've an idea of what's going wrong. geom = ST_SetStartPoint(geom, NEW.geom) generates a a spatialite geom, not a GPKG one. Should probably be geom = AsGpkg(ST_SetStartPoint(geom, NEW.geom))
Can you check if this fix the issue?
#4 Updated by Etienne MORO over 6 years ago
- Assignee set to Alessandro Pasotti
The idea was good, but it's not AsGpgk that I should use, but AsGPB, to transform the geometry into a binary geopackage geometry, and then it works well. So, the right code is:
geom = AsGPB(ST_SetStartPoint(geom, NEW.geom))
Thank you for the idea.
#5 Updated by Alessandro Pasotti about 6 years ago
- Resolution set to invalid
- Status changed from Feedback to Closed