I would like to add two new columns in the table (E, N), fill in the coordinates of a polygon centroid using PostGIS SQL code and have the column upadated as soon as I save the edit of a drawing in QGIS.
Obs: MyGeometry is already in UTM
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.
Sign up to join this communityYou can use a trigger to update those columns; assuming you have them created as easting
& northing
(for this example, of course; you can use "E"
& "N"
as column names, but I would advise against uppercase and the need for ""
enclosing):
CREATE OR REPLACE FUNCTION update_en()
RETURNS TRIGGER AS
$$
BEGIN
NEW.easting := ST_X(ST_Centroid(NEW.geom)); -- ST_X(ST_Transform(ST_Centroid(NEW.geom), 4326)) for Lon in degree
NEW.northing := ST_Y(ST_Centroid(NEW.geom)); -- ST_Y(ST_Transform(ST_Centroid(NEW.geom), 4326)) for Lat in degree
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER update_en
BEFORE UPDATE OR INSERT ON <your_table>
FOR EACH ROW
EXECUTE PROCEDURE update_en();
And, for completeness, to create/initialize those columns:
ALTER TABLE <your_table>
ADD COLUMN easting DOUBLE PRECISION,
ADD COLUMN northing DOUBLE PRECISION;
UPDATE <your_table>
SET (easting, northing) = (ST_X(ST_Centroid(geom)), ST_Y(ST_Centroid(geom)));
VACUUM ANALYZE <your_table>;