[postgis-users] Traverse set distance along a multiline?

Tue Dec 28 07:48:22 PST 2010

Thank you. Another method I've used is a hybrid model where I create a
separate table but have a foreign key going back to the original table, then
I make a view to combine both tables. I preserve the original table and have
my separate table that I can throw away if I need.

Aren

On Tue, Dec 28, 2010 at 12:57 AM, <pcreso at 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 <aren at arencambre.com>* wrote:
>
>
> From: Aren Cambre <aren at arencambre.com>
> Subject: Re: [postgis-users] Traverse set distance along a multiline?
> To: "PostGIS Users Discussion" <postgis-users at 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 <pramsey at opengeo.org<http://mc/compose?to=pramsey@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 <aren at arencambre.com<http://mc/compose?to=aren@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 <pramsey at opengeo.org<http://mc/compose?to=pramsey@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 <aren at arencambre.com<http://mc/compose?to=aren@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 at postgis.refractions.net<http://mc/compose?to=postgis-users@postgis.refractions.net>
> >> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >> >
> >> >
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at postgis.refractions.net<http://mc/compose?to=postgis-users@postgis.refractions.net>
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net<http://mc/compose?to=postgis-users@postgis.refractions.net>
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net<http://mc/compose?to=postgis-users@postgis.refractions.net>
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> -----Inline Attachment Follows-----
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net<http://mc/compose?to=postgis-users@postgis.refractions.net>
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20101228/a32e219d/attachment.html>