Mercurial > dive4elements > river
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; |