Wildintellect: don't forget to preview

2009-09-22T00:18:14Z

don't forget to preview

Darkblueb: New page: 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...

2009-09-19T22:52:11Z

New page: 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...

New page

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

@@ Line 1: / Line 1: @@
-darkblue_B: hi MartinSpott
+ darkblue_B: hi MartinSpott
-darkblue_B: we were working on the OSGeo liveDVD last night
+ darkblue_B: we were working on the OSGeo liveDVD last night
-darkblue_B: I moved over a PostGIS database
+ 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"
+ 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" ?
+ 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: 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: yes
-darkblue_B: there were about hmm 15 tables?
+ 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: 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: I'm sure you have ways you have developed to do such things
-darkblue_B: your database is so well maintained!
+ darkblue_B: your database is so well maintained!
-MartinSpot: Really ?
+ MartinSpot: Really ?
-darkblue_B: you and the others I should say
+ darkblue_B: you and the others I should say
-darkblue_B: ;-)
+ darkblue_B: ;-)
-darkblue_B: only you know the bad parts
+ darkblue_B: only you know the bad parts
-darkblue_B: to me, it looks great
+ 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.
+ 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
+ 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: 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: ....  plus a couple of cascaded shell scripts to generate the respective SQL maintenance scripts.
-MartinSpot: Now, is there anything missing for your HowTo ?
+ MartinSpot: Now, is there anything missing for your HowTo ?
-darkblue_B: Its not written@
+ darkblue_B: Its not written@
-darkblue_B: !!
+ darkblue_B: !!
-darkblue_B: I have 'real work' too
+ darkblue_B: I have 'real work' too
-MartinSpot: Ah, sure
+ MartinSpot: Ah, sure
-darkblue_B: well we made a template_postgis
+ darkblue_B: well we made a template_postgis
-darkblue_B: with spatial_ref_sys populated
+ darkblue_B: with spatial_ref_sys populated
-darkblue_B: then for the 15 tables, and 4 views.. I listed them in psql
+ darkblue_B: then for the 15 tables, and 4 views.. I listed them in psql
-darkblue_B: removed the seqeunce names
+ darkblue_B: removed the seqeunce names
-darkblue_B: used regular expressions to generate a list of the tables (and views)
+ 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 change owner script
-darkblue_B: created a pg_dump -t name1 -t name2... db --inserts line
+ darkblue_B: created a pg_dump -t name1 -t name2... db --inserts line
-darkblue_B: including geometry_columns but not including spatial ref sys
+ darkblue_B: including geometry_columns but not including spatial ref sys
-MartinSpot: Maybe adding an index or two is usually a good idea ....
+ 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: then edited out the create table statment by hand
-darkblue_B: and saved it
+ darkblue_B: and saved it
-MartinSpot: Ah good
+ MartinSpot: Ah good
-darkblue_B: hmm indexes
+ darkblue_B: hmm indexes
-darkblue_B: didnt think of that.. yes
+ darkblue_B: didnt think of that.. yes
-darkblue_B: fortunately all the commands worked for the views as if they were tables
+ 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)
+ 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 have done a similar thing for 100+ tables in a database once..
-darkblue_B: I didnt do anything special with the key
+ darkblue_B: I didnt do anything special with the key
-MartinSpot: plus a GIST index on the geometry column .....  clustered onto the table
+ MartinSpot: plus a GIST index on the geometry column .....  clustered onto the table
-darkblue_B: yes, indexes.. I didnt do that
+ darkblue_B: yes, indexes.. I didnt do that
-darkblue_B: this is for the FOSS4G live DVD
+ 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
+ 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: quite a few of the project will read it as a data source
-darkblue_B: well, PgAdmin editing, for one
+ darkblue_B: well, PgAdmin editing, for one
-darkblue_B: in the window
+ darkblue_B: in the window
-MartinSpot: Either 'ogr2ogr' or 'mapserver' or 'pgsql2shp' or ....  I don't know by now
+ MartinSpot: Either 'ogr2ogr' or 'mapserver' or 'pgsql2shp' or ....  I don't know by now
-darkblue_B: ah ok
+ 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)
+ 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: yes.. mleslie supplied the original
-darkblue_B: so there were constraints and such already
+ darkblue_B: so there were constraints and such already
-MartinSpot: Fine
+ MartinSpot: Fine
-darkblue_B: perhaps I will just add this chat to the wiki and call it done
+ 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: ....  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 ....)
+ MartinSpot: (typically my fault ....)
-darkblue_B: yes - geometry_columns seems to be in good order.. all in the same projection
+ 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: EPSG:2207 if I recall
-darkblue_B: in our US State of Oregon
+ darkblue_B: in our US State of Oregon
-darkblue_B: 2270
+ darkblue_B: 2270

Talk:Live GIS Disc Testing - Revision history

Wildintellect: don't forget to preview

Darkblueb: New page: 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...