Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-spatial.sql @ 1240:2ef8b67bd68a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. flys-backend/trunk@2642 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Fri, 02 Sep 2011 13:49:46 +0000
parents 07882568cef8
children f68a0504dfb6
comparison
equal deleted inserted replaced
1239:07882568cef8 1240:2ef8b67bd68a
1 BEGIN; 1 BEGIN;
2 2
3 -- Geodaesie/Flussachse+km/achse 3 -- Geodaesie/Flussachse+km/achse
4 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
4 CREATE TABLE river_axes ( 5 CREATE TABLE river_axes (
5 id SERIAL PRIMARY KEY NOT NULL, 6 id int PRIMARY KEY NOT NULL,
6 river_id int REFERENCES rivers(id), 7 river_id int REFERENCES rivers(id),
7 kind int NOT NULL DEFAULT 0 8 kind int NOT NULL DEFAULT 0
8 ); 9 );
9 SELECT AddGeometryColumn('river_axes', 'geom', 31466, 'LINESTRING', 2); 10 SELECT AddGeometryColumn('river_axes', 'geom', 31466, 'LINESTRING', 2);
11 ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ');
10 12
11 13
12 -- Geodaesie/Querprofile/* 14 -- Geodaesie/Querprofile/*
15 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
13 CREATE TABLE cross_section_tracks ( 16 CREATE TABLE cross_section_tracks (
14 id SERIAL PRIMARY KEY NOT NULL, 17 id int PRIMARY KEY NOT NULL,
15 river_id int REFERENCES rivers(id), 18 river_id int REFERENCES rivers(id),
16 km NUMERIC NOT NULL, 19 km NUMERIC NOT NULL,
17 z NUMERIC NOT NULL DEFAULT 0 20 z NUMERIC NOT NULL DEFAULT 0
18 ); 21 );
19 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31466, 'LINESTRING', 2); 22 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31466, 'LINESTRING', 2);
23 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ');
20 24
21 25
22 -- Geodaesie/Linien/rohre-und-spreen 26 -- Geodaesie/Linien/rohre-und-spreen
27 CREATE SEQUENCE LINES_ID_SEQ;
23 CREATE TABLE lines ( 28 CREATE TABLE lines (
24 id SERIAL PRIMARY KEY NOT NULL, 29 id int PRIMARY KEY NOT NULL,
25 river_id int REFERENCES rivers(id), 30 river_id int REFERENCES rivers(id),
26 kind int NOT NULL DEFAULT 0, 31 kind int NOT NULL DEFAULT 0,
27 z NUMERIC DEFAULT 0 32 z NUMERIC DEFAULT 0
28 ); 33 );
29 SELECT AddGeometryColumn('lines', 'geom', 31466, 'LINESTRING', 4); 34 SELECT AddGeometryColumn('lines', 'geom', 31466, 'LINESTRING', 4);
35 ALTER TABLE lines ALTER COLUMN id SET DEFAULT NEXTVAL('LINES_ID_SEQ');
30 -- 'kind': 36 -- 'kind':
31 -- 0: ROHR1 37 -- 0: ROHR1
32 -- 1: DAMM 38 -- 1: DAMM
33 39
34 40
35 -- Geodaesie/Bauwerke/Wehre.shp 41 -- Geodaesie/Bauwerke/Wehre.shp
42 CREATE SEQUENCE BUILDINGS_ID_SEQ;
36 CREATE TABLE buildings ( 43 CREATE TABLE buildings (
37 id SERIAL PRIMARY KEY NOT NULL, 44 id int PRIMARY KEY NOT NULL,
38 river_id int REFERENCES rivers(id), 45 river_id int REFERENCES rivers(id),
39 name VARCHAR(50) 46 name VARCHAR(50)
40 ); 47 );
41 SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2); 48 SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2);
49 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ');
42 50
43 51
44 -- Geodaesie/Festpunkte/Festpunkte.shp 52 -- Geodaesie/Festpunkte/Festpunkte.shp
53 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
45 CREATE TABLE fixpoints ( 54 CREATE TABLE fixpoints (
46 id SERIAL PRIMARY KEY NOT NULL, 55 id int PRIMARY KEY NOT NULL,
47 river_id int REFERENCES rivers(id), 56 river_id int REFERENCES rivers(id),
48 x int, 57 x int,
49 y int, 58 y int,
50 km NUMERIC NOT NULL, 59 km NUMERIC NOT NULL,
51 HPGP VARCHAR(2) 60 HPGP VARCHAR(2)
52 ); 61 );
53 SELECT AddGeometryColumn('fixpoints', 'geom', 31466, 'POINT', 2); 62 SELECT AddGeometryColumn('fixpoints', 'geom', 31466, 'POINT', 2);
63 ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ');
54 64
55 65
56 -- Hydrologie/Hydr. Grenzen/talaue.shp 66 -- Hydrologie/Hydr. Grenzen/talaue.shp
67 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
57 CREATE TABLE floodplain ( 68 CREATE TABLE floodplain (
58 id SERIAL PRIMARY KEY NOT NULL, 69 id int PRIMARY KEY NOT NULL,
59 river_id int REFERENCES rivers(id) 70 river_id int REFERENCES rivers(id)
60 ); 71 );
61 SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'MULTIPOLYGON', 2); 72 SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'MULTIPOLYGON', 2);
73 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ');
62 74
63 75
64 -- Geodaesie/Hoehenmodelle/* 76 -- Geodaesie/Hoehenmodelle/*
65 -- XXX DGM in englisch DEM, um dies zu vereinheitlichen 77 -- XXX DGM in englisch DEM, um dies zu vereinheitlichen
66 CREATE TABLE dgm ( 78 CREATE SEQUENCE DEM_ID_SEQ;
67 id SERIAL PRIMARY KEY NOT NULL, 79 CREATE TABLE dem (
80 id int PRIMARY KEY NOT NULL,
68 river_id int REFERENCES rivers(id), 81 river_id int REFERENCES rivers(id),
69 -- XXX Should we use the ranges table instead? 82 -- XXX Should we use the ranges table instead?
70 lower NUMERIC, 83 lower NUMERIC,
71 upper NUMERIC, 84 upper NUMERIC,
72 path VARCHAR(256), 85 path VARCHAR(256),
73 UNIQUE (river_id, lower, upper) 86 UNIQUE (river_id, lower, upper)
74 ); 87 );
88 ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ');
75 89
76 90
77 -- TODO 91 -- TODO
78 -- Flussachse+km/km 92 -- Flussachse+km/km
79 93
http://dive4elements.wald.intevation.org