How to create postgresql triggers to calculate geometry from x,y and vice versa at the same time

Question

I have a postgis table with point geometry which looks like this

CREATE TABLE mypoints 
 (
  gid serial NOT NULL,
  coordid integer,
  geom geometry(Point,4326)
 );

And a table with coordinates like this

CREATE TABLE mycoords
 (
  coordid serial NOT NULL, 
  x numeric(10,4),
  y numeric(10,4)
 );

I want to create several triggers, that update or insert mypoints.geom value with MakePoint(mycoords.x, mycoords.y) function, taking values from joined mycoords table, and at the same time vice versa - when mypoints.geom is updated or inserted, correlated mycoords.x and mycoords.y values should be calculated with ST_X(mypoints.geom) and ST_Y(mypoints.geom) respectively.

How can I do that?

Answer 1

Is there a reason you have mycoords in a separate table from mypoints? If no reason I would combine them. For example do you do that because if a point changes you expect all geometries that had that point to change (so yo have a 1 to many?). If it's always a 1 to 1 I would collapse the two into one table to make your life easier.

The main issue I see with what you have is a contention issue. If you have a trigger on mypoints and mycoords, you'll probably need to have a bit that is set during the trigger to denote an update by the trigger. Otherwise you'd run into a vicious cycle with update to mypoints trying to update mycoords and mycoords update trying to update mypoints.

We have an example somehwat similar to this in appendix D of PostGIS In Action 2nd http://www.postgis.us/chapter_appendix_d_edition_2 (downloadable from there). Look for geography trigger function in the code file which both updates a field and also writes to a log table. In your case you'd want to do a lookup in mycoords and vice versa and have a similar trigger on mycoords.

The main thing you will want to consider is comparing the OLD and NEW records passed in via the trigger and comparing what was there before OLD with the NEW what got passed in, to determine whether to update the coordinates or the geometry or neither.

IF it's an INSERT, you'll need to check which fields have data to know which one to set.