[postgis-users] match line with road segment

searchelite searchelite at gmail.com
Thu Jul 17 09:12:18 PDT 2008

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
> pt_id,
> ln_id,
> ST_Line_Interpolate_Point(
>  ln_geom,
>  ST_Line_Locate_Point(ln_geom, pt_geom)
>  ) As snapped_point,
> travel_id
> (
>  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
> gps_points pt INNER JOIN
> road_table ln
> ON
> ST_DWithin(pt.the_geom, ln.the_geom, 10.0)
> 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

thanks a lot for the reply...i'll try the code you give me first thing

View this message in context: http://www.nabble.com/match-line-with-road-segment-tp18479996p18512194.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

More information about the postgis-users mailing list