Bug report #18520

Updating a geometry by trigger in geopackage db

Added by Etienne MORO about 2 years ago. Updated almost 2 years ago.

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;

pbGeopackageTrigger.zip (644 KB) Etienne MORO, 2018-03-23 09:24 AM

History

#1 Updated by Alessandro Pasotti almost 2 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 almost 2 years ago

  • Category changed from Data Provider/SpatiaLite to Data Provider/OGR

#3 Updated by Alessandro Pasotti almost 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 almost 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.

#5 Updated by Alessandro Pasotti almost 2 years ago

  • Resolution set to invalid
  • Status changed from Feedback to Closed

Also available in: Atom PDF