0

Context

I have set up a trigger on a PostgreSQL (+Postgis) table containing observation points.

This trigger must compute a new geometry for each inserted or updated point. This new geometry must be calculated as the projection of the geometry of the currently inserted feature point onto the nearest line of another table containing line features.

What I've done

The trigger is as follow, it is correctly fired as the EXPLAIN ANALYZE shows after a new insertion:

EXPLAIN ANALYZE results

no errors are thrown when I insert a new point, but the projected geometry column proj_geom stays empty:

CREATE OR REPLACE FUNCTION project_funct()
  RETURNS trigger AS
  $$
  BEGIN
    -- Here I wish I could update several fields (line_id, dist and proj_geom) at the same time
    -- but for the moment I only put the focus on the new geometry proj_geom:
    NEW.projected_geom := ( 
      SELECT sub.proj_geom
      FROM
        ( SELECT
             points.id AS point_id,
             schema.lines.id AS line_id,
             ST_Distance(schema.lines.geom, NEW.geom) AS dist,
             ST_ClosestPoint(schema.lines.geom, NEW.geom) AS proj_geom
          FROM schema.line, points
            -- Something is weird to me there, I should probably not have this WHERE statement here as the 
            -- trigger should be used on a single row at a time, hence with a single ID for each execution:
            WHERE points.id = NEW.id 
            ORDER BY dist
            LIMIT 1
        ) AS sub
    );
      RETURN NEW;
  END
  $$
LANGUAGE 'plpgsql';
CREATE TRIGGER proj_trigger
    -- Here, I don't really know if I must used BEFORE or AFTER, AFTER sounds better to me 
    -- but I may be totally wrong. I also wish I could keep the same code for both UPDATE and
    -- INSERT but this may not be a good idea?:
    BEFORE INSERT OR UPDATE ON points 
    FOR EACH ROW
    EXECUTE PROCEDURE project_funct();

As I discovered triggers yesterday evening, and even if I've done my best, my code is probably full of mistakes.
I also noticed some SQL scripts that I already have and which are actually working, ... doesn't work anymore in this fancy(?) way of writing triggers (e.g. I don't know the meaning of the double dollar sign $$).

Notice:
If I take out the inner most SELECT statement, and replace the word NEW by the actual name of the points table, the query, when run directly in pgAdmin with a specified ID such as WHERE points.id = 41 returns the correct results:

Results

In the trigger, my first comment relates to the fact I wish to be able to update at the same time the line_id, dist and proj_geom fields of the table points each time an observation is inserted.

Question

How could I fix my code, at least to be able to get the new geometry?
I'm pretty sure I'm missing some tiny details (probably where I left some comments in the code where I have that actual feeling) but I can't figure them out and I've done too much hit'n'try now with the trigger function (none of them worked as good as the one here above).

Environment

Ubuntu 18.04
PostgreSQL: 10.12
PostGIS: 2.4

  • Impossible to tell without access to your database. Add a couple of RAISE NOTICE to your code for debugging. Perhaps your subquery does not return what you think it should. – Laurenz Albe May 4 at 8:16
  • See What are '$$' used for in PL/pgSQL. – CL. May 4 at 10:18
  • Use SELECT INTO for multiple values. – CL. May 4 at 10:22
2

In a BEFORE INSERT trigger, the new row has not yet been inserted into the table. So when the query searches for points.id with the new value, it will not find it.

And there is no need to read the points table because you want to access only the new row, and all those values are available in NEW:

      SELECT
         NEW.id AS point_id,
         schema.lines.id AS line_id,
         schema.lines.geom <-> NEW.geom AS dist,
         ST_ClosestPoint(schema.lines.geom, NEW.geom) AS proj_geom
      FROM schema.line
      ORDER BY dist
      LIMIT 1

You need to use a BEFORE trigger to be able to modify the row before it is written into the table.

For a nearest-neighbour search, better use the <-> operator, which is able to use a geometric index on lines.geom.

| improve this answer | |
  • Execution time doesn't really change between ST_Distance and <-> but it's working as wel, so I'll keep it, good tip, tanks! – s.k May 9 at 18:46
  • 1
    You will see a difference if the lines do not fit into memory and/or if the DB server is loaded by other clients. – CL. May 10 at 6:12

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.