Hi Aren, for start & end points you can use ST_StartPoint & ST_EndPoint, should be faster than 0 & 100% values.
It may also be useful to optimise your working data, by pre ordering the points so that the startpoint is always the southern or western one, so your algorithm does not need to check this for every linestring, eg: update table set geom_nad = ST_Reverse(geom_nad) where ST_StartPoint(geom_nad) |>> ST_EndPoint(geom_nad); see: http://www.postgis.org/documentation/manual-1.5/reference.html#Operators --- On Wed, 12/29/10, Aren Cambre <a...@arencambre.com> wrote: From: Aren Cambre <a...@arencambre.com> Subject: Re: [postgis-users] Traverse set distance along a multiline? To: postgis-users@postgis.refractions.net Date: Wednesday, December 29, 2010, 5:00 AM Just want to again say thanks for all the help on this. After projecting from 4269 to 3081, I reanalyzed everything and checked my points sample again. All my points at major intersections and state borders were <= 1 mile off! That's good enough for me. Now my next two problems (which I need to research) are:Forcing my algorithm to start mile 0 from a route's the southern or western extreme. I am guessing I would get the points at 0% and 100% of a route and the compare them. Whichever "wins" then becomes the starting point for my traversal. At least for most mainline IH and US routes, the algorithm to determine the "winner" may takes the route number as input since even = W-E and odd = N-S. Connecting some routes that are inconsistent or noncontiguous. Examples I've found in the Interstate system:Where I-35W and I-35E split south of Dallas/Ft. Worth, I-35W starts over at mile marker 0, but I-35E maintains mileposts as if it is the contiguous I-35 route. So I need to connect I-35 south of Dallas, I-35E, and I-35 north Dallas as one route. I think the PostGIS functions exist to combine these three routes into one. I-10 for some reason doesn't follow I-35 near downtown San Antonio, so I have two I-10s separated by that short I-35 multiplex gap. Mile markers on a contiguous route would count up to well over 800 by the Sabine River (eastern Texas border). Aren On Sun, Dec 26, 2010 at 6:35 PM, Aren Cambre <a...@arencambre.com> wrote: I am trying to determine mile markers along Texas highways. My starting point is the ShapeFile TxDOT Roadways 2010 at http://www.tnris.state.tx.us/datadownload/download.jsp. I've used shp2pgsql to get it into a PostGIS 1.52-enabled Postgres 9.01 database. I naively thought I could just figure out the number of miles per unit of latitude and then traverse each roadway, one mile at a time, using ST_Line_Interpolate_Point. However, predictably, the more "longitudinal" a route, the more error it shows when I compare my calculated mile markers to what Google Maps shows. Again, this is because I was using a consistent ratio of degrees to miles, so any route E-W component introduces errors. So here's the question--does PostGIS allow any way to traverse a route a set distance at a time? Specifically, is there a way I can traverse a route a mile at a time and then record the points at the end of each mile? I reviewed the functions available at http://postgis.refractions.net/documentation/manual-1.5/reference.html and am not seeing anything clear. In case it matters, the SHP's PRJ file says NAD83. Aren Cambre -----Inline Attachment Follows----- _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
