Mercurial > dive4elements > river

annotate flys-backend/doc/schema/postgresql-spatial.sql @ 1241:f68a0504dfb6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Postgresql spatial scheme extended. flys-backend/trunk@2706 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Bjoern Schilberg <bjoern@intevation.de>
date Mon, 12 Sep 2011 13:20:05 +0000
parents 2ef8b67bd68a
children d6520d46edb7
rev   line source
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
1 BEGIN;
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
2
1238
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
3 -- Geodaesie/Flussachse+km/achse
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
4 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
5 CREATE TABLE river_axes (
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
6 id int PRIMARY KEY NOT NULL,
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
7 river_id int REFERENCES rivers(id),
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
8 kind int NOT NULL DEFAULT 0
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
9 );
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
10 SELECT AddGeometryColumn('river_axes', 'geom', 31466, 'LINESTRING', 2);
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
11 ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ');
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
12
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
13
1238
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
14 -- Geodaesie/Querprofile/*
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
15 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
16 CREATE TABLE cross_section_tracks (
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
17 id int PRIMARY KEY NOT NULL,
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
18 river_id int REFERENCES rivers(id),
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
19 km NUMERIC NOT NULL,
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
20 z NUMERIC NOT NULL DEFAULT 0
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
21 );
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
22 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31466, 'LINESTRING', 2);
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
23 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ');
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
24
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
25
1238
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
26 -- Geodaesie/Linien/rohre-und-spreen
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
27 CREATE SEQUENCE LINES_ID_SEQ;
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
28 CREATE TABLE lines (
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
29 id int PRIMARY KEY NOT NULL,
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
30 river_id int REFERENCES rivers(id),
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
31 kind int NOT NULL DEFAULT 0,
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
32 z NUMERIC DEFAULT 0
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
33 );
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
34 SELECT AddGeometryColumn('lines', 'geom', 31466, 'LINESTRING', 4);
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
35 ALTER TABLE lines ALTER COLUMN id SET DEFAULT NEXTVAL('LINES_ID_SEQ');
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
36 -- 'kind':
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
37 -- 0: ROHR1
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
38 -- 1: DAMM
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
39
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
40
1238
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
41 -- Geodaesie/Bauwerke/Wehre.shp
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
42 CREATE SEQUENCE BUILDINGS_ID_SEQ;
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
43 CREATE TABLE buildings (
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
44 id int PRIMARY KEY NOT NULL,
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
45 river_id int REFERENCES rivers(id),
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
46 name VARCHAR(50)
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
47 );
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
48 SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2);
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
49 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ');
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
50
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
51
1238
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
52 -- Geodaesie/Festpunkte/Festpunkte.shp
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
53 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
54 CREATE TABLE fixpoints (
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
55 id int PRIMARY KEY NOT NULL,
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
56 river_id int REFERENCES rivers(id),
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
57 x int,
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
58 y int,
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
59 km NUMERIC NOT NULL,
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
60 HPGP VARCHAR(2)
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
61 );
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
62 SELECT AddGeometryColumn('fixpoints', 'geom', 31466, 'POINT', 2);
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
63 ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ');
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
64
1236
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
65
1238
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
66 -- Hydrologie/Hydr. Grenzen/talaue.shp
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
67 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
1237
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
68 CREATE TABLE floodplain (
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
69 id int PRIMARY KEY NOT NULL,
1237
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
70 river_id int REFERENCES rivers(id)
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
71 );
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
72 SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'MULTIPOLYGON', 2);
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
73 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ');
1237
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
74
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
75
1238
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
76 -- Geodaesie/Hoehenmodelle/*
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
77 CREATE SEQUENCE DEM_ID_SEQ;
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
78 CREATE TABLE dem (
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
79 id int PRIMARY KEY NOT NULL,
1236
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
80 river_id int REFERENCES rivers(id),
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
81 -- XXX Should we use the ranges table instead?
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
82 lower NUMERIC,
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
83 upper NUMERIC,
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
84 path VARCHAR(256),
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
85 UNIQUE (river_id, lower, upper)
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
86 );
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
87 ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ');
1236
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
88
1238
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
89
1241
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
90 -- Hydrologie/Einzugsgebiete/EZG.shp
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
91 -- Hinweise zu ezg_saar.shp wird nicht importiert:
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
92 -- CLASS: Integer (8.0) KLAEREN: wir die benoetigt?
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
93 -- AREA: Real (19.8) laesst sich auch durch EZG.shp bestimmen
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
94 -- PERIMETER: Real (19.8) laesst sich auch durch EZG.shp bestimmen
1238
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
95
1241
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
96 CREATE SEQUENCE CATCHMENT_ID_SEQ;
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
97 CREATE TABLE catchment (
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
98 id int PRIMARY KEY NOT NULL,
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
99 river_id int REFERENCES rivers(id),
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
100 "area" numeric,
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
101 "name" VARCHAR(80)
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
102 );
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
103 SELECT AddGeometryColumn('catchment','geom',31466,'POLYGON',2);
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
104 ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ');
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
105
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
106 -- Hydrologie/Einzugsgebiete/b1-polygon.shp
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
107 -- Nur fuer saar vorhanden
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
108 -- IDEE alle Begrenzungspolygone in eine Tabelle.
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
109 CREATE SEQUENCE BOUNDARYPOLYS_ID_SEQ;
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
110 CREATE TABLE boundarypolys (
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
111 id int PRIMARY KEY NOT NULL,
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
112 river_id int REFERENCES rivers(id)
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
113 );
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
114 SELECT AddGeometryColumn('boundarypolys','geom',31466,'MULTIPOLYGON',2);
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
115 ALTER TABLE boundarypolys ALTER COLUMN id SET DEFAULT NEXTVAL('BOUNDARYPOLYS_ID_SEQ');
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
116
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
117 -- Hydrologie/HW-Schutzanlagen
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
118 -- Wird nicht benoetigt, stattdessen verwenden wir
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
119 -- Gewaesser/Saar/Geodaesie/Linien/rohre-und-sperren.shp
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
120 -- hws.shp beinhaltet die Geometrien von:
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
121 -- HWS-Lisdorf.shp -- hws_anlage
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
122 -- HWS-Mettlach.shp -- maßnahme -> hws_anlage
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
123 -- HWS-Rehlingen.shp -- hw -> hws_anlage
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
124 -- HWS_Saarburg.shp -- höhe? bauart?
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
125 -- HWS-Schoden-Rhl-Pf.shp -- hws_anlage
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
126 -- HWS_Schoden.shp --höhe? bauart?
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
127 -- HWS-Serrig.shp --hws_anlage
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
128 -- CREATE SEQUENCE HWS_EZG_ID_SEQ;
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
129 -- CREATE TABLE hws (
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
130 -- id int PRIMARY KEY NOT NULL,
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
131 -- oid int,
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
132 -- river_id int REFERENCES rivers(id),
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
133 -- hws_facility VARCHAR(40),
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
134 -- typ VARCHAR(254)
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
135 -- );
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
136 -- SELECT AddGeometryColumn('hws','geom',31466,'MULTILINESTRING',2);
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
137 -- ALTER TABLE hw ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
138
1238
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
139 -- Hydrologie/Hydr. Grenzen/Linien
1241
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
140 -- BfG/boeschung_*.shp
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
141 CREATE SEQUENCE BANKS_ID_SEQ;
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
142 CREATE TABLE banks (
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
143 id int PRIMARY KEY NOT NULL,
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
144 river_id int REFERENCES rivers(id)
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
145 );
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
146 SELECT AddGeometryColumn('banks','geom',31466,'MULTILINESTRING',2);
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
147 ALTER TABLE banks ALTER COLUMN id SET DEFAULT NEXTVAL('BANKS_ID_SEQ');
1238
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
148
1241
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
149 -- BfG/hauptoeff_*.shp
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
150 CREATE SEQUENCE MAINSPANS_ID_SEQ;
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
151 CREATE TABLE mainspans(
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
152 id int PRIMARY KEY NOT NULL,
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
153 river_id int REFERENCES rivers(id)
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
154 );
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
155 SELECT AddGeometryColumn('mainspans','geom',31466,'MULTILINESTRING',2);
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
156 ALTER TABLE mainspans ALTER COLUMN id SET DEFAULT NEXTVAL('MAINSPANS_ID_SEQ');
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
157
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
158 -- BfG/MNQ-*.shp
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
159 CREATE SEQUENCE MNQ_ID_SEQ;
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
160 CREATE TABLE mnq (gid serial PRIMARY KEY,
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
161 id int PRIMARY KEY NOT NULL,
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
162 river_id int REFERENCES rivers(id),
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
163 haltung varchar(16)
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
164 );
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
165 SELECT AddGeometryColumn('mnq', 'the_geom',31466,'MULTIPOLYGON',2);
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
166 ALTER TABLE mnq ALTER COLUMN id SET DEFAULT NEXTVAL('MNQ_ID_SEQ');
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
167
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
168 -- BfG/modellgrenze*.shp
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
169 CREATE SEQUENCE MODELBOUNDARY_ID_SEQ;
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
170 CREATE TABLE modelboundary (
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
171 id int PRIMARY KEY NOT NULL,
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
172 river_id int REFERENCES rivers(id)
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
173 );
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
174 SELECT AddGeometryColumn('modelboundary','geom',31466,'MULTILINESTRING',2);
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
175 ALTER TABLE modelboundary ALTER COLUMN id SET DEFAULT NEXTVAL('MODELBOUNDARY_ID_SEQ');
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
176
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
177 -- TODO: Klaeren ob benoetigt, da einzel Geometrien in Tabelle vorland.
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
178 -- BfG/saar-sld-vorland.shp
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
179
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
180 -- BfG/uferlinie.shp
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
181 CREATE SEQUENCE SHORELINE_ID_SEQ;
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
182 CREATE TABLE shoreline(
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
183 id int PRIMARY KEY NOT NULL,
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
184 river_id int REFERENCES rivers(id)
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
185 );
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
186 SELECT AddGeometryColumn('shoreline','geom',31466,'MULTILINESTRING',2);
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
187 ALTER TABLE shoreline ALTER COLUMN id SET DEFAULT NEXTVAL('SHORELINE_ID_SEQ');
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
188
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
189 -- BfG/vorland_*.shp
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
190 CREATE SEQUENCE FORELAND_ID_SEQ;
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
191 CREATE TABLE foreland(
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
192 id int PRIMARY KEY NOT NULL,
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
193 river_id int REFERENCES rivers(id)
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
194 );
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
195 SELECT AddGeometryColumn('foreland','geom',31466,'MULTILINESTRING',2);
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
196 ALTER TABLE foreland ALTER COLUMN id SET DEFAULT NEXTVAL('FORELANDS_ID_SEQ');
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
197
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
198
1238
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
199 -- Hydrologie/Streckendaten
1241
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
200 -- pegellage_saar.shp
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
201 CREATE SEQUENCE LEVELPOSITION_ID_SEQ;
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
202 CREATE TABLE levelposition (
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
203 id int PRIMARY KEY NOT NULL,
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
204 river_id int REFERENCES rivers(id),
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
205 x numeric(10,0),
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
206 y numeric(10,0),
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
207 name varchar(254)
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
208 );
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
209 SELECT AddGeometryColumn('levelposition','geom','31466','POINT',2);
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
210 ALTER TABLE levelposition ALTER COLUMN id SET DEFAULT NEXTVAL('LEVELPOSITION_ID_SEQ');
1238
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
211
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
212 -- Hydrologie/UeSG/Berechnung
1241
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
213 -- Berechnung/Aktuell/BfG
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
214 CREATE SEQUENCE COMPUTATIONS_BFG_ID_SEQ;
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
215 CREATE TABLE computations_bfg (
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
216 id int PRIMARY KEY NOT NULL,
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
217 river_id int REFERENCES rivers(id),
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
218 section varchar(254),
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
219 area float8,
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
220 perimeter float8
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
221 );
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
222 SELECT AddGeometryColumn('computations_bfg','geom','31466','MULTIPOLYGON',2);
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
223 ALTER TABLE computations_bfg ALTER COLUMN id SET DEFAULT NEXTVAL('COMPUTATIONS_BFG_ID_SEQ');
1238
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
224
1241
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
225 -- Berechnung/Aktuell/Land
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
226 CREATE SEQUENCE COMPUTATIONS_COUNTRY_ID_SEQ;
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
227 CREATE TABLE computations_country(
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
228 id int PRIMARY KEY NOT NULL,
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
229 river_id int REFERENCES rivers(id),
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
230 text varchar(254)
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
231 );
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
232 SELECT AddGeometryColumn('computations_contry','geom','31466','MULTILINESTRING',2);
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
233 ALTER TABLE computations_country ALTER COLUMN id SET DEFAULT NEXTVAL('COMPUTATIONS_COUNTRY_ID_SEQ');
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
234
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
235
1238
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
236 -- Hydrologie/UeSG/Messung
1241
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
237 CREATE SEQUENCE MEASUREMENTS_ID_SEQ;
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
238 CREATE TABLE measurements (
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
239 id int PRIMARY KEY NOT NULL,
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
240 river_id int REFERENCES rivers(id),
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
241 year varchar(254),
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
242 oid varchar(40)
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
243 );
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
244 SELECT AddGeometryColumn('measurement','geom','31466','MULTILINESTRING',2);
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
245 ALTER TABLE measurements ALTER COLUMN id SET DEFAULT NEXTVAL('MEASUREMENTS_ID_SEQ');
1238
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
246
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
247 END;
http://dive4elements.wald.intevation.org