Trigger to update geometry from another table

[Kyle Felipe Vieira Roberto]

Hi guys!
I was trying to make a point table, called teste_pontos, using (st_pointsOnSurface) from a table, called producao_florestal, with polygon geometry collumn. (this topic)

But, using Spatial Views i cant change the geometry type from colum geom....
And i have an idea to make a trigger, to update that point table, but, if i use:

CREATE TRIGGER altera_ponto BEFORE UPDATE ON 'producao_florestal' FOR EACH ROW

 BEGIN

 SELECT load_extension('mod_spatialite');

 UPDATE 'teste_pontos' SET geom = ST_PointOnSurface('producao_florestal'.geom)

 WHERE 'teste_pontos'.id = 'producao_florestal'.id;

END;

Im not able to update 'producao_florestal'...

there is a way to do this conversion on the fly on update, insert an delete geometry on producao_florestal?

[a.fu...@lqt.it]

On Tue, 23 Jan 2018 10:27:31 -0800 (PST), Kyle Felipe Vieira Roberto
wrote:

> Hi guys!
> I was trying to make a point table, called teste_pontos, using
> (st_pointsOnSurface) from a table, called producao_florestal, with

> polygon geometry collumn. (this topic [1])

> But, using Spatial Views i cant change the geometry type from colum
> geom....
>

Hi Kyle,

due to the very specific requirements of SQLite, the Geometry column
in any Spatial View _MUST_ always directly match a Geometry coming
from one of the underlaying Spatial Tables.

you are absolutely free to derive any possible Spatial View based on
the "producao_florestal" Spatial Table, but all such Views _MUST_
declare a Geometry column exactly corresponding to the original
Geometries stored within "producao_florestal" (you can just change
the name of the Geometry column, but you are strictly forbidden
to change the geometry-type, its SRID or the dimensions).

a short rationale accounting for all this: any Spatial View has
the capability to effectively support an eventual R*Tree Spatial
Index supporting the underlaying Spatial Table.
but in order to do such a thing both Geometries (the one in the
Spatial Table and the other in the Spatial View) _MUST_ exactly
correspond.

corollary: the Geometry in a Spatial View can never be the
result of some spatial function, as e.g. ST_PointOnSurface()

> And i have an idea to make a trigger, to update that point table,
> but,
> if i use:
>
> CREATE TRIGGER altera_ponto BEFORE UPDATE ON 'producao_florestal' FOR
> EACH ROW
> BEGIN
> SELECT load_extension('mod_spatialite');
> UPDATE 'teste_pontos' SET geom =
> ST_PointOnSurface('producao_florestal'.geom)
> WHERE 'teste_pontos'.id = 'producao_florestal'.id;
> END;
>
> Im not able to update 'producao_florestal'...
>

just two remarks about the above trigger:

1. attempting to load spatialite as a dynamic extension from within
a trigger sounds really extravagant.
usually "load_extension" is expected to be called immediately
after establishing a new DB connection and before attempting
to execute the very first SQL statement of the current session.

2. the UPDATE statement is obviously wrong and will just return
a syntax error.
you can't call "ST_PointOnSurface('producao_florestal'.geom)"
without properly declaring somewhere a corresponding
"FROM producao_florestal" clause.
that's not all; you are obviously required to select just
one row from the "producao_florestal" table, so you'll
necessarily add something like "WHERE rowid = X".

> there is a way to do this conversion on the fly on update, insert an
> delete geometry on producao_florestal?
>

CREATE TRIGGER altera_ponto BEFORE UPDATE ON producao_florestal
FOR EACH ROW BEGIN

INSERT OR REPLACE INTO teste_pontos (id, geom)
VALUES (NEW.id, ST_PointOnSurface(NEW.geom));
END

CREATE TRIGGER insert_ponto BEFORE INSERT ON producao_florestal
FOR EACH ROW BEGIN
INSERT OR REPLACE INTO teste_pontos (id, geom)
VALUES (NEW.id, ST_PointOnSurface(NEW.geom));
END

CREATE TRIGGER delete_ponto AFTER DELETE ON producao_florestal
FOR EACH ROW BEGIN
DELETE FROM teste_pontos WHERE id = OLD.id;
END

-----------------------

just a final pedantic remark.
I notice that you often write something like
'teste_pontos'.id or 'producao_florestal'.geom

this is a violation of pure SQL syntax (all right, SQLite is
rather forgiving about this, but nonetheless it remains
"dirty SQL").

rule #1: all TEXT CONSTANTS must be delimited by
_SINGLE_ quotes.

rule #2: all TABLE or COLUMN names could be eventually
delimited by _DOUBLE_ quotes.

examples:

- ... WHERE "table"."column" = 'some value' ...
this one is a crystal clear SQL syntax

- ... WHERE 'table.'column' = "some value" ...
this is a dirty SQL syntax; may well be that SQLite
and some other DBMS could understand it, but others
SQL tools may legitimately adopt a more rigorous
syntax checking and will consequently consider such
an expressions just as invalid.

bye Sandro

[Kyle Felipe Vieira Roberto]

@Sandro, thx very much for your help agains....

I made some change to fit my needs here....

First trigger:

CREATE TRIGGER altera_ponto BEFORE UPDATE ON producao_florestal 

FOR EACH ROW BEGIN 

     UPDATE testandooo SET geom = ST_PointOnSurface(NEW.geom) 

     WHERE id = NEW.id; 

END 

This just update geom column to the new geometry when producao_florestal is updated to a new place...

CREATE TRIGGER insert_ponto AFTER INSERT ON producao_florestal 

FOR EACH ROW BEGIN 

     INSERT INTO testandooo (id, geom) 

     VALUES (NEW.id, ST_PointOnSurface(NEW.geom)); 

END 

Here i change to AFTER INSERT ON producao_florestal, bc using BEFORE, the trigger did not takes the new id correctly, and when geometry changes, the point was not updating to...

The trigger DELETE was working like a char with those changes....

About the tips... Im learning by my own here.... so this will help improve my code here.... I have seem many tutorials with "wrong" write way.
I will keep your tips close to pratice all time....

Always grateful...

Kyle Felipe...