Talk:Live GIS Disc Testing

darkblue_B: hi MartinSpott
darkblue_B: we were working on the OSGeo liveDVD last night
darkblue_B: I moved over a PostGIS database
darkblue_B: one of the others asked if I could write up a note on "How To"
MartinSpot: Hi darkblue_B, "ho to move a PosiGIS DB" ?
darkblue_B: its not that much.. but, since the database was created from Templte, spatial_ref_sys and geometry_colums already existed
darkblue_B: yes
darkblue_B: there were about hmm 15 tables?
darkblue_B: in Postgres its work to do anyhting to them.. like change the owner..
darkblue_B: I'm sure you have ways you have developed to do such things
darkblue_B: your database is so well maintained!
MartinSpot: Really ?
darkblue_B: you and the others I should say
darkblue_B: ;-)
darkblue_B: only you know the bad parts
darkblue_B: to me, it looks great
MartinSpot: Well, I'm casting everything into scripts, thus I have a pretty good overview about what's actually done on the DB.
darkblue_B: right
MartinSpot: Actually everything started by having one table (plus two little helpers) that contain everything I need to build the DB structure from
MartinSpot: ....  plus a couple of cascaded shell scripts to generate the respective SQL maintenance scripts.
MartinSpot: Now, is there anything missing for your HowTo ?
darkblue_B: Its not written@
darkblue_B: !!
darkblue_B: I have 'real work' too 
MartinSpot: Ah, sure
darkblue_B: well we made a template_postgis
darkblue_B: with spatial_ref_sys populated
darkblue_B: then for the 15 tables, and 4 views.. I listed them in psql
darkblue_B: removed the seqeunce names
darkblue_B: used regular expressions to generate a list of the tables (and views)
darkblue_B: created a change owner script
darkblue_B: created a pg_dump -t name1 -t name2... db --inserts line
darkblue_B: including geometry_columns but not including spatial ref sys
MartinSpot: Maybe adding an index or two is usually a good idea ....
darkblue_B: then edited out the create table statment by hand
darkblue_B: and saved it
MartinSpot: Ah good
darkblue_B: hmm indexes
darkblue_B: didnt think of that.. yes
darkblue_B: fortunately all the commands worked for the views as if they were tables
MartinSpot: I've so far been pretty happy with creating a primary key on the 'ogc_fid' column (or however you name it)
darkblue_B: I have done a similar thing for 100+ tables in a database once..
darkblue_B: I didnt do anything special with the key
MartinSpot: plus a GIST index on the geometry column .....  clustered onto the table
darkblue_B: yes, indexes.. I didnt do that
darkblue_B: this is for the FOSS4G live DVD
MartinSpot: I'm not entirely certain which one it was, but one of the tools I use requires a primary key on every table
darkblue_B: quite a few of the project will read it as a data source
darkblue_B: well, PgAdmin editing, for one
darkblue_B: in the window
MartinSpot: Either 'ogr2ogr' or 'mapserver' or 'pgsql2shp' or ....  I don't know by now
darkblue_B: ah ok
MartinSpot: Maybe as a sanity check put a constraint or two onto every table (enforce_geotype_wkb_geometry, enforce_srid_wkb_geometry)
darkblue_B: yes.. mleslie supplied the original
darkblue_B: so there were constraints and such already
MartinSpot: Fine
darkblue_B: perhaps I will just add this chat to the wiki and call it done
MartinSpot: ....  and don't forget to maintain the geometry_columns properly. 4 of 5 strange faults I usually see are due to typos or mismatches in the geometry_columns  :-)
MartinSpot: (typically my fault ....)
darkblue_B: yes - geometry_columns seems to be in good order.. all in the same projection
darkblue_B: EPSG:2207 if I recall
darkblue_B: in our US State of Oregon
darkblue_B: 2270