After a week of unsuccessful fighting I'm forced to ask for your help.

I have created a table 't_excavations' with fields 'id_excavation', 'geom' (MULTIPOLYGONE), 'longitude', 'latitude'

CREATE TABLE public.t_excavations ( id_excavation serial NOT NULL PRIMARY KEY, longitude numeric(9,6), latitude numeric(9,6), geom public.geometry ) WITH ( OIDS = FALSE );

Then I created a rule to auto update 'longitude' and 'latitude' columns to get XY of the polygon centroid that I add through QGIS

CREATE OR REPLACE RULE "insert_longlat" AS ON INSERT TO "public"."t_excavations" DO (UPDATE t_excavations SET longitude = st_x(st_centroid(t_excavations.geom)); UPDATE t_excavations SET latitude = st_y(st_centroid(t_excavations.geom)));

This rule works when I save changes in QGIS and coordinates are calculated fine

Now I want to update 'longitude' and 'latitude' columns if 'geom' field is changed (for example if I move the entire polygon or just one vertice)

I created a function:

CREATE OR REPLACE FUNCTION update_longlat() RETURNS trigger AS $BODY$ BEGIN NEW.longitude = ST_X(ST_Centroid(geom)); NEW.latitude = ST_Y(ST_Centroid(geom)); RETURN NEW; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION update_longitude() OWNER TO postgres;

and a trigger:

 CREATE TRIGGER tr_update_longlat BEFORE UPDATE ON t_excavations FOR EACH ROW EXECUTE PROCEDURE update_longlat();

And now when I try to add new or edit existing polygon in QGIS I get this error message from postgres:

Can't change layer t_excavations

Errors: ERROR: not added 1 object.

Errors of the source:

Error PostGIS while adding objects: ERROR: column "geom" doesn't exist LINE 1: SELECT ST_X(ST_Centroid(geom)) ^ QUERY: SELECT ST_X(ST_Centroid(geom)) CONTEXT: function PL/pgSQL update_longlat(), row 3, assignment operator

What I do wrong with the function/trigger?

1 Answer 1


I think your trigger function should look something like this:

 lat := ST_Y(ST_Centroid(NEW.geom));
 lon := ST_X(ST_Centroid(NEW.geom));
 id := NEW.id_excavation;
 UPDATE t_excavations SET longitude=lon, latitude=lat WHERE id_excavation=id;

and you'll want the trigger defined as AFTER UPDATE. The "RETURN NULL" should prevent an endless update loop.

  • Just got the answer from "simplexio": "...geom needs to be new.geom , not geom..." So in the function code I should write: NEW.longitude = ST_X(ST_Centroid(new.geom)); NEW.latitude = ST_Y(ST_Centroid(new.geom)); Now it works! Thanks him and you all for help! I appreciate it very much!
    – Stas
    Mar 17, 2015 at 12:51

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.