[Note] PostgreSQL – Snap points to nearest line

Hallo

If you have your data stored in PostGIS you can do it directly in the

database.

To get a new table with the snapped points and the line attribudes you

can run:

CREATE TABLE line_attribute_point AS

SELECT distinct on (linetable.id) linetable.*,

ST_ClosestPoint(linetable.geom, pointtable.geom) as snapped_point

FROM

linetable,pointtable

ORDER BY ST_Distance(linetable.geom, pointtable.geom);

Something like that.

But this will be slow if you have many points and polygons. Then you can

use ST_Dwithin to speed it up if you know how far away the line and

point can be that should be snapped. Say you don’t want to snap longer

than 100 meters then:

CREATE TABLE line_attribute_point AS

SELECT distinct on (linetable.id) linetable.*,

ST_ClosestPoint(linetable.geom, pointtable.geom) as snapped_point

FROM

linetable INNER JOIN pointtable on ST_Dwithin(linetable.geom,

pointtable.geom, 100)

ORDER BY ST_Distance(linetable.geom, pointtable.geom);

Something like that.

HTH

Nicklas

http://osgeo-org.1560.x6.nabble.com/Snap-points-to-nearest-line-td4126948.html

Hi Cameron

Two suggestions, but not an excact solution:

You can use the mmqgis plugin and select “Hub Distance”. With this you can transfer an attribute (or an ID and then use a join afterwards) to a pointlayer from the nearest line in a linelayer.

But be aware that “nearest” line will be based only on a “centre point” for the line and if your lines are very curved or of varying lengths you may not get the expected result.

You might use the “Vector-Geometry Tools-Extract Nodes” to convert your line layer to a point layer and use this point layer as the Hublayer instead of the line layer.

Anybody knows an excact method?