Snapping points to lines in QGIS/PostGIS?

Question

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?

Answer 1

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;

Answer 2

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
;