[postgis-users] Traverse set distance along a multiline becoming multiple storage columns?

Tue Dec 28 11:20:49 PST 2010

Hi Ben,

I'm interested in what an authoritative answer is to this :-)

I know that several years (& Postgis/Postgres versions) ago running a query on this setup, then dropping the original column & vacuuming & re-running the query didn't make a significant difference, coz I tried it, but this was with only around 66,000 records. I should try it with a couple of hundred million with v9/1.52 & see what happens. 

It obviously does impact on filesystem volume & dumps/backups, which could be an issue for some.

Maybe something to play with while at sea in January. if I have the time.

Cheers,

 Brent

--- On Tue, 12/28/10, Ben Madin <lists at remoteinformation.com.au> wrote:

From: Ben Madin <lists at remoteinformation.com.au>
Subject: Re: [postgis-users] Traverse set distance along a multiline becoming multiple storage columns?
To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
Cc: "Brent Wood" <pcreso at pcreso.com>
Date: Tuesday, December 28, 2010, 11:29 PM

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, 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_geomFROM 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> 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> 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> 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> 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://postgis.refractions.net/mailman/listinfo/postgis-users

>> >

>> >

>> _______________________________________________

>> postgis-users mailing list

>> postgis-users at postgis.refractions.net

>> http://postgis.refractions.net/mailman/listinfo/postgis-users

>

>

> _______________________________________________

> postgis-users mailing list

> postgis-users at postgis.refractions.net

> http://postgis.refractions.net/mailman/listinfo/postgis-users

>

>

_______________________________________________

postgis-users mailing list

postgis-users at 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://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at 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/73924385/attachment.html>