[postgis-users] Input on upgrade to GIS database

Onyx omueller at i3.com
Fri Nov 18 09:39:29 PST 2005


Gregory, Dylan, Darren, or anyone else,
So, is the postgis_restore.pl script converting the geometry data to binary?
Or is this done somewhere else?  Regardless, is there a way to avoid the
conversion, for legacy code reasons?  I would like to keep the geometries as
ASCII.

- Onyx
---
Onyx Mueller
Software Engineer
i-cubed : information integration & imaging LLC
201 Linden Street : Third Floor
Fort Collins, CO  80524
970-482-4400 voice
970-482-4499 fax
http://www.i3.com
 
 

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of Darren Houston
> Sent: Thursday, November 17, 2005 3:33 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Input on upgrade to GIS database
> 
> Hi Onyx,
> 
> No fix necessary. PostGIS now stores spatial data as binary. 
> Try selecting that data out as text (select asewkt(the_geom)) 
> and compare it to your old data. Should match 100%.
> 
> Darren H.
> 
> On Thursday 17 November 2005 15:06, Onyx wrote:
> > Daniel Faivre, Jeremy Sears, Darren Houston, thank you for 
> your input.  
> > OK, so, with your help, I have been able to bring up _most_ of our 
> > data successfully in the new database.  But, I am having 
> problems with 
> > the data contained within the tables of type PostGIS geometry.
> >
> > Originally, the table contained something like:
> >
> > SRID=4326;MULTIPOLYGON(((-105.499999977881 40.5 0,-105.499999977881
> > 40.9999999185093 0,-105.000000006985 40.9999999185093 
> > 0,-105.000000006985
> > 40.5 0,-105.249999999884 40.5 0,-105.499999977881 40.5 0)))
> >
> >
> > Now, after the upgrade and xfer of data, the table contains:
> >
> > 
> >01060000A0E61000000100000001030000800100000006000000FA3FE8FFF
> F5F5AC0000
> >0000
> >0
> > 
> >004044400000000000000000FA3FE8FFFF5F5AC0040051FFFF7F444000000
> 0000000000
> >0068
> >0
> > 
> >070000405AC0040051FFFF7F444000000000000000000680070000405AC00
> 0000000004
> >0444
> >0
> > 
> >00000000000000001DE0FFFFFF4F5AC000000000004044400000000000000
> 000FA3FE8F
> >FFF5
> >F 5AC000000000004044400000000000000000
> >
> >
> > Any ideas why and how to fix?
> >
> >
> > - Onyx
> > ---
> > Onyx Mueller
> > Software Engineer
> > i-cubed : information integration & imaging LLC
> > 201 Linden Street : Third Floor
> > Fort Collins, CO  80524
> > 970-482-4400 voice
> > 970-482-4499 fax
> > http://www.i3.com
> >
> > > -----Original Message-----
> > > From: postgis-users-bounces at postgis.refractions.net
> > > [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of 
> > > Darren Houston
> > > Sent: Thursday, November 17, 2005 12:20 PM
> > > To: PostGIS Users Discussion
> > > Subject: Re: [postgis-users] Input on upgrade to GIS database
> > >
> > > Hello Onyx,
> > >
> > > If like me and you don't have custom functions, schema, etc.
> > > and only have a couple of tables with data, I found the 
> easiest way 
> > > to do an upgrade is to dump the tables, upgrade your 
> database, and 
> > > load the tables. I also dump and load the 
> geometry_columns table and 
> > > then run fix_geometry_columns(). I always use a new 
> spatial_ref_sys 
> > > table from the upgrade.
> > > VACUUM ANALYZE and you should be good to go. I keep the 
> old database 
> > > kicking around just in case I need to start it up again to grab 
> > > something I forgot. I hear postgis_restore.pl works, but 
> I haven't 
> > > used it.
> > >
> > > Darren H.
> > >
> > > On Thursday 17 November 2005 09:37, Sears, Jeremy wrote:
> > > > Onyx,
> > > >
> > > > I have recently preformed a simmilar task. In the name of 
> > > > experimentation I had tried a coupla methods to move 
> data from an 
> > > > older PostgreSQL(8.0.2) + PostGIS install to a new one
> > >
> > > (postgresql 8.1
> > >
> > > > and postgis 1.0.4). The older database dumped data into a
> > >
> > > sql loader
> > >
> > > > file. This was then loaded into the new database via 'psql -f 
> > > > datafile.sql'. This method seems to have worked although I
> > >
> > > did notice a couple of errors fly by as the data loaded. . .
> > >
> > > > Note that the new database is still in the testing phase and 
> > > > errors could exist that I havent noticed yet.. however 
> so far so good.
> > > >
> > > > Jeremy
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: postgis-users-bounces at postgis.refractions.net
> > > > 
> [mailto:postgis-users-bounces at postgis.refractions.net]On Behalf Of 
> > > > Onyx
> > > > Sent: November 17, 2005 11:01 AM
> > > > To: postgis-users at postgis.refractions.net
> > > > Subject: [postgis-users] Input on upgrade to GIS database
> > > >
> > > >
> > > >
> > > > PostGIS Friends,
> > > > I need some help/input on the proper approach to upgrading on 
> > > > GIS-driven database backend, and I am hoping that some 
> of you have 
> > > > been in similar situations.  We run a PostgreSQL database
> > >
> > > with Proj,
> > >
> > > > Geos, and (of course) PostGIS libraries.  I want to upgrade
> > >
> > > the following:
> > > > - PostgreSQL 7.4.6      --> 8.1.0
> > > > - Geos 2.1.0            --> 2.1.4
> > > > - PostGIS 0.9.0         --> 1.0.4
> > > >
> > > > What is the proper approach to get this done successfully,
> > >
> > > keeping our
> > >
> > > > existing data intact?  I know that it is not as simple as
> > >
> > > 1) dumping
> > >
> > > > the schema & data from the database to a file, 2) 
> moving/deleting 
> > > > PostgreSQL
> > > > 7.4.6 (and old libraries), 3) installing PostgreSQL 8.1.0
> > >
> > > (and the new
> > >
> > > > libraries), and 4) feeding the schema & data back in,
> > >
> > > although I wish
> > >
> > > > it were that easy.  I have read the section of the PostGIS
> > >
> > > manual on
> > >
> > > > upgrading, but I am worried that there are some other
> > >
> > > aspects I need
> > >
> > > > to know since I have other variables I am upgrading as well 
> > > > (PostgreSQL & Geos).  Has anyone been in my boat before?
> > > >
> > > > Any help would be greatly appreciated.  Thanks!
> > > >
> > > >
> > > > - Onyx
> > > > ---
> > > > Onyx Mueller
> > > > Software Engineer
> > > > i-cubed : information integration & imaging LLC
> > > > 201 Linden Street : Third Floor
> > > > Fort Collins, CO  80524
> > > > 970-482-4400 voice
> > > > 970-482-4499 fax
> > > >  <http://www.i3.com> http://www.i3.com
> > >
> > > _______________________________________________
> > > 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
> 





More information about the postgis-users mailing list