PostGIS Centroid coordinates (E, N)

Question

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

Answer 1

You 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>;