[postgis-users] match line with road segment

searchelite searchelite at gmail.com
Thu Jul 24 04:44:19 PDT 2008

Obe, Regina     DND\MIS wrote:
> Does this work for you?- main issue I see is that it wouldn't work if
> your travel involved crossing segments you already crossed.  For that
> its even trickier and would involve probably 2 correctlate sub selects.
> Basic below is 
> Do the snap points to line segment routine to get the points matched
> with a line, 
> order the points by trip, road segment and time
> and then make line of the points along that line segment 
> - getting the max and min gps time to denote the start of time of
> segment and end time of segment.
> SELECT ln_id, MIN(gps_time) As start_time, MAX(gps_time) As end_time
> ST_MakeLine(snapped_point) As road_segment
> (SELECT *  
> FROM (
> pt.trip_id
>  ln.id AS ln_id,
>  pt.id AS pt_id, 
> ST_Line_Interpolate_Point(
>  ln.the_geom,
>  ST_Line_Locate_Point(ln.the_geom, pt.the_geom)
>  ) As snapped_point, pt.gps_time
> point_table pt INNER JOIN
> line_table ln
> ON
> ST_DWithin(pt.the_geom, ln.the_geom, 10.0)
> pt.id,ST_Distance(ln.the_geom, pt.the_geom)) As newpoints
> ORDER BY trip_id, ln_id, gps_time) As ordered_points
> GROUP BY trip_id, ln_id;
> Hope that helps,
> Regina
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> searchelite
> Sent: Wednesday, July 23, 2008 3:39 AM
> To: postgis-users at postgis.refractions.net
> Subject: RE: [postgis-users] match line with road segment
> Paragon Corporation-2 wrote:
>> Are you just looking to get back the portions of the line segments
> with
>> the
>> line segment id of the line segments that intersect your road.
>> If so, then now that you have your single line, you can splice it with
> the
>> road segments by doing
>> SELECT r.gid As road_gid, t.tripid, ST_Intersection(r.the_geom,
>> triproute.the_geom) As road_segment
>>  FROM roads r INNER JOIN triproute t ON ST_Intersects(r.the_geom,
>> triproute.the_geom)
>> The only problem with the above is that it will splice your trip into
> the
>> various road segments making it up, but then you loose the time path
> that
>> make line gave you.  
>> It may not matter depending on what you are doing though.
>> If you want to still maintain a sense of time and splice such that you
>> have
>> something like
>> Timestart, trip_id, trip_road_segment, road_gid
>> Then that's a tad bit trickier and requires you do this after you do
> the
>> snap points.
>> Hope that helps,
>> Regina 

I've tried your aprroach but it's not really working, i still got the route
which doesnt match with the road segment. Btw, i've managed to do it with
different approach. I've made a table that consists the node of the road
segment, after that i create ST_makeline(snapped_point, node). I got the
route match with the road segment :-D but i still have to perfect it..any
suggestion about this? 

thank you

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

More information about the postgis-users mailing list