Snapping points and returning features in PostGIS?

Question

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

Answer 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.

• Luffydude: 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