Re: [Qgis-user] spatialite with update trigger seems to result in 'unselectable' geometry

[Janneke van Dijk]

Hi Claas,
Indeed that worked Thank you so much. Any idea if it's already in 
the documentation somewhere (in which case I clearly didn't read the 
manual...)? Otherwise I'll include that on my to do list.

Again, thanks,
Janneke

On 08/02/2019 00:47, Claas Leiner wrote:

Hi Janneke

in a similar problem,
it helped to update the spatial index:

-
CREATE TRIGGER update_geom AFTER UPDATE ON watersources BEGIN

UPDATE watersources SET geom = Transform(MakePoint(east, north, 4326), 
32638) WHERE id=1678;

-- I have done this in my trigger
Select recoverSpatialIndex('watersources','geom');
SELECT UpdateLayerStatistics('watersources');

END






___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] spatialite with update trigger seems to result in 'unselectable' geometry

[Claas Leiner]

Hi Janneke

in a similar problem,
it helped to update the spatial index:

-
CREATE TRIGGER update_geom AFTER UPDATE ON watersources BEGIN

UPDATE watersources SET geom = Transform(MakePoint(east, north, 4326), 
32638) WHERE id=1678;

-- I have done this in my trigger
Select recoverSpatialIndex('watersources','geom');
SELECT UpdateLayerStatistics('watersources');

END




--
-
GKG-Kassel - Dr.-Ing. Claas Leiner
QGIS-Support und mehr

Geodatenservice, Kartenwerkstatt &
GIS-Schule Kassel

Wilhelmshöher Allee 304 E
34131 Kassel
Tel. 0561/56013445
claas.lei...@gkg-kassel.de

http://www.gkg-kassel.de

Unterstützen Sie QGIS
QGIS-DE e.V. | http://qgis.de
QGIS Projekt | http://qgis.org/de/site/
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

[Qgis-user] spatialite with update trigger seems to result in 'unselectable' geometry

[Janneke van Dijk]

Dear list,

Could anybody maybe help me understand what I'm doing wrong (or even 
tell me that what I want to do can't be done)?


This is what I'm trying to do. I have a spatialite layer with columns 
north and east which contain coordinates in decimal degrees. I would 
like to be able to change the coordinates in the attribute table and 
have the trigger take care of updating the geometry based on the new 
coordinates. The following update statement, using the DB manager:


UPDATE watersources SET geom = Transform(MakePoint(east, north, 4326), 
32638) WHERE id=1678;


works as expected: after saving the edits, the point will appear in the 
new location based on the changed coordinates, and I can select the 
point in the map and I can use the identify button to open the form at 
that point. If (by way of a test) I use the same statement inside a 
trigger (and I realise this is not ultimately how I would want to define 
the trigger, but I'm trying to understand the different effects of the 
same update statement):


CREATE TRIGGER update_geom AFTER UPDATE ON watersources BEGIN

UPDATE watersources SET geom = Transform(MakePoint(east, north, 4326), 
32638) WHERE id=1678;


END

I can change the coordinates, save the changes and stop editing, the 
point will indeed appear in the new location, but I can't select it in 
the map, or use the identify feature to open the form. I can locate it 
in the attribute table and select it and use 'zoom to selected' after 
which the map will center on the selected point (but not zoom in). After 
running the update statement 'stand alone' again, all works as expected 
again.


Does anyone have an idea why this could happen? Has anyone tried doing 
something similar in spatialite and gotten it to work? Any tips on how I 
could troubleshoot this? I have a test file with only 4 points that I 
could share if that helps. Using QGIS 3.4.4. on Windows 10.


Any help/thoughts will be appreciated!

Thanks,

Janneke



___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user