How to snap line string start & end vertex to the nearest point?

Question

I have a point layer and a line layer. The points are road junctions, collected using DGPS, while the lines are the connecting roads, collected through Handheld GPS in tracking mode. As a result, the lines are not actually connected to the points.

Now I want to:

1. Remove all intermediate nodes from each line string, to make it a straight line.

2. Snap the start & end vertex of lines to the nearest points.

I am using PostGIS 2.0.This by far what I have done:

UPDATE line
SET geom = ST_Simplify(geom, 1000);

Q. Is there any other better way to accomplish it?(Since I am using an absurd tolerance)

UPDATE line
SET geom = ST_AddPoint(
(SELECT geom FROM line WHERE id = 1),
(SELECT p.geom FROM point AS p, line AS l
    ORDER BY ST_Distance(p.geom,(SELECT ST_StartPoint(l.geom) FROM lt WHERE l.id=1)) LIMIT 1),
0)
WHERE id=1;

This will extend the line(with id=1) to the nearest point(point added at the beginning of the line).

Q. The above looks a bit complicated, is there any other efficient method/function available?

Answer 1

Since you don't need most of the data from the green lines, you could take a very simple (simplistic?) approach and make a a point file consisting of the start and end nodes of the green lines, attributed with a line ID. Then use the ST_Snap function to snap the new points to the existing intersections (red points). Then use the ST_MakeLine function to convert your snapped start/end points into your blue lines.