RE: match line with road segment

Obe, Regina DND\MIS wrote:

Now you do your make line.  But you'll have to take into consideration what Paolo Rizzi said afterward.  Not sure the easiest way to do that.
 
So basically your process should look something like the below where trip_id is some fied that uniquely identifies each trip.  The below snaps each point to closest road segment and then makes a line for each trip.  It assumes the pt.gid is in the order of time travel.  If you have a datetime field for each point, use that instead of the pt.gid.
 
 SELECT trip_id, ST_MakeLine(snapped_point) As trip_route
(SELECT
pt_id,
ln_id,

ST_Line_Interpolate_Point(
 ln_geom,
 ST_Line_Locate_Point(ln_geom, pt_geom)
 ) As snapped_point,
travel_id

FROM
(
SELECT DISTINCT ON (pt.gid)
 ln.the_geom AS ln_geom,
 pt.the_geom AS pt_geom,
 ln.id AS ln_id,
 pt.gid AS pt_id,pt.trip_id
FROM
gps_points pt INNER JOIN
road_table ln
ON
ST_DWithin(pt.the_geom, ln.the_geom, 10.0)
ORDER BY
pt.gid,ST_Distance(ln.the_geom, pt.the_geom)
) AS subquery ORDER BY trip_id, pt_id) As snapped_points
GROUP BY travel_id;
 
Hope that helps,
Regina