Using Triggers in PGAdmin4 v5 to get the geom of a line being generated in QGIS

Question

So I have a database of pipes that is visually coded in qgis. My goal is to add a trigger to PGAdmin4 v5 that uses ST_StartPoint() and ST_EndPoint() to get the end points of the polyline (the pipe) as it is being entered. Then, I was going use this geometry to grab the nearest structure (a point) and autocomplete two columns with corresponding data. I have working code that gets me the endpoints of existing pipes and finds the nearest structures, but I want to use the newly created lines (the pipes) to complete the form automatically as it is being entered into the database.

Working Code:

 SELECT p.id,
    dump_line.geom,
    st_endpoint(dump_line.geom) AS downstream,
    j.geom_closest_downstream,
    st_startpoint(dump_line.geom) AS upstream,
    i.geom_closest_upstream
   FROM sewers.pipes p,
    LATERAL st_dump(st_setsrid(p.geom, 2965)) dump_line(path, geom),
    LATERAL ( SELECT s.geom
           FROM sewers.structures s
          ORDER BY (st_endpoint(dump_line.geom) <-> s.geom)
         LIMIT 1) j(geom_closest_downstream),
    LATERAL ( SELECT s.geom
           FROM sewers.structures s
          ORDER BY (st_startpoint(dump_line.geom) <-> s.geom)
         LIMIT 1) i(geom_closest_upstream);

I need to figure out how to get data from an update (adding an entry to qgis) and use its geometry instead of the geometry of existing pipes.

@LaurenzAlbe I edited the post to be more clear. By form, I meant column. Hopefully this edit adds clarity. — AThomspon, Jul 19 '21 at 15:07
Use a `BEFORE` trigger and assign to the respective columns in `NEW` to modify the row about to be inserted. — Laurenz Albe, Jul 19 '21 at 15:10
@LaurenzAlbe I am relatively new to Triggers, can you mock up a fiddle? — AThomspon, Jul 19 '21 at 15:10

Jim Jones · Accepted Answer · 2021-07-19T17:54:01.203

2

Just put this query into a function ..

CREATE OR REPLACE FUNCTION insert_pipe() RETURNS TRIGGER AS $$
BEGIN
  SELECT 
  j.geom_closest_downstream,
  i.geom_closest_upstream
  INTO NEW.geom_closest_downstream, NEW.geom_closest_upstream
  FROM ST_Dump(NEW.geom) dump_line,
  LATERAL (SELECT s.geom 
           FROM structures s
           ORDER BY ST_EndPoint((dump_line).geom)<->s.geom 
           LIMIT 1) j (geom_closest_downstream),
  LATERAL (SELECT s.geom 
           FROM structures s
           ORDER BY ST_StartPoint((dump_line).geom)<->s.geom 
           LIMIT 1) i (geom_closest_upstream);
           
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

And attach a trigger to it, e.g. a BEFORE INSERT OR UPDATE trigger:

CREATE TRIGGER t_insert_pipe 
BEFORE INSERT OR UPDATE ON pipes FOR EACH ROW EXECUTE PROCEDURE insert_pipe();

This demo might give you some hints: db<>fiddle

edited Jul 19 '21 at 17:54

answered Jul 19 '21 at 16:03

Jim Jones

18,404
3
35
44

This works, however I now need to use the geom_closest id's to correspond to a "node_id" and insert that into the column. This is what I believe it would be, but I am getting errors of mixed SRID's. https://dbfiddle.uk/?rdbms=postgres_12&fiddle=68c16c71449d0e6121a8f7f258e5660a – AThomspon Jul 19 '21 at 19:39
1

@AThomspon you mean this? https://dbfiddle.uk/?rdbms=postgres_12&fiddle=cfaf4042993e32a96c7c30f6b56fd4b8 – Jim Jones Jul 19 '21 at 21:07
1

@AThomspon keep in mind that this has nothing to do with your original question. So if you need help with something else, ask another question... otherwise it gets too confusing and pretty much useless for other users, which is definitely not the point of StackOverflow ;) So, if this solves your original question, move on to the next one to keep things more palatable. cheers – Jim Jones Jul 19 '21 at 21:10
1

Thanks. I cleaned it up and started a new post with the next question. – AThomspon Jul 20 '21 at 13:13

Using Triggers in PGAdmin4 v5 to get the geom of a line being generated in QGIS

1 Answers1