Mercurial > dive4elements > river

annotate flys-backend/doc/schema/postgresql-spatial.sql @ 1237:774dff3ee546

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added relation and hibernate classes for floodplains (german 'Talaue'). flys-backend/trunk@2615 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Wed, 31 Aug 2011 08:50:20 +0000
parents 324cb486db9f
children 35740d675866
rev   line source
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
1 BEGIN;
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
2
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
3 CREATE TABLE river_axes (
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
4 id SERIAL PRIMARY KEY NOT NULL,
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
5 river_id int REFERENCES rivers(id),
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
6 kind int NOT NULL DEFAULT 0
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
7 );
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
8 SELECT AddGeometryColumn('river_axes', 'geom', 31466, 'LINESTRING', 2);
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
9
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
10
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
11 CREATE TABLE cross_section_tracks (
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
12 id SERIAL PRIMARY KEY NOT NULL,
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
13 river_id int REFERENCES rivers(id),
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
14 km NUMERIC NOT NULL,
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
15 z NUMERIC NOT NULL DEFAULT 0
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
16 );
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
17 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31466, 'LINESTRING', 2);
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
18
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
19
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
20 CREATE TABLE lines (
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
21 id SERIAL PRIMARY KEY NOT NULL,
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
22 river_id int REFERENCES rivers(id),
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
23 kind int NOT NULL DEFAULT 0,
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
24 z NUMERIC DEFAULT 0
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
25 );
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
26 SELECT AddGeometryColumn('lines', 'geom', 31466, 'LINESTRING', 4);
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
27 -- 'kind':
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
28 -- 0: ROHR1
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
29 -- 1: DAMM
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
30
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
31
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
32 CREATE TABLE buildings (
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
33 id SERIAL PRIMARY KEY NOT NULL,
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
34 river_id int REFERENCES rivers(id),
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
35 name VARCHAR(50)
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
36 );
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
37 SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2);
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
38
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
39
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
40 CREATE TABLE fixpoints (
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
41 id SERIAL PRIMARY KEY NOT NULL,
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
42 river_id int REFERENCES rivers(id),
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
43 x int,
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
44 y int,
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
45 km NUMERIC NOT NULL,
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
46 HPGP VARCHAR(2)
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
47 );
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
48 SELECT AddGeometryColumn('fixpoints', 'geom', 31466, 'POINT', 2);
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
49
1236
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
50
1237
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
51 -- Talaue
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
52 CREATE TABLE floodplain (
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
53 id SERIAL PRIMARY KEY NOT NULL,
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
54 river_id int REFERENCES rivers(id)
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
55 );
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
56 SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'MULTIPOLYGON', 2);
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
57
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
58
1236
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
59 CREATE TABLE dgm (
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
60 id SERIAL PRIMARY KEY NOT NULL,
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
61 river_id int REFERENCES rivers(id),
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
62 -- XXX Should we use the ranges table instead?
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
63 lower NUMERIC,
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
64 upper NUMERIC,
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
65 path VARCHAR(256),
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
66 UNIQUE (river_id, lower, upper)
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
67 );
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
68
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
69 END;
http://dive4elements.wald.intevation.org