[postgis-users] match line with road segment

Obe, Regina robe.dnd at cityofboston.gov
Thu Jul 17 08:16:20 PDT 2008

Now you do your make line.  But you'll have to take into consideration what Paolo Rizzi said afterward.  Not sure the easiest way to do that.
So basically your process should look something like the below where trip_id is some fied that uniquely identifies each trip.  The below snaps each point to closest road segment and then makes a line for each trip.  It assumes the pt.gid is in the order of time travel.  If you have a datetime field for each point, use that instead of the pt.gid.
 SELECT trip_id, ST_MakeLine(snapped_point) As trip_route

 ST_Line_Locate_Point(ln_geom, pt_geom)
 ) As snapped_point,

 ln.the_geom AS ln_geom,
 pt.the_geom AS pt_geom,
 ln.id AS ln_id,
 pt.gid AS pt_id,pt.trip_id
gps_points pt INNER JOIN
road_table ln
ST_DWithin(pt.the_geom, ln.the_geom, 10.0)
pt.gid,ST_Distance(ln.the_geom, pt.the_geom)
) AS subquery ORDER BY trip_id, pt_id) As snapped_points
GROUP BY travel_id;
Hope that helps,


From: postgis-users-bounces at postgis.refractions.net on behalf of searchelite
Sent: Thu 7/17/2008 10:23 AM
To: postgis-users at postgis.refractions.net
Subject: RE: [postgis-users] match line with road segment

Paragon Corporation-2 wrote:
> You may be better off snapping the points to the line first before you do
> a
> make line.  Take a look at Paul's blog entry. 
> http://blog.cleverelephant.ca/2008/04/snapping-points-in-postgis.html
> Sounds similar to what you are trying to do.
> Hope that helps,
> Regina

Thanks for the reply...Yes, I already did that (snapping the point to the
line)..so, is there any idea what should i do next?
View this message in context: http://www.nabble.com/match-line-with-road-segment-tp18479996p18509643.html
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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080717/1e5f8962/attachment.html>

More information about the postgis-users mailing list