Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-spatial.sql @ 2426:f97e9b810e58 2.6.1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged flys-backend/2.6.1
author Thomas Arendsen Hein <thomas@intevation.de>
date Fri, 28 Sep 2012 12:14:26 +0200
parents 918877ee70e7
children f3b4e32b5db6
comparison
equal deleted inserted replaced
2424:092e519ff461 2426:f97e9b810e58
1 BEGIN;
2
3 -- Geodaesie/Flussachse+km/achse
4 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
5 CREATE TABLE river_axes (
6 id int PRIMARY KEY NOT NULL,
7 river_id int REFERENCES rivers(id),
8 kind int NOT NULL DEFAULT 0
9 );
10 SELECT AddGeometryColumn('river_axes', 'geom', 31466, 'LINESTRING', 2);
11 ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ');
12
13
14 -- TODO: TestMe.
15 -- Geodaesie/Flussachse+km/km.shp
16 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
17 CREATE TABLE river_axes_km (
18 id int PRIMARY KEY NOT NULL,
19 river_id int REFERENCES rivers(id),
20 km NUMERIC NOT NULL
21 );
22 SELECT AddGeometryColumn('river_axes_km', 'geom', 31466, 'POINT', 2);
23 ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ');
24
25
26 --Geodaesie/Querprofile/QP-Spuren/qps.shp
27 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
28 CREATE TABLE cross_section_tracks (
29 id int PRIMARY KEY NOT NULL,
30 river_id int REFERENCES rivers(id),
31 km NUMERIC NOT NULL,
32 z NUMERIC NOT NULL DEFAULT 0
33 );
34 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31466, 'LINESTRING', 2);
35 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ');
36
37
38 -- Geodaesie/Linien/rohre-und-spreen
39 CREATE SEQUENCE LINES_ID_SEQ;
40 CREATE TABLE lines (
41 id int PRIMARY KEY NOT NULL,
42 river_id int REFERENCES rivers(id),
43 kind VARCHAR(16) NOT NULL,
44 z NUMERIC DEFAULT 0
45 );
46 SELECT AddGeometryColumn('lines', 'geom', 31466, 'LINESTRING', 4);
47 ALTER TABLE lines ALTER COLUMN id SET DEFAULT NEXTVAL('LINES_ID_SEQ');
48 -- 'kind':
49 -- 0: ROHR1
50 -- 1: DAMM
51
52
53 -- Geodaesie/Bauwerke/Wehre.shp
54 CREATE SEQUENCE BUILDINGS_ID_SEQ;
55 CREATE TABLE buildings (
56 id int PRIMARY KEY NOT NULL,
57 river_id int REFERENCES rivers(id),
58 name VARCHAR(256)
59 );
60 SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2);
61 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ');
62
63
64 -- Geodaesie/Festpunkte/Festpunkte.shp
65 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
66 CREATE TABLE fixpoints (
67 id int PRIMARY KEY NOT NULL,
68 river_id int REFERENCES rivers(id),
69 x int,
70 y int,
71 km NUMERIC NOT NULL,
72 HPGP VARCHAR(2)
73 );
74 SELECT AddGeometryColumn('fixpoints', 'geom', 31466, 'POINT', 2);
75 ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ');
76
77
78 -- Hydrologie/Hydr. Grenzen/talaue.shp
79 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
80 CREATE TABLE floodplain (
81 id int PRIMARY KEY NOT NULL,
82 river_id int REFERENCES rivers(id)
83 );
84 SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'POLYGON', 2);
85 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ');
86
87
88 -- Geodaesie/Hoehenmodelle/*
89 CREATE SEQUENCE DEM_ID_SEQ;
90 CREATE TABLE dem (
91 id int PRIMARY KEY NOT NULL,
92 river_id int REFERENCES rivers(id),
93 -- XXX Should we use the ranges table instead?
94 lower NUMERIC,
95 upper NUMERIC,
96 path VARCHAR(256),
97 UNIQUE (river_id, lower, upper)
98 );
99 ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ');
100
101
102 -- Hydrologie/Einzugsgebiete/EZG.shp
103 CREATE SEQUENCE CATCHMENT_ID_SEQ;
104 CREATE TABLE catchment (
105 id int PRIMARY KEY NOT NULL,
106 river_id int REFERENCES rivers(id),
107 area NUMERIC,
108 name VARCHAR(256)
109 );
110 SELECT AddGeometryColumn('catchment','geom',31466,'POLYGON',2);
111 ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ');
112
113
114 --Hydrologie/HW-Schutzanlagen/hws.shp
115 CREATE SEQUENCE HWS_ID_SEQ;
116 CREATE TABLE hws (
117 id int PRIMARY KEY NOT NULL,
118 river_id int REFERENCES rivers(id),
119 hws_facility VARCHAR(256),
120 type VARCHAR(256)
121 );
122 SELECT AddGeometryColumn('hws','geom',31466,'LINESTRING',2);
123 ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');
124
125
126 END;
http://dive4elements.wald.intevation.org