Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-spatial.sql @ 4931:f5c55d7ea07e dami

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
SCHEMA Change (psql only) use FLOAT8 for real numbers In oracle they are numbers with precision so there is no change necessary there.
author Andre Heinecke <aheinecke@intevation.de>
date Mon, 28 Jan 2013 18:24:01 +0100
parents 28f992c0f937
children a72e90fc45ed
comparison
equal deleted inserted replaced
4930:28f992c0f937 4931:f5c55d7ea07e
17 -- Geodaesie/Flussachse+km/km.shp 17 -- Geodaesie/Flussachse+km/km.shp
18 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ; 18 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
19 CREATE TABLE river_axes_km ( 19 CREATE TABLE river_axes_km (
20 id int PRIMARY KEY NOT NULL, 20 id int PRIMARY KEY NOT NULL,
21 river_id int REFERENCES rivers(id), 21 river_id int REFERENCES rivers(id),
22 km NUMERIC NOT NULL, 22 km FLOAT8 NOT NULL,
23 name VARCHAR(64), 23 name VARCHAR(64),
24 path VARCHAR(256) 24 path VARCHAR(256)
25 ); 25 );
26 SELECT AddGeometryColumn('river_axes_km', 'geom', 31467, 'POINT', 2); 26 SELECT AddGeometryColumn('river_axes_km', 'geom', 31467, 'POINT', 2);
27 ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ'); 27 ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ');
30 --Geodaesie/Querprofile/QP-Spuren/qps.shp 30 --Geodaesie/Querprofile/QP-Spuren/qps.shp
31 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; 31 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
32 CREATE TABLE cross_section_tracks ( 32 CREATE TABLE cross_section_tracks (
33 id int PRIMARY KEY NOT NULL, 33 id int PRIMARY KEY NOT NULL,
34 river_id int REFERENCES rivers(id), 34 river_id int REFERENCES rivers(id),
35 km NUMERIC NOT NULL, 35 km FLOAT8 NOT NULL,
36 z NUMERIC NOT NULL DEFAULT 0, 36 z FLOAT8 NOT NULL DEFAULT 0,
37 name VARCHAR(64), 37 name VARCHAR(64),
38 path VARCHAR(256) 38 path VARCHAR(256)
39 ); 39 );
40 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31467, 'LINESTRING', 2); 40 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31467, 'LINESTRING', 2);
41 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ'); 41 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ');
45 CREATE SEQUENCE LINES_ID_SEQ; 45 CREATE SEQUENCE LINES_ID_SEQ;
46 CREATE TABLE lines ( 46 CREATE TABLE lines (
47 id int PRIMARY KEY NOT NULL, 47 id int PRIMARY KEY NOT NULL,
48 river_id int REFERENCES rivers(id), 48 river_id int REFERENCES rivers(id),
49 kind VARCHAR(16) NOT NULL, 49 kind VARCHAR(16) NOT NULL,
50 z NUMERIC DEFAULT 0, 50 z FLOAT8 DEFAULT 0,
51 name VARCHAR(64), 51 name VARCHAR(64),
52 path VARCHAR(256) 52 path VARCHAR(256)
53 ); 53 );
54 SELECT AddGeometryColumn('lines', 'geom', 31467, 'LINESTRING', 3); 54 SELECT AddGeometryColumn('lines', 'geom', 31467, 'LINESTRING', 3);
55 ALTER TABLE lines ALTER COLUMN id SET DEFAULT NEXTVAL('LINES_ID_SEQ'); 55 ALTER TABLE lines ALTER COLUMN id SET DEFAULT NEXTVAL('LINES_ID_SEQ');
73 -- Geodaesie/Festpunkte/Festpunkte.shp 73 -- Geodaesie/Festpunkte/Festpunkte.shp
74 CREATE SEQUENCE FIXPOINTS_ID_SEQ; 74 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
75 CREATE TABLE fixpoints ( 75 CREATE TABLE fixpoints (
76 id int PRIMARY KEY NOT NULL, 76 id int PRIMARY KEY NOT NULL,
77 river_id int REFERENCES rivers(id), 77 river_id int REFERENCES rivers(id),
78 x int, 78 x FLOAT8,
79 y int, 79 y FLOAT8,
80 km NUMERIC NOT NULL, 80 km FLOAT8 NOT NULL,
81 HPGP VARCHAR(2), 81 HPGP VARCHAR(2),
82 name VARCHAR(64), 82 name VARCHAR(64),
83 path VARCHAR(256) 83 path VARCHAR(256)
84 ); 84 );
85 SELECT AddGeometryColumn('fixpoints', 'geom', 31467, 'POINT', 2); 85 SELECT AddGeometryColumn('fixpoints', 'geom', 31467, 'POINT', 2);
103 CREATE TABLE dem ( 103 CREATE TABLE dem (
104 id int PRIMARY KEY NOT NULL, 104 id int PRIMARY KEY NOT NULL,
105 river_id int REFERENCES rivers(id), 105 river_id int REFERENCES rivers(id),
106 -- XXX Should we use the ranges table instead? 106 -- XXX Should we use the ranges table instead?
107 name VARCHAR(64), 107 name VARCHAR(64),
108 lower NUMERIC, 108 lower FLOAT8,
109 upper NUMERIC, 109 upper FLOAT8,
110 year_from VARCHAR(32) NOT NULL, 110 year_from VARCHAR(32) NOT NULL,
111 year_to VARCHAR(32) NOT NULL, 111 year_to VARCHAR(32) NOT NULL,
112 projection VARCHAR(32) NOT NULL, 112 projection VARCHAR(32) NOT NULL,
113 elevation_state VARCHAR(32), 113 elevation_state VARCHAR(32),
114 format VARCHAR(32), 114 format VARCHAR(32),
123 -- Hydrologie/Einzugsgebiete/EZG.shp 123 -- Hydrologie/Einzugsgebiete/EZG.shp
124 CREATE SEQUENCE CATCHMENT_ID_SEQ; 124 CREATE SEQUENCE CATCHMENT_ID_SEQ;
125 CREATE TABLE catchment ( 125 CREATE TABLE catchment (
126 id int PRIMARY KEY NOT NULL, 126 id int PRIMARY KEY NOT NULL,
127 river_id int REFERENCES rivers(id), 127 river_id int REFERENCES rivers(id),
128 area NUMERIC, 128 area FLOAT8,
129 name VARCHAR(256), 129 name VARCHAR(256),
130 path VARCHAR(256) 130 path VARCHAR(256)
131 ); 131 );
132 SELECT AddGeometryColumn('catchment','geom',31467,'POLYGON',2); 132 SELECT AddGeometryColumn('catchment','geom',31467,'POLYGON',2);
133 ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ'); 133 ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ');
228 CREATE TABLE floodmaps ( 228 CREATE TABLE floodmaps (
229 id int PRIMARY KEY NOT NULL, 229 id int PRIMARY KEY NOT NULL,
230 river_id int REFERENCES rivers(id), 230 river_id int REFERENCES rivers(id),
231 name varchar(64) NOT NULL, 231 name varchar(64) NOT NULL,
232 kind int NOT NULL, 232 kind int NOT NULL,
233 diff real, 233 diff FLOAT8,
234 count int, 234 count int,
235 area real, 235 area FLOAT8,
236 perimeter real, 236 perimeter FLOAT8,
237 path VARCHAR(256) 237 path VARCHAR(256)
238 ); 238 );
239 SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2); 239 SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2);
240 ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom; 240 ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom;
241 ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text); 241 ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text);
http://dive4elements.wald.intevation.org