Just want to again say thanks for all the help on this.<br><div><br></div><div>After projecting from <span class="Apple-style-span" style="font-family: arial, sans-serif; font-size: 13px; border-collapse: collapse; ">4269 </span>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.</div>
<div><br></div><div>Now my next two problems (which I need to research) are:</div><div><ol><li>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.</li>
<li>Connecting some routes that are inconsistent or noncontiguous. Examples I've found in the Interstate system:</li><ul><li>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.</li>
<li>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).</li>
</ul></ol><div>Aren</div></div><div><div><br><div class="gmail_quote">On Sun, Dec 26, 2010 at 6:35 PM, Aren Cambre <span dir="ltr"><<a href="mailto:aren@arencambre.com">aren@arencambre.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
I am trying to determine mile markers along Texas highways. My starting point is the ShapeFile <span style="font-family:Tahoma, Geneva, Arial, sans-serif;font-size:13px;border-collapse:collapse;line-height:17px">TxDOT Roadways 2010 at <a href="http://www.tnris.state.tx.us/datadownload/download.jsp" target="_blank">http://www.tnris.state.tx.us/datadownload/download.jsp</a>. I've used shp2pgsql to get it into a PostGIS 1.52-enabled Postgres 9.01 database.</span><div>
<span style="font-family:Tahoma, Geneva, Arial, sans-serif;font-size:13px;border-collapse:collapse;line-height:17px"><br></span></div><div><font face="Tahoma, Geneva, Arial, sans-serif"><span style="border-collapse:collapse;line-height:17px">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 <b>ST_Line_Interpolate_Point</b>. However, predictably, the more "longitudinal" a route, the more error it shows when I compare my calculated mile markers to what Google Maps shows.</span></font></div>
<div><font face="Tahoma, Geneva, Arial, sans-serif"><span style="border-collapse:collapse;line-height:17px"><br></span></font></div><div><font face="Tahoma, Geneva, Arial, sans-serif"><span style="border-collapse:collapse;line-height:17px">Again, this is because I was using a consistent ratio of degrees to miles, so any route E-W component introduces errors.</span></font></div>
<div><font face="Tahoma, Geneva, Arial, sans-serif"><span style="border-collapse:collapse;line-height:17px"><br></span></font></div><div><font face="Tahoma, Geneva, Arial, sans-serif"><span style="border-collapse:collapse;line-height:17px">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?</span></font></div>
<div><font face="Tahoma, Geneva, Arial, sans-serif"><span style="border-collapse:collapse;line-height:17px"><br></span></font></div><div><font face="Tahoma, Geneva, Arial, sans-serif"><span style="border-collapse:collapse;line-height:17px">I reviewed the functions available at </span></font><a href="http://postgis.refractions.net/documentation/manual-1.5/reference.html" target="_blank">http://postgis.refractions.net/documentation/manual-1.5/reference.html</a> and am not seeing anything clear.</div>
<div><br></div><div>In case it matters, the SHP's PRJ file says NAD83.</div><div><font face="Tahoma, Geneva, Arial, sans-serif"><span style="border-collapse:collapse;line-height:17px"><br>
</span></font></div><div><font face="Tahoma, Geneva, Arial, sans-serif"><span style="border-collapse:collapse;line-height:17px">Aren Cambre</span></font></div>
</blockquote></div><br></div></div>