Difference between revisions of "Talk:Live GIS Disc Testing"
Jump to navigation
Jump to search
(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...) |
(don't forget to preview) |
||
| 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 |
Latest revision as of 16:18, 21 September 2009
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