[postgis-users] match line with road segment

[searchelite]

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
> FROM 
> (SELECT *  
> FROM (
> SELECT DISTINCT ON (pt.id)
> 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
> FROM
> point_table pt INNER JOIN
> line_table ln
> ON
> ST_DWithin(pt.the_geom, ln.the_geom, 10.0)
> ORDER BY
> 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.