0

I'm creating a point layer and I already have a line layer

There's a column in my point layer called line_id. I need to return automatically the id from the line to which the point is snapped to

I have written the following code, it works but ONLY when a point is snapped to a vertex in the line feature, not when the point is snapped to the rest of the segment

CREATE TRIGGER line_id_update
BEFORE INSERT OR UPDATE
ON pointlayer
FOR EACH ROW
EXECUTE PROCEDURE line_id_update()

CREATE OR REPLACE FUNCTION line_id_update()
RETURNS trigger AS
$BODY$
BEGIN
new.line_id := (select line_id from line_layer b where st_intersects(b.geom, new.geom) limit 1 ) ;  
    RETURN new; 
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

On QGIS I'm snapping to both vertex and segment, how to make it so that it will always return the line when it's connected to the segment

1 Answer 1

1

The problem is using ST_Contains. ST_Contains ist only TRUE when a point is exactly on a line between two vertices. In general, this is unlikely to happen - depending on tolerances, used projection and your data.

You could use ST_Distance to find lines which have a defined maximum distance from your points.

1
  • I'm using update point_layer a set line_id = (select b.line_id from line_layer b where st_distance(b.geom, a.geom) < 10 order by st_distance(b.geom, a.geom) limit 1) and it's still not returning the id when the point is on top of the line
    – Luffydude
    Feb 19, 2018 at 15:15

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.