Thank you. Now my shp2pgsql conversion results in a PostGIS table with an SRID. I then ran this query to reproject the data into a new table:<div><b><font class="Apple-style-span" face="'courier new', monospace">INSERT INTO txdot_roadways_3081_transform</font></b></div>
<div><div><b><font class="Apple-style-span" face="'courier new', monospace">SELECT [all other fields go here], ST_Transform(the_geom, 3081) as the_geom</font></b></div><div><b><font class="Apple-style-span" face="'courier new', monospace">FROM txdot_roadways;</font></b></div>
<div><br></div><div>Loading into qgis, the map now looks like a correct projection for taking planar (?) measurements. Previously the state looked as if it was stretched horizontally, but I guess that's to be expected if longitudinal lines don't bend.</div>
<div><br></div><div>Thanks again to both of you for helping with this. It never occurred to me how easy it can be to reproject GIS data.</div><div><br></div><div>Aren<br><br><div class="gmail_quote">On Mon, Dec 27, 2010 at 6:15 PM, Paul Ramsey <span dir="ltr"><<a href="mailto:pramsey@opengeo.org">pramsey@opengeo.org</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">Right, use 4269, that's a good NAD83-geographic-coordinates number.<br>
Import with shp2pgsql -s 4269 and go from there.<br>
<font color="#888888"><br>
P<br>
</font><div><div></div><div class="h5"><br>
On Mon, Dec 27, 2010 at 3:54 PM, Aren Cambre <<a href="mailto:aren@arencambre.com">aren@arencambre.com</a>> wrote:<br>
> Brent and Paul,<br>
> Thank you for your help!<br>
> So here's my (new) dilemma--my PostGIS table doesn't appear to have a<br>
> projection specified, and I am not clear how to get to one.<br>
> I don't think it has a projection because this table's corresponding entry<br>
> in the geometry_columns table has -1 for the srid column.<br>
> This ShapeFile's PRJ file has this:<br>
> GEOGCS["GCS_North_American_1983",DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]<br>
> I'm not seeing a clear match between this and any projection.<br>
> Some Google searching suggests this may be 4326, but I'm not sure about<br>
> this. And if I modify geometry_columns and import the PostGIS table into<br>
> QGis, I get this QGis error:<br>
> 1 cursor states lost.<br>
> SQL: CLOSE qgisf0<br>
> Result: 7 (ERROR: current transaction is aborted, commands ignored until<br>
> end of transaction block<br>
> )<br>
> If I revert that field back to -1, the error goes away on next import.<br>
> When I imported using shp2pgsql, I didn't use the -s switch. I presumed it<br>
> would catch the projection automatically.<br>
> I'm at a loss to know what to do next. I guess I need to figure out what the<br>
> true SRID of this data is before I can do any re-projections?<br>
> Aren<br>
><br>
> On Mon, Dec 27, 2010 at 12:22 AM, Paul Ramsey <<a href="mailto:pramsey@opengeo.org">pramsey@opengeo.org</a>> wrote:<br>
>><br>
>> You need to do your analysis in a projected coordinate system, not<br>
>> geographics.<br>
>><br>
>> CREATE TABLE my_new_texas_roads AS<br>
>> SELECT ST_Transform(the_geom, 3081) as the_geom, other_attributes<br>
>> FROM texas_roads;<br>
>><br>
>> EPSG:3081 should be a good coordinate system for working with your Texas<br>
>> data.<br>
>><br>
>> <a href="http://spatialreference.org/ref/epsg/3081/" target="_blank">http://spatialreference.org/ref/epsg/3081/</a><br>
>><br>
>> Not that the units are meters, so perform the appropriate linear<br>
>> transformations when looking for mile markers.<br>
>><br>
>> Paul<br>
>><br>
>> On Sun, Dec 26, 2010 at 4:35 PM, Aren Cambre <<a href="mailto:aren@arencambre.com">aren@arencambre.com</a>> wrote:<br>
>> > I am trying to determine mile markers along Texas highways. My starting<br>
>> > point is the ShapeFile TxDOT Roadways 2010<br>
>> > 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<br>
>> > shp2pgsql to get it into a PostGIS 1.52-enabled Postgres 9.01 database.<br>
>> > I naively thought I could just figure out the number of miles per unit<br>
>> > of<br>
>> > latitude and then traverse each roadway, one mile at a time,<br>
>> > using ST_Line_Interpolate_Point. However, predictably, the more<br>
>> > "longitudinal" a route, the more error it shows when I compare my<br>
>> > calculated<br>
>> > mile markers to what Google Maps shows.<br>
>> > Again, this is because I was using a consistent ratio of degrees to<br>
>> > miles,<br>
>> > so any route E-W component introduces errors.<br>
>> > So here's the question--does PostGIS allow any way to traverse a route a<br>
>> > set<br>
>> > distance at a time? Specifically, is there a way I can traverse a route<br>
>> > a<br>
>> > mile at a time and then record the points at the end of each mile?<br>
>> > I reviewed the functions available<br>
>> ><br>
>> > at <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<br>
>> > am not seeing anything clear.<br>
>> > In case it matters, the SHP's PRJ file says NAD83.<br>
>> > Aren Cambre<br>
>> > _______________________________________________<br>
>> > postgis-users mailing list<br>
>> > <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
>> > <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
>> ><br>
>> ><br>
>> _______________________________________________<br>
>> postgis-users mailing list<br>
>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
><br>
><br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
><br>
><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br></div></div>