I've done it this way, but now may be the time for me to check... I have often wondered if because the Geometry is stored in a TOAST table, this shouldn't impact on speed should it?
cheers Ben On 28/12/2010, at 5:57 PM, pcr...@pcreso.com wrote: > Hi Aren, > > In this sort of case I usually prefer to keep my source data as a reference, > as well as an indexed reprojected (working) version of the geometry. Instead > of doing it as you have done, and create a new table, I add a new geometry > column of the appropriate type & SRID to the original table, then populate it > using an update (& don't forget to index it): > > eg: > > select ST_AddGeometryColumn( > '','txdot_roadways','geom_nad',3081,'LINESTRING',2);" > update txdot_roadways set geom_nad=ST_Transform(the_geom, 3081); > > (& if your original geometry is a MULTILINESTRING, then use that type instead) > > This keeps the two geometries together in the same table, something that is > non-trivial in a traditional GIS, but just another column in spatially > enabled database. If you really don't need to keep the original column, you > can always drop it from the table after creating the 3081 version. > > Having a second table also works, but I figured I'd mention this alternative. > > Cheers, > > Brent Wood > > --- On Tue, 12/28/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 Discussion" <postgis-users@postgis.refractions.net> > Date: Tuesday, December 28, 2010, 6:50 PM > > 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: > INSERT INTO txdot_roadways_3081_transform > SELECT [all other fields go here], ST_Transform(the_geom, 3081) as the_geom > FROM txdot_roadways; > > 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. > > Thanks again to both of you for helping with this. It never occurred to me > how easy it can be to reproject GIS data. > > Aren > > On Mon, Dec 27, 2010 at 6:15 PM, Paul Ramsey <pram...@opengeo.org> wrote: > Right, use 4269, that's a good NAD83-geographic-coordinates number. > Import with shp2pgsql -s 4269 and go from there. > > P > > On Mon, Dec 27, 2010 at 3:54 PM, Aren Cambre <a...@arencambre.com> wrote: > > Brent and Paul, > > Thank you for your help! > > So here's my (new) dilemma--my PostGIS table doesn't appear to have a > > projection specified, and I am not clear how to get to one. > > I don't think it has a projection because this table's corresponding entry > > in the geometry_columns table has -1 for the srid column. > > This ShapeFile's PRJ file has this: > > 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]] > > I'm not seeing a clear match between this and any projection. > > Some Google searching suggests this may be 4326, but I'm not sure about > > this. And if I modify geometry_columns and import the PostGIS table into > > QGis, I get this QGis error: > > 1 cursor states lost. > > SQL: CLOSE qgisf0 > > Result: 7 (ERROR: current transaction is aborted, commands ignored until > > end of transaction block > > ) > > If I revert that field back to -1, the error goes away on next import. > > When I imported using shp2pgsql, I didn't use the -s switch. I presumed it > > would catch the projection automatically. > > I'm at a loss to know what to do next. I guess I need to figure out what the > > true SRID of this data is before I can do any re-projections? > > Aren > > > > On Mon, Dec 27, 2010 at 12:22 AM, Paul Ramsey <pram...@opengeo.org> wrote: > >> > >> You need to do your analysis in a projected coordinate system, not > >> geographics. > >> > >> CREATE TABLE my_new_texas_roads AS > >> SELECT ST_Transform(the_geom, 3081) as the_geom, other_attributes > >> FROM texas_roads; > >> > >> EPSG:3081 should be a good coordinate system for working with your Texas > >> data. > >> > >> http://spatialreference.org/ref/epsg/3081/ > >> > >> Not that the units are meters, so perform the appropriate linear > >> transformations when looking for mile markers. > >> > >> Paul > >> > >> On Sun, Dec 26, 2010 at 4: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 > >> > _______________________________________________ > >> > 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 > > > > > > _______________________________________________ > > 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 > > > -----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
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
