1

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?

3
  • 1
    Have you tried anything? It seems like you understand which functions to use, so what problem have you faced implementing these triggers. Dec 29 '14 at 14:41
  • Do you have a good reason for having such data redundancy?
    – Martin F
    Dec 30 '14 at 3:00
  • Sure I do. It's more complicated in reality: coordinate data comes from external resource, and the geometry itself is also editable. So the data should be consistent both ways.
    – mofoyoda
    Dec 30 '14 at 8:40
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.

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.