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?

  • What do you want to do? For every point, find a closest line and snap point onto it? – dmitry.v.kiselev Aug 12 '15 at 13:05
  • yes, find closest segment (not vertex) and snap to it. All points are within about 50m – Graham04 Aug 12 '15 at 13:49
up vote 2 down vote accepted

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;
  • Thanks for the quick replies. Error with the "AS" in SET geom=ST_ClosestPoint(ltest.geom, ptest.geom) AS geom – Graham04 Aug 12 '15 at 13:53
  • sorry, query updated. – Sergio Aug 12 '15 at 14:03
  • Sorry seems to have an issue with missing FROM-clause entry for table "lapmin" in WHERE lapmin.pid=lap.pid AND lapmin.min_distance=lap.dist... – Graham04 Aug 12 '15 at 14:13
  • its lap_min not lapmin, updated – Sergio Aug 12 '15 at 14:17
  • thanks, getting there I think. It seems to have an issue with the SRID - I removed it and it ran without error, however created an empty table? I manually set SRID to 27700 when adding to the QGIS canvas. – Graham04 Aug 12 '15 at 14:24

Does that select do what you want?

select 
    ST_ClosestPoint(close_line.geom, ptest.geom)
from 
    (
     select 
         ltest.geom geom
         from ltest
         order by 
             ST_Distance(ltest.geom, ptest.geom)
         limit 1
    ) close_line,
    ptest
  • it's having issue with the lines table - is this what you meant to select from? – Graham04 Aug 12 '15 at 13:50
  • fixed it, select from ltest in nested query, as close_line – dmitry.v.kiselev Aug 12 '15 at 14:02
  • I changed FROM ltest to FROM ltest, ptest as was getting an error on the ptest.geom in ORDER BY. I presume I need to create the close_line table to begin with? – Graham04 Aug 12 '15 at 14:32

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.