Bug report #18520
Updating a geometry by trigger in geopackage db
|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|
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:
password for the video: sgmqgisbug!180323
Same problem with v3.00.
The trigger used is :
CREATE TRIGGER luminaires_upd_updcablesgeom
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";
#3 Updated by Alessandro Pasotti over 2 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 2 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.