1

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

1 Answer 1

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.