Mercurial > gemma

comparison schema/gemma.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 8b83b18a1d49
children 7cc79c65a9e5
comparison
equal deleted inserted replaced
4939:39b67b910204 4940:b3b2ba09a450
286 CREATE TABLE dirimps ( 286 CREATE TABLE dirimps (
287 dirimp smallint PRIMARY KEY 287 dirimp smallint PRIMARY KEY
288 -- TODO: Do we need name and/or definition from IENC feature catalogue? 288 -- TODO: Do we need name and/or definition from IENC feature catalogue?
289 -- (see page 381 of edition 2.3) 289 -- (see page 381 of edition 2.3)
290 ); 290 );
291 INSERT INTO dirimps VALUES (1), (2), (3), (4); 291 INSERT INTO dirimps VALUES (1), (2), (3), (4), (5);
292 -- dirimp_5 left out because it cannot be used for waterway area
293 292
294 CREATE TABLE depth_references ( 293 CREATE TABLE depth_references (
295 depth_reference varchar(4) PRIMARY KEY 294 depth_reference varchar(4) PRIMARY KEY
296 -- See col. AB and AI RIS-Index Encoding Guide 295 -- See col. AB and AI RIS-Index Encoding Guide
297 -- XXX: We need a way to distinguish between geodetic (eg. col. AP 296 -- XXX: We need a way to distinguish between geodetic (eg. col. AP
939 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, 938 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
940 scamin, picrep, txtdsc, sordat, sorind, 939 scamin, picrep, txtdsc, sordat, sorind,
941 0, colour, colpat, conrad, marsys, boyshp, catlam 940 0, colour, colpat, conrad, marsys, boyshp, catlam
942 ) AS fairway_marks_boylat) 941 ) AS fairway_marks_boylat)
943 )) 942 ))
943
944 -- Additional attributes for IENC feature BOYSAW
945 CREATE TABLE fairway_marks_boysaw (
946 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
947 colour varchar,
948 colpat varchar,
949 conrad int,
950 marsys int,
951 boyshp int
952 ) INHERITS (fairway_marks)
953 -- Prevent identical entries using composite type comparison
954 -- (i.e. considering two NULL values in a field equal):
955 CREATE UNIQUE INDEX fairway_marks_boysaw_distinct_rows
956 ON fairway_marks_boysaw
957 ((CAST((geom,
958 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
959 scamin, picrep, txtdsc, sordat, sorind,
960 0, colour, colpat, conrad, marsys, boyshp
961 ) AS fairway_marks_boysaw)
962 ))
963
964 -- Additional attributes for IENC feature BOYSPP
965 CREATE TABLE fairway_marks_boyspp (
966 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
967 colour varchar,
968 colpat varchar,
969 conrad int,
970 marsys int,
971 boyshp int,
972 catspm varchar
973 ) INHERITS (fairway_marks)
974 -- Prevent identical entries using composite type comparison
975 -- (i.e. considering two NULL values in a field equal):
976 CREATE UNIQUE INDEX fairway_marks_boyspp_distinct_rows
977 ON fairway_marks_boyspp
978 ((CAST((geom,
979 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
980 scamin, picrep, txtdsc, sordat, sorind,
981 0, colour, colpat, conrad, marsys, boyshp, catspm
982 ) AS fairway_marks_boyspp)
983 ))
984
985 -- Additional attributes for IENC features DAYMAR/daymar
986 CREATE TABLE fairway_marks_daymar (
987 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
988 colour varchar,
989 colpat varchar,
990 condtn int,
991 topshp int,
992 orient double precision
993 ) INHERITS (fairway_marks)
994 -- Prevent identical entries using composite type comparison
995 -- (i.e. considering two NULL values in a field equal):
996 CREATE UNIQUE INDEX fairway_marks_daymar_distinct_rows
997 ON fairway_marks_daymar
998 ((CAST((geom,
999 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
1000 scamin, picrep, txtdsc, sordat, sorind,
1001 0, colour, colpat, condtn, topshp, orient
1002 ) AS fairway_marks_daymar)
1003 ))
1004
1005 CREATE TABLE fairway_marks_daymar_dirimps (
1006 fm_daymar_id int REFERENCES fairway_marks_daymar,
1007 dirimp smallint REFERENCES dirimps,
1008 PRIMARY KEY (fm_daymar_id, dirimp)
1009 )
1010
1011 -- Additional attributes for IENC feature LIGHTS
1012 CREATE TABLE fairway_marks_lights (
1013 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1014 colour varchar,
1015 condtn int,
1016 orient double precision,
1017 catlit varchar,
1018 exclit int,
1019 litchr int,
1020 litvis varchar,
1021 mltylt int,
1022 sectr1 double precision,
1023 sectr2 double precision,
1024 siggrp varchar,
1025 sigper double precision,
1026 sigseq varchar,
1027 status varchar
1028 ) INHERITS (fairway_marks)
1029 -- Prevent identical entries using composite type comparison
1030 -- (i.e. considering two NULL values in a field equal):
1031 CREATE UNIQUE INDEX fairway_marks_lights_distinct_rows
1032 ON fairway_marks_lights
1033 ((CAST((geom,
1034 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
1035 scamin, picrep, txtdsc, sordat, sorind,
1036 0, colour, condtn, orient, catlit, exclit, litchr, litvis,
1037 mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status
1038 ) AS fairway_marks_lights)
1039 ))
1040
1041 -- Additional attributes for IENC feature RTPBCN
1042 CREATE TABLE fairway_marks_rtpbcn (
1043 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1044 condtn int,
1045 siggrp varchar,
1046 catrtb int,
1047 radwal varchar
1048 ) INHERITS (fairway_marks)
1049 -- Prevent identical entries using composite type comparison
1050 -- (i.e. considering two NULL values in a field equal):
1051 CREATE UNIQUE INDEX fairway_marks_rtpbcn_distinct_rows
1052 ON fairway_marks_rtpbcn
1053 ((CAST((geom,
1054 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
1055 scamin, picrep, txtdsc, sordat, sorind,
1056 0, condtn, siggrp, catrtb, radwal
1057 ) AS fairway_marks_rtpbcn)
1058 ))
1059
1060 -- Additional attributes for IENC feature TOPMAR
1061 CREATE TABLE fairway_marks_topmar (
1062 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1063 colour varchar,
1064 colpat varchar,
1065 condtn int,
1066 topshp int
1067 ) INHERITS (fairway_marks)
1068 -- Prevent identical entries using composite type comparison
1069 -- (i.e. considering two NULL values in a field equal):
1070 CREATE UNIQUE INDEX fairway_marks_topmar_distinct_rows
1071 ON fairway_marks_topmar
1072 ((CAST((geom,
1073 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
1074 scamin, picrep, txtdsc, sordat, sorind,
1075 0, colour, colpat, condtn, topshp
1076 ) AS fairway_marks_topmar)
1077 ))
1078
1079 -- Additional attributes for IENC feature NOTMRK
1080 CREATE TABLE fairway_marks_notmrk (
1081 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1082 condtn int,
1083 marsys int,
1084 orient double precision,
1085 status varchar,
1086 addmrk varchar,
1087 catnmk int,
1088 disipd double precision,
1089 disipu double precision,
1090 disbk1 double precision,
1091 disbk2 double precision,
1092 fnctnm int,
1093 bnkwtw int
1094 ) INHERITS (fairway_marks)
1095 -- Prevent identical entries using composite type comparison
1096 -- (i.e. considering two NULL values in a field equal):
1097 CREATE UNIQUE INDEX fairway_marks_notmrk_distinct_rows
1098 ON fairway_marks_notmrk
1099 ((CAST((geom,
1100 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
1101 scamin, picrep, txtdsc, sordat, sorind,
1102 0, condtn, marsys, orient, status, addmrk, catnmk,
1103 disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw
1104 ) AS fairway_marks_notmrk)
1105 ))
1106
1107 CREATE TABLE fairway_marks_notmrk_dirimps (
1108 fm_notmrk_id int REFERENCES fairway_marks_notmrk,
1109 dirimp smallint REFERENCES dirimps,
1110 PRIMARY KEY (fm_notmrk_id, dirimp)
1111 )
944 ; 1112 ;
945 1113
946 1114
947 -- 1115 --
948 -- Import queue and respective logging 1116 -- Import queue and respective logging