More

Snapping points to lines in QGIS/PostGIS?

Snapping points to lines in QGIS/PostGIS?


I'm attempting to snap approximately 900 points to a line table.

I've tried recreating what I have found from a couple of PostGIS answers but not having much luck. To test, I've created two simplified tables; one for points (ptest) and one for the lines (ltest). The test tables only consist of an ID column.

CREATE TABLE line_attribute_point AS SELECT distinct on (ltest.id) ltest.*, ST_ClosestPoint(ltest.geom, ptest.geom) as snapped_point FROM ltest,ptest ORDER BY ST_Distance(ltest.geom, ptest.geom);

The main error seems to be the ORDER BY.

Any ideas?


You can not use a function in the order by clause, because you are tempting to sort a table with a column that not exists. Try this

CREATE TABLE line_attribute_point AS SELECT distinct on (ltest.id) ltest.*, ST_Distance(ltest.geom, ptest.geom) as distance, ST_ClosestPoint(ltest.geom, ptest.geom) as snapped_point FROM ltest,ptest ORDER BY distance;

But you don't have what you want with this query. (but it works)

I suggest something like this (not tested):

--create a table with id and distance CREATE TABLE line_attribute_point AS SELECT ltest.id as lid, ptest.id as pid, ST_Distance(ltest.geom, ptest.geom)::double precision as distance FROM ltest,ptest ORDER BY distance; --because the same point could be near to one or more lines take only the point whit the closest line CREATE TABLE line_attribute_point_min AS SELECT pid, min(distance) as min_distance FROM line_attribute_point GROUP BY pid; --re-assign the line id to point id ALTER TABLE line_attribute_point_min ADD COLUMN lid integer; UPDATE line_attribute_point_min as lap_min SET lid=lap.lid FROM line_attribute_point as lap WHERE lap_min.pid=lap.pid AND lap_min.min_distance=lap.distance; --create the geometry column and after that you can use this table for update the geometry of ptest and have the point snapped to lines ALTER TABLE line_attribute_point_min ADD COLUMN geom geometry(Point, SRID); UPDATE line_attribute_point_min as lap_min SET geom=ST_ClosestPoint(ltest.geom, ptest.geom) FROM ltest, ptest WHERE ltest.id=lap_min.lid AND ptest.id=lap_min.pid;

Does that select do what you want?

SELECT ST_ClosestPoint(close_line.geom, ptest.geom) FROM ( SELECT ltest.geom AS geom FROM ltest, ptest ORDER BY ST_Distance(ltest.geom, ptest.geom) LIMIT 1 ) close_line, ptest ;

Watch the video: Spatial #Joins with Multi-Table. #PostGIS #PostgreSQL #QGIS. Urdu. Hindi. Eng. #17