You can use the QGIS plugin LRS for that. It accepts points not lying on the line, so you avoid the first step (snapping points to lines).
This is the workflow:
Based on both a line (routes) and a point (for calibration) layer, go to the Calibration tab.
- Select the line (pipeline) layer with its route field (a field that identifies routes, it's very useful if your line layer has several routes).
- Select the calibration point layer with its corresponding route (point route value should match with the line route value) and measure fields. As you can see, you would need a calibration point layer with at least two points, which could be located at the start and the end of the route, with values of 0 and 10000 in case that the route has 10km.
- Click on Ok to interpolate M values along the routes.
Go to the Measures tab.
- Select your valves layer.
- Fill the output fields (They'll be applied to a new layer, don't worry).
- Click on Ok to get a new (memory) layer which will contain the M value (chainage) of each valve.
Don't forget to save the layer you obtain from 2., since it's a memory (temporal) layer.
In the following screenshot you can see a sample pipeline, a couple of calibration points (orange triangles), and a couple of valves not lying on the pipeline (green points) with their calculated chainage (M value).
You can even verify the chainage of the valves by using the Locate tab. Just select your route and enter a known measure (e.g., 5603). You should see a yellow mark on the line, representing the projection of the valve on the line.
I use QGIS v.2.6.1. You can find documentation of LRS plugin here.
ST_LineLocatePoint( geometry, ST_EndPoint( geometry))
don't have much sense, because is searching for the closest point of a line to the endpoint of the same line, and will always return 1
.
And ST_LineInterpolatePoint( geometry, 1)
will always return the endpoint.
Since you want to snap geometry vertices, use ST_Snap instead. Just find the distance threshold to search a vertex to snap.
In this example, I am snapping the geometries of the lines table to the vertices of the geometries in the smoothed_lines table, that are at most to 0.5
meters away.
Both tables have an id column, and a geometry column with the geometry( LINESTRING, 54004)
type. I am casting the ST_Snap()
output to the same type.
The inner join is returning only the candidate pairs, and from that pairs ST_Snap()
is returning all lines geometries, modified or not.
DROP TABLE IF EXISTS snapped_lines;
CREATE TABLE IF NOT EXISTS snapped_lines AS (
SELECT
l.id AS id_l,
s.id AS id_s,
ST_Snap(
l.geometry,
s.geometry,
0.5
)::geometry( LineString, 54004) AS geometry
FROM
lines AS l
INNER JOIN
smoothed_lines AS s
ON
ST_DWithin( l.geometry, s.geometry, 0.5)
);
UPDATE:
If you need to snap lines within the same layer, you can perform the inner join to quickly exclude the pairs of lines that are far from each other, snap between each pair of selected lines, but filter the results for those lines that are more than zero apart from their pair (to avoid the lines that are generated by snapping to themselves or their adjacents).
One criterion to choose which lines must be snapped to which other, is the distance (<= 0.5) between them, adjust the value of 0.5 (reference system of the geometry column units) to the one that fits your needs.
When the lines from and to snapping, belong to the same layer, the same criterion that is applied to snapping line 1 to line 2, is applied to snapping line 2 to line 1. So let's add one more criterion than be deterministic to decide which line should snap to the other, for example, that the shortest line must snaps to the longest one.
DROP TABLE IF EXISTS snapped_lines;
CREATE TABLE IF NOT EXISTS snapped_lines AS (
SELECT
l.id AS id_l,
s.id AS id_s,
ST_Snap(
l.geometry,
s.geometry,
0.5
)::geometry( LineString, 54004) AS geometry
FROM
smoothed_lines AS l
INNER JOIN
smoothed_lines AS s
ON
ST_DWithin( l.geometry, s.geometry, 0.5)
WHERE
ST_Distance( l.geometry, s.geometry) > 0
AND
ST_Length( l.geometry) < ST_Length( s.geometry)
);
Best Answer
To permanently update the point geometries on the database level, run
in either the DB Manager in QGIS or pgAdmin (or the client of your choice).
This will find the closest line to each point and projects the closest point on that line to the given point as the new geometry, effectively 'snapping' them to the closest line.
(Note: in your picture, the point at the lower left will be snapped to the closest line, so to the one it kind of sits on already; if that is undesireable, you can specify an attribute of the line table to match any of the point's to only snap to those lines, see
-- WHERE ...
above; uncomment that line if you need or delete it).This will make use of the spatial index on both
<geometry_column>
.After the update, I suggest to reindex the table and run
VACUUM ANALYZE <point_table>
to update the table statistics, as part of standard maintenance.