[postgis-users] match line with road segment

[Obe, Regina]

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 
> 
> 

thanks for the idea regina, it's almost what i want to..it's just that
now i
don't have a connected route..my purpose is to match the route line with
a
road segment. From the illustration that i gave, i have to connect the
two
point between the intersection with the intersection so i have a route
match
with a road. I have the end_point and start_point in every road segment.
any
idea how to do that? 

Thank you, sorry for the rough explanation, i hope you can understand my
problem
-- 
View this message in context:
http://www.nabble.com/match-line-with-road-segment-tp18479996p18605499.h
tml
Sent from the PostGIS - User mailing list archive at Nabble.com.

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.