[postgis-users] Desperately seeking simple solution

[Wood Brent]

--- Gerry Creager N5JXS <gerry.creager at tamu.edu> wrote:
> I've got a postgres database that incorporates point data.  One table 
> holds lat/lon and other site (static) info, while another table houses 
> all the time-dependent data.  We're talking surface meteorological data 
> for the most part.
> 
> I'm trying to transition a big chunk to PostGIS and was wondering if 
> there's a relatively easy way to do it.  I don't want to take the site 
> down for several days, so the transition will have to occur off-line, 
> and then I'll do a swap.

I don't know of an elegant way, but when I did a similar exercise I dumped the
table defintion & edited it to make an sql to create the new table using
PostGIS spatial data column instead of the Postgres one.

Then extracted the data from the table (copy to file) & wrote a script to
reformat the file to have the geometry data re-formatted into the PostGIS text
import format.

I also wrapped each line into am insert sql & wrapped the whole lot in a
begin/commit transaction as I wasn't sure at that stage how copy would work
into a PostGIS table. I now believe copy works fine so this isn't necessary.

Note that I rebuilt the non-unique indices after the import, it's faster that
way.

This was simple enough to implement & did what I needed.

Cheers,

  Brent Wood