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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of searchelite Sent: Wednesday, July 23, 2008 3:39 AM To: postgis-users@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@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. _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
