[postgis-users] match line with road segment
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 (
> SELECT DISTINCT ON (pt.id)
> ln.id AS ln_id,
> pt.id AS pt_id,
> ST_Line_Locate_Point(ln.the_geom, pt.the_geom)
> ) As snapped_point, pt.gps_time
> point_table pt INNER JOIN
> line_table ln
> 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,
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> 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
>> 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
>> 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,
>> The only problem with the above is that it will splice your trip into
>> various road segments making it up, but then you loose the time path
>> 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
>> 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
>> snap points.
>> Hope that helps,
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?
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