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; |