comparison flys-backend/doc/schema/postgresql-spatial.sql @ 2796:f3b4e32b5db6

Improved the db schema for storing existing floodmaps. flys-backend/trunk@4125 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Tue, 06 Mar 2012 11:54:41 +0000
parents 918877ee70e7
children e41d03bf9807
comparison
equal deleted inserted replaced
2795:c41bb1293acb 2796:f3b4e32b5db6
121 ); 121 );
122 SELECT AddGeometryColumn('hws','geom',31466,'LINESTRING',2); 122 SELECT AddGeometryColumn('hws','geom',31466,'LINESTRING',2);
123 ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ'); 123 ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');
124 124
125 125
126 --Hydrologie/UeSG
127 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
128 CREATE TABLE floodmaps (
129 id int PRIMARY KEY NOT NULL,
130 river_id int REFERENCES rivers(id),
131 kind int NOT NULL,
132 diff NUMERIC,
133 count int,
134 area NUMERIC,
135 perimeter NUMERIC
136 );
137 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ');
138
139
140 CREATE SEQUENCE POLYGONS_SEQ;
141 CREATE TABLE polygons (
142 id int PRIMARY KEY NOT NULL
143 );
144 SELECT AddGeometryColumn('polygons', 'geom', 31466, 'POLYGON', 2);
145 ALTER TABLE polygons ALTER COLUMN id SET DEFAULT NEXTVAL('POLYGONS_SEQ');
146
147
148 CREATE SEQUENCE FLOODMAP_GEOMS_SEQ;
149 CREATE TABLE floodmap_geoms (
150 id int PRIMARY KEY NOT NULL,
151 floodmaps_id int NOT NULL REFERENCES floodmaps(id),
152 polygons_id int NOT NULL REFERENCES polygons(id)
153 );
154 ALTER TABLE floodmap_geoms ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAP_GEOMS_SEQ');
155
126 END; 156 END;

http://dive4elements.wald.intevation.org