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;
3
  • 1
    Yes, that's possible - but you need to provide more details. At least the create table statement for your table.
    – user1822
    Jul 20, 2015 at 9:51
  • it is an update trigger on a table which contains point geometries. it has fields called 'easting' and 'northing'. Jul 20, 2015 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. Jul 22, 2015 at 1:08

1 Answer 1

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.

1

Your Answer

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

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