2

I am wondering if it is at all possible to create trigger that populates the X and Y of a created point into 2 separate fields called 'easting' and 'northing'?

It is an update trigger on a table which contains point geometries.

It has fields called 'easting' and 'northing'. These fields need to converted from integer to double precision due to this error code on saving of the table.

Provider errors:

PostGIS error while adding features: ERROR:  invalid input syntax for integer: "387733.568253859"
CONTEXT:  PL/pgSQL function "tf_xy_27700" line 5 at assignment'*

So I have converted them to double precision. I now need to get these fields to update with the x/y on update.

See below for my code so far:

CREATE OR REPLACE FUNCTION tf_xy_27700()
    RETURNS trigger AS
$BODY$
    BEGIN
        IF TG_OP = 'UPDATE' OR TG_OP = 'INSERT' THEN
            new.easting := ST_X(new.wkb_geometry);
            RETURN NEW;
        END IF;
    END;
$BODY$
LANGUAGE plpgsql;
  • 1
    Yes, that's possible - but you need to provide more details. At least the create table statement for your table. – a_horse_with_no_name Jul 20 '15 at 9:51
  • it is an update trigger on a table which contains point geometries. it has fields called 'easting' and 'northing'. – daniel franklin Jul 20 '15 at 10:32
  • 1
    The explanation is rather confusing. Not going to touch this question without table definition and Postgres version. Also, the whole idea seems flawed. Storing functionally dependent values (redundantly) is a bad idea in most cases. – Erwin Brandstetter Jul 22 '15 at 1:08
1

its okay, and sorry for the bad explanation.

i got it to work as shown below:

CREATE OR REPLACE FUNCTION tf_xy_point()
  RETURNS trigger AS
$BODY$BEGIN

IF TG_OP = 'UPDATE' OR TG_OP = 'INSERT' THEN

    new.easting := ST_X(new.wkb_geometry);

    new.northing := ST_Y(new.wkb_geometry);

RETURN NEW;

END IF;
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION tf_xy_point() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION tf_xy_point() TO public;
GRANT EXECUTE ON FUNCTION tf_xy_point() TO postgres;

This now allows for the trigger to used as generic for any other table wishing to find the eastings and northings on UPDATE.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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