Mercurial > gemma

comparison schema/updates/1403/02.add_fairway_mark_types.sql @ 4940:b3b2ba09a450 fairway-marks-import

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add missing fairway mark types
author Tom Gottfried <tom@intevation.de>
date Mon, 17 Feb 2020 18:38:45 +0100
parents
children
comparison
equal deleted inserted replaced
4939:39b67b910204 4940:b3b2ba09a450
1 CREATE TABLE waterway.fairway_marks_boysaw (
2 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
3 colour varchar,
4 colpat varchar,
5 conrad int,
6 marsys int,
7 boyshp int
8 ) INHERITS (waterway.fairway_marks);
9 CREATE UNIQUE INDEX fairway_marks_boysaw_distinct_rows
10 ON waterway.fairway_marks_boysaw
11 ((CAST((geom,
12 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
13 scamin, picrep, txtdsc, sordat, sorind,
14 0, colour, colpat, conrad, marsys, boyshp
15 ) AS waterway.fairway_marks_boysaw)
16 ));
17
18 CREATE TABLE waterway.fairway_marks_boyspp (
19 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
20 colour varchar,
21 colpat varchar,
22 conrad int,
23 marsys int,
24 boyshp int,
25 catspm varchar
26 ) INHERITS (waterway.fairway_marks);
27 CREATE UNIQUE INDEX fairway_marks_boyspp_distinct_rows
28 ON waterway.fairway_marks_boyspp
29 ((CAST((geom,
30 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
31 scamin, picrep, txtdsc, sordat, sorind,
32 0, colour, colpat, conrad, marsys, boyshp, catspm
33 ) AS waterway.fairway_marks_boyspp)
34 ));
35
36 CREATE TABLE waterway.fairway_marks_daymar (
37 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
38 colour varchar,
39 colpat varchar,
40 condtn int,
41 topshp int,
42 orient double precision
43 ) INHERITS (waterway.fairway_marks);
44 CREATE UNIQUE INDEX fairway_marks_daymar_distinct_rows
45 ON waterway.fairway_marks_daymar
46 ((CAST((geom,
47 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
48 scamin, picrep, txtdsc, sordat, sorind,
49 0, colour, colpat, condtn, topshp, orient
50 ) AS waterway.fairway_marks_daymar)
51 ));
52
53 CREATE TABLE waterway.fairway_marks_daymar_dirimps (
54 fm_daymar_id int REFERENCES waterway.fairway_marks_daymar,
55 dirimp smallint REFERENCES dirimps,
56 PRIMARY KEY (fm_daymar_id, dirimp)
57 );
58
59 CREATE TABLE waterway.fairway_marks_lights (
60 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
61 colour varchar,
62 condtn int,
63 orient double precision,
64 catlit varchar,
65 exclit int,
66 litchr int,
67 litvis varchar,
68 mltylt int,
69 sectr1 double precision,
70 sectr2 double precision,
71 siggrp varchar,
72 sigper double precision,
73 sigseq varchar,
74 status varchar
75 ) INHERITS (waterway.fairway_marks);
76 CREATE UNIQUE INDEX fairway_marks_lights_distinct_rows
77 ON waterway.fairway_marks_lights
78 ((CAST((geom,
79 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
80 scamin, picrep, txtdsc, sordat, sorind,
81 0, colour, condtn, orient, catlit, exclit, litchr, litvis,
82 mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status
83 ) AS waterway.fairway_marks_lights)
84 ));
85
86 CREATE TABLE waterway.fairway_marks_rtpbcn (
87 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
88 condtn int,
89 siggrp varchar,
90 catrtb int,
91 radwal varchar
92 ) INHERITS (waterway.fairway_marks);
93 CREATE UNIQUE INDEX fairway_marks_rtpbcn_distinct_rows
94 ON waterway.fairway_marks_rtpbcn
95 ((CAST((geom,
96 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
97 scamin, picrep, txtdsc, sordat, sorind,
98 0, condtn, siggrp, catrtb, radwal
99 ) AS waterway.fairway_marks_rtpbcn)
100 ));
101
102 CREATE TABLE waterway.fairway_marks_topmar (
103 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
104 colour varchar,
105 colpat varchar,
106 condtn int,
107 topshp int
108 ) INHERITS (waterway.fairway_marks);
109 CREATE UNIQUE INDEX fairway_marks_topmar_distinct_rows
110 ON waterway.fairway_marks_topmar
111 ((CAST((geom,
112 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
113 scamin, picrep, txtdsc, sordat, sorind,
114 0, colour, colpat, condtn, topshp
115 ) AS waterway.fairway_marks_topmar)
116 ));
117
118 CREATE TABLE waterway.fairway_marks_notmrk (
119 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
120 condtn int,
121 marsys int,
122 orient double precision,
123 status varchar,
124 addmrk varchar,
125 catnmk int,
126 disipd double precision,
127 disipu double precision,
128 disbk1 double precision,
129 disbk2 double precision,
130 fnctnm int,
131 bnkwtw int
132 ) INHERITS (waterway.fairway_marks);
133 CREATE UNIQUE INDEX fairway_marks_notmrk_distinct_rows
134 ON waterway.fairway_marks_notmrk
135 ((CAST((geom,
136 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
137 scamin, picrep, txtdsc, sordat, sorind,
138 0, condtn, marsys, orient, status, addmrk, catnmk,
139 disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw
140 ) AS waterway.fairway_marks_notmrk)
141 ));
142
143 CREATE TABLE waterway.fairway_marks_notmrk_dirimps (
144 fm_notmrk_id int REFERENCES waterway.fairway_marks_notmrk,
145 dirimp smallint REFERENCES dirimps,
146 PRIMARY KEY (fm_notmrk_id, dirimp)
147 );
148
149 GRANT SELECT on ALL tables in schema waterway TO waterway_user ;
150 GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway
151 TO waterway_admin;