[postgis-users] match line with road segment
robe.dnd at cityofboston.gov
Wed Jul 23 09:26:40 PDT 2008
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 DISTINCT ON (pt.id)
ln.id AS ln_id,
pt.id AS pt_id,
) As snapped_point, pt.gps_time
point_table pt INNER JOIN
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,
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,
thanks for the idea regina, it's almost what i want to..it's just that
don't have a connected route..my purpose is to match the route line with
road segment. From the illustration that i gave, i have to connect the
point between the intersection with the intersection so i have a route
with a road. I have the end_point and start_point in every road segment.
idea how to do that?
Thank you, sorry for the rough explanation, i hope you can understand my
View this message in context:
Sent from the PostGIS - User mailing list archive at Nabble.com.
postgis-users mailing list
postgis-users at postgis.refractions.net
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.
More information about the postgis-users