Snapping Points in PostGIS08 Apr 2008
Fun question on the #postgis IRC channel today, just hard enough to be interesting and just easy enough to not be overwhelming:
Given a table of points and a table of lines, snap all the points within 10 metres of the lines to the lines.
My first thought was “PostGIS doesn’t have that snapping function”, but it actually does, hidden in the linear-referencing functions:
OK, that returns a measure along the line, but I want a point! No problem, ST_Line_Interpolate_Point(line, measure) returns a point from a measure.
Great, so now all I need are, for each point within 10 metres of the lines, the nearest line. Yuck, finding the minimum. However, with the PostgreSQL
DISTINCT ON syntax and some ordering, it all pops out:
The sub-query finds all the points/line combinations that meet the 10 meter tolerance rule, and returns them in sorted order, by point id and distance. The outer query then strips off the first entry for each distinct point id and runs the LRS functions on it to derive the new snapped point.