Mercurial > gemma

comparison schema/gemma.sql @ 4971:de190de05f67 fairway-marks-import

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add index to speed up fairway marks imports a bit The new indexes allow index based filtering of historic entries using the @> operator in the main import SQL statement.
author Tom Gottfried <tom@intevation.de>
date Fri, 28 Feb 2020 18:35:07 +0100
parents 3f704ebad0c5
children 4a816ecf70de
comparison
equal deleted inserted replaced
4970:5890e62e6d52 4971:de190de05f67
897 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, 897 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
898 scamin, picrep, txtdsc, sordat, sorind, 898 scamin, picrep, txtdsc, sordat, sorind,
899 colour, colpat, condtn, bcnshp, catlam, 0 899 colour, colpat, condtn, bcnshp, catlam, 0
900 ) AS fairway_marks_bcnlat_hydro) 900 ) AS fairway_marks_bcnlat_hydro)
901 )) 901 ))
902 CREATE INDEX fairway_marks_bcnlat_hydro_validity
903 ON fairway_marks_bcnlat_hydro USING GiST (validity)
902 904
903 CREATE TABLE fairway_marks_bcnlat_ienc ( 905 CREATE TABLE fairway_marks_bcnlat_ienc (
904 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY 906 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
905 ) INHERITS (fairway_marks_bcnlat) 907 ) INHERITS (fairway_marks_bcnlat)
906 -- Prevent identical entries using composite type comparison 908 -- Prevent identical entries using composite type comparison
911 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, 913 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
912 scamin, picrep, txtdsc, sordat, sorind, 914 scamin, picrep, txtdsc, sordat, sorind,
913 colour, colpat, condtn, bcnshp, catlam, 0 915 colour, colpat, condtn, bcnshp, catlam, 0
914 ) AS fairway_marks_bcnlat_ienc) 916 ) AS fairway_marks_bcnlat_ienc)
915 )) 917 ))
918 CREATE INDEX fairway_marks_bcnlat_ienc_validity
919 ON fairway_marks_bcnlat_ienc USING GiST (validity)
916 920
917 CREATE TABLE fairway_marks_bcnlat_dirimps ( 921 CREATE TABLE fairway_marks_bcnlat_dirimps (
918 fm_bcnlat_id int REFERENCES fairway_marks_bcnlat_ienc, 922 fm_bcnlat_id int REFERENCES fairway_marks_bcnlat_ienc,
919 dirimp smallint REFERENCES dirimps, 923 dirimp smallint REFERENCES dirimps,
920 PRIMARY KEY (fm_bcnlat_id, dirimp) 924 PRIMARY KEY (fm_bcnlat_id, dirimp)
938 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, 942 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
939 scamin, picrep, txtdsc, sordat, sorind, 943 scamin, picrep, txtdsc, sordat, sorind,
940 0, colour, colpat, conrad, marsys, boyshp, catcam 944 0, colour, colpat, conrad, marsys, boyshp, catcam
941 ) AS fairway_marks_boycar) 945 ) AS fairway_marks_boycar)
942 )) 946 ))
947 CREATE INDEX fairway_marks_boycar_validity
948 ON fairway_marks_boycar USING GiST (validity)
943 949
944 -- Additional attributes for IENC feature BOYLAT 950 -- Additional attributes for IENC feature BOYLAT
945 CREATE TABLE fairway_marks_boylat ( 951 CREATE TABLE fairway_marks_boylat (
946 colour varchar, 952 colour varchar,
947 colpat varchar, 953 colpat varchar,
962 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, 968 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
963 scamin, picrep, txtdsc, sordat, sorind, 969 scamin, picrep, txtdsc, sordat, sorind,
964 colour, colpat, conrad, marsys, boyshp, catlam, 0 970 colour, colpat, conrad, marsys, boyshp, catlam, 0
965 ) AS fairway_marks_boylat_hydro) 971 ) AS fairway_marks_boylat_hydro)
966 )) 972 ))
973 CREATE INDEX fairway_marks_boylat_hydro_validity
974 ON fairway_marks_boylat_hydro USING GiST (validity)
967 975
968 CREATE TABLE fairway_marks_boylat_ienc ( 976 CREATE TABLE fairway_marks_boylat_ienc (
969 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY 977 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
970 ) INHERITS (fairway_marks_boylat) 978 ) INHERITS (fairway_marks_boylat)
971 -- Prevent identical entries using composite type comparison 979 -- Prevent identical entries using composite type comparison
976 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, 984 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
977 scamin, picrep, txtdsc, sordat, sorind, 985 scamin, picrep, txtdsc, sordat, sorind,
978 colour, colpat, conrad, marsys, boyshp, catlam, 0 986 colour, colpat, conrad, marsys, boyshp, catlam, 0
979 ) AS fairway_marks_boylat_ienc) 987 ) AS fairway_marks_boylat_ienc)
980 )) 988 ))
989 CREATE INDEX fairway_marks_boylat_ienc_validity
990 ON fairway_marks_boylat_ienc USING GiST (validity)
981 991
982 -- Additional attributes for IENC feature BOYSAW 992 -- Additional attributes for IENC feature BOYSAW
983 CREATE TABLE fairway_marks_boysaw ( 993 CREATE TABLE fairway_marks_boysaw (
984 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 994 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
985 colour varchar, 995 colour varchar,
996 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, 1006 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
997 scamin, picrep, txtdsc, sordat, sorind, 1007 scamin, picrep, txtdsc, sordat, sorind,
998 0, colour, colpat, conrad, marsys, boyshp 1008 0, colour, colpat, conrad, marsys, boyshp
999 ) AS fairway_marks_boysaw) 1009 ) AS fairway_marks_boysaw)
1000 )) 1010 ))
1011 CREATE INDEX fairway_marks_boysaw_validity
1012 ON fairway_marks_boysaw USING GiST (validity)
1001 1013
1002 -- Additional attributes for IENC feature BOYSPP 1014 -- Additional attributes for IENC feature BOYSPP
1003 CREATE TABLE fairway_marks_boyspp ( 1015 CREATE TABLE fairway_marks_boyspp (
1004 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 1016 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1005 colour varchar, 1017 colour varchar,
1017 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, 1029 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
1018 scamin, picrep, txtdsc, sordat, sorind, 1030 scamin, picrep, txtdsc, sordat, sorind,
1019 0, colour, colpat, conrad, marsys, boyshp, catspm 1031 0, colour, colpat, conrad, marsys, boyshp, catspm
1020 ) AS fairway_marks_boyspp) 1032 ) AS fairway_marks_boyspp)
1021 )) 1033 ))
1034 CREATE INDEX fairway_marks_boyspp_validity
1035 ON fairway_marks_boyspp USING GiST (validity)
1022 1036
1023 -- Additional attributes for IENC features DAYMAR/daymar 1037 -- Additional attributes for IENC features DAYMAR/daymar
1024 CREATE TABLE fairway_marks_daymar ( 1038 CREATE TABLE fairway_marks_daymar (
1025 colour varchar, 1039 colour varchar,
1026 colpat varchar, 1040 colpat varchar,
1039 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, 1053 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
1040 scamin, picrep, txtdsc, sordat, sorind, 1054 scamin, picrep, txtdsc, sordat, sorind,
1041 colour, colpat, condtn, topshp, 0 1055 colour, colpat, condtn, topshp, 0
1042 ) AS fairway_marks_daymar_hydro) 1056 ) AS fairway_marks_daymar_hydro)
1043 )) 1057 ))
1058 CREATE INDEX fairway_marks_daymar_hydro_validity
1059 ON fairway_marks_daymar_hydro USING GiST (validity)
1044 1060
1045 CREATE TABLE fairway_marks_daymar_ienc ( 1061 CREATE TABLE fairway_marks_daymar_ienc (
1046 orient double precision, 1062 orient double precision,
1047 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY 1063 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
1048 ) INHERITS (fairway_marks_daymar) 1064 ) INHERITS (fairway_marks_daymar)
1054 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, 1070 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
1055 scamin, picrep, txtdsc, sordat, sorind, 1071 scamin, picrep, txtdsc, sordat, sorind,
1056 colour, colpat, condtn, topshp, orient, 0 1072 colour, colpat, condtn, topshp, orient, 0
1057 ) AS fairway_marks_daymar_ienc) 1073 ) AS fairway_marks_daymar_ienc)
1058 )) 1074 ))
1075 CREATE INDEX fairway_marks_daymar_ienc_validity
1076 ON fairway_marks_daymar_ienc USING GiST (validity)
1059 1077
1060 CREATE TABLE fairway_marks_daymar_dirimps ( 1078 CREATE TABLE fairway_marks_daymar_dirimps (
1061 fm_daymar_id int REFERENCES fairway_marks_daymar_ienc, 1079 fm_daymar_id int REFERENCES fairway_marks_daymar_ienc,
1062 dirimp smallint REFERENCES dirimps, 1080 dirimp smallint REFERENCES dirimps,
1063 PRIMARY KEY (fm_daymar_id, dirimp) 1081 PRIMARY KEY (fm_daymar_id, dirimp)
1090 scamin, picrep, txtdsc, sordat, sorind, 1108 scamin, picrep, txtdsc, sordat, sorind,
1091 0, colour, condtn, orient, catlit, exclit, litchr, litvis, 1109 0, colour, condtn, orient, catlit, exclit, litchr, litvis,
1092 mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status 1110 mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status
1093 ) AS fairway_marks_lights) 1111 ) AS fairway_marks_lights)
1094 )) 1112 ))
1113 CREATE INDEX fairway_marks_lights_validity
1114 ON fairway_marks_lights USING GiST (validity)
1095 1115
1096 -- Additional attributes for IENC feature RTPBCN 1116 -- Additional attributes for IENC feature RTPBCN
1097 CREATE TABLE fairway_marks_rtpbcn ( 1117 CREATE TABLE fairway_marks_rtpbcn (
1098 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 1118 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1099 condtn int, 1119 condtn int,
1109 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, 1129 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
1110 scamin, picrep, txtdsc, sordat, sorind, 1130 scamin, picrep, txtdsc, sordat, sorind,
1111 0, condtn, siggrp, catrtb, radwal 1131 0, condtn, siggrp, catrtb, radwal
1112 ) AS fairway_marks_rtpbcn) 1132 ) AS fairway_marks_rtpbcn)
1113 )) 1133 ))
1134 CREATE INDEX fairway_marks_rtpbcn_validity
1135 ON fairway_marks_rtpbcn USING GiST (validity)
1114 1136
1115 -- Additional attributes for IENC feature TOPMAR 1137 -- Additional attributes for IENC feature TOPMAR
1116 CREATE TABLE fairway_marks_topmar ( 1138 CREATE TABLE fairway_marks_topmar (
1117 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 1139 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1118 colour varchar, 1140 colour varchar,
1128 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, 1150 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
1129 scamin, picrep, txtdsc, sordat, sorind, 1151 scamin, picrep, txtdsc, sordat, sorind,
1130 0, colour, colpat, condtn, topshp 1152 0, colour, colpat, condtn, topshp
1131 ) AS fairway_marks_topmar) 1153 ) AS fairway_marks_topmar)
1132 )) 1154 ))
1155 CREATE INDEX fairway_marks_topmar_validity
1156 ON fairway_marks_topmar USING GiST (validity)
1133 1157
1134 -- Additional attributes for IENC feature NOTMRK 1158 -- Additional attributes for IENC feature NOTMRK
1135 CREATE TABLE fairway_marks_notmrk ( 1159 CREATE TABLE fairway_marks_notmrk (
1136 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 1160 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1137 condtn int, 1161 condtn int,
1156 scamin, picrep, txtdsc, sordat, sorind, 1180 scamin, picrep, txtdsc, sordat, sorind,
1157 0, condtn, marsys, orient, status, addmrk, catnmk, 1181 0, condtn, marsys, orient, status, addmrk, catnmk,
1158 disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw 1182 disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw
1159 ) AS fairway_marks_notmrk) 1183 ) AS fairway_marks_notmrk)
1160 )) 1184 ))
1185 CREATE INDEX fairway_marks_notmrk_validity
1186 ON fairway_marks_notmrk USING GiST (validity)
1161 1187
1162 CREATE TABLE fairway_marks_notmrk_dirimps ( 1188 CREATE TABLE fairway_marks_notmrk_dirimps (
1163 fm_notmrk_id int REFERENCES fairway_marks_notmrk, 1189 fm_notmrk_id int REFERENCES fairway_marks_notmrk,
1164 dirimp smallint REFERENCES dirimps, 1190 dirimp smallint REFERENCES dirimps,
1165 PRIMARY KEY (fm_notmrk_id, dirimp) 1191 PRIMARY KEY (fm_notmrk_id, dirimp)