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