Mercurial > dive4elements > river
comparison flys-backend/doc/schema/postgresql-spatial.sql @ 3807:d73c43798a99 pre2.6-2011-11-04
merged flys-backend/pre2.6-2011-11-04
| author | Thomas Arendsen Hein <thomas@intevation.de> |
|---|---|
| date | Fri, 28 Sep 2012 12:14:51 +0200 |
| parents | b8e01307c138 |
| children | a8f408fdf58c |
comparison
equal
deleted
inserted
replaced
| 3806:881fcd01e056 | 3807:d73c43798a99 |
|---|---|
| 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 int NOT NULL DEFAULT 0, | |
| 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 oid int, | |
| 119 river_id int REFERENCES rivers(id), | |
| 120 hws_facility VARCHAR(256), | |
| 121 typ VARCHAR(256) | |
| 122 ); | |
| 123 SELECT AddGeometryColumn('hws','geom',31466,'LINESTRING',2); | |
| 124 ALTER TABLE hw ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ'); | |
| 125 | |
| 126 | |
| 127 END; |