[postgis-users] Input on upgrade to GIS database

[Darren Houston]

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:
>
> 01060000A0E61000000100000001030000800100000006000000FA3FE8FFFF5F5AC00000000
>0
> 004044400000000000000000FA3FE8FFFF5F5AC0040051FFFF7F44400000000000000000068
>0
> 070000405AC0040051FFFF7F444000000000000000000680070000405AC0000000000040444
>0
> 00000000000000001DE0FFFFFF4F5AC000000000004044400000000000000000FA3FE8FFFF5
>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