Mercurial > gemma
comparison pkg/imports/bn.go @ 4069:12f476e91c70 historization_ng
Adapted bn-import to new historization model.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Thu, 25 Jul 2019 12:54:30 +0200 |
parents | aa7bede70b96 |
children | 4332b9e26e2f |
comparison
equal
deleted
inserted
replaced
4068:76482935b6e5 | 4069:12f476e91c70 |
---|---|
52 (SELECT b FROM bounds ORDER BY b USING >~ FETCH FIRST ROW ONLY)) AS r) | 52 (SELECT b FROM bounds ORDER BY b USING >~ FETCH FIRST ROW ONLY)) AS r) |
53 INSERT INTO waterway.bottlenecks ( | 53 INSERT INTO waterway.bottlenecks ( |
54 bottleneck_id, | 54 bottleneck_id, |
55 validity, | 55 validity, |
56 gauge_location, | 56 gauge_location, |
57 gauge_validity, | |
58 objnam, | 57 objnam, |
59 nobjnm, | 58 nobjnm, |
60 stretch, | 59 stretch, |
61 area, | 60 area, |
62 rb, | 61 rb, |
64 responsible_country, | 63 responsible_country, |
65 revisiting_time, | 64 revisiting_time, |
66 limiting, | 65 limiting, |
67 date_info, | 66 date_info, |
68 source_organization | 67 source_organization |
69 ) SELECT | 68 ) VALUES ( |
70 $1, | 69 $1, |
71 validity * $2, -- intersections with gauge validity ranges | 70 $2::tstzrange, |
72 location, | 71 isrs_fromText($3), |
73 validity, | |
74 $4, | 72 $4, |
75 $5, | 73 $5, |
76 (SELECT r FROM r), | 74 (SELECT r FROM r), |
77 ISRSrange_area( | 75 ISRSrange_area( |
78 ISRSrange_axis((SELECT r FROM r), | 76 ISRSrange_axis((SELECT r FROM r), |
84 $10, | 82 $10, |
85 $11, | 83 $11, |
86 $12, | 84 $12, |
87 $13, | 85 $13, |
88 $14 | 86 $14 |
89 FROM waterway.gauges | 87 ) |
90 WHERE location = isrs_fromText($3) AND validity && $2 | |
91 RETURNING id | 88 RETURNING id |
92 ` | 89 ` |
93 | 90 |
94 updateBottleneckSQL = ` | 91 updateBottleneckSQL = ` |
95 WITH | 92 WITH |
99 (SELECT b FROM bounds ORDER BY b USING >~ FETCH FIRST ROW ONLY)) AS r) | 96 (SELECT b FROM bounds ORDER BY b USING >~ FETCH FIRST ROW ONLY)) AS r) |
100 UPDATE waterway.bottlenecks SET ( | 97 UPDATE waterway.bottlenecks SET ( |
101 bottleneck_id, | 98 bottleneck_id, |
102 validity, | 99 validity, |
103 gauge_location, | 100 gauge_location, |
104 gauge_validity, | |
105 objnam, | 101 objnam, |
106 nobjnm, | 102 nobjnm, |
107 stretch, | 103 stretch, |
108 area, | 104 area, |
109 rb, | 105 rb, |
111 responsible_country, | 107 responsible_country, |
112 revisiting_time, | 108 revisiting_time, |
113 limiting, | 109 limiting, |
114 date_info, | 110 date_info, |
115 source_organization | 111 source_organization |
116 ) = ( SELECT | 112 ) = ( |
117 $2, | 113 $2, |
118 validity * $3, -- intersections with gauge validity ranges | 114 $3::tstzrange, |
119 location, | 115 isrs_fromText($4), |
120 validity, | |
121 $5, | 116 $5, |
122 $6, | 117 $6, |
123 (SELECT r FROM r), | 118 (SELECT r FROM r), |
124 ISRSrange_area( | 119 ISRSrange_area( |
125 ISRSrange_axis((SELECT r FROM r), | 120 ISRSrange_axis((SELECT r FROM r), |
131 $11, | 126 $11, |
132 $12::smallint, | 127 $12::smallint, |
133 $13, | 128 $13, |
134 $14::timestamptz, | 129 $14::timestamptz, |
135 $15 | 130 $15 |
136 FROM waterway.gauges | 131 ) |
137 WHERE location = isrs_fromText($4) AND validity && $3 ) | |
138 WHERE id=$1 | 132 WHERE id=$1 |
139 RETURNING id | 133 RETURNING id |
140 ` | 134 ` |
141 | 135 |
142 findExactMatchBottleneckSQL = ` | 136 findExactMatchBottleneckSQL = ` |
148 SELECT id FROM waterway.bottlenecks | 142 SELECT id FROM waterway.bottlenecks |
149 WHERE ( | 143 WHERE ( |
150 bottleneck_id, | 144 bottleneck_id, |
151 validity, | 145 validity, |
152 gauge_location, | 146 gauge_location, |
153 gauge_validity, | |
154 objnam, | 147 objnam, |
155 nobjnm, | 148 nobjnm, |
156 stretch, | 149 stretch, |
157 rb, | 150 rb, |
158 lb, | 151 lb, |
162 date_info, | 155 date_info, |
163 source_organization, | 156 source_organization, |
164 staging_done | 157 staging_done |
165 ) = ( SELECT | 158 ) = ( SELECT |
166 $1, | 159 $1, |
167 validity * $2, -- intersections with gauge validity ranges | 160 $2::tstzrange, |
168 location, | 161 isrs_fromText($3), |
169 validity, | |
170 $4, | 162 $4, |
171 $5, | 163 $5, |
172 (SELECT r FROM r), | 164 (SELECT r FROM r), |
173 $8, | 165 $8, |
174 $9, | 166 $9, |
176 $11::smallint, | 168 $11::smallint, |
177 $12, | 169 $12, |
178 $13::timestamptz, | 170 $13::timestamptz, |
179 $14, | 171 $14, |
180 true | 172 true |
181 FROM waterway.gauges | |
182 WHERE location = isrs_fromText($3) AND validity && $2 | |
183 ) | 173 ) |
184 ` | 174 ` |
185 | 175 |
186 findMatchBottleneckSQL = ` | 176 findMatchBottleneckSQL = ` |
187 SELECT id FROM waterway.bottlenecks | 177 SELECT id FROM waterway.bottlenecks |
188 WHERE ( | 178 WHERE ( |
189 bottleneck_id, | 179 bottleneck_id, |
190 validity, | 180 validity, |
191 staging_done | 181 staging_done |
192 ) = ( SELECT | 182 ) = ( |
193 $1, | 183 $1, |
194 validity * $2, -- intersections with gauge validity ranges | 184 $2::tstzrange, |
195 true | 185 true |
196 FROM waterway.gauges | |
197 WHERE location = isrs_fromText($3) AND validity && $2 | |
198 ) | 186 ) |
199 ` | 187 ` |
188 // FIXME: Is this still neede wtih the new simplified historization | |
189 // model? My intuition is: no it isn't and should be removed, but we | |
190 // should double check before doing so... [sw] | |
191 // | |
200 // Alignment with gauge validity might have generated new entries | 192 // Alignment with gauge validity might have generated new entries |
201 // for the same time range. Thus, remove the old ones | 193 // for the same time range. Thus, remove the old ones |
202 deleteObsoleteBNSQL = ` | 194 deleteObsoleteBNSQL = ` |
203 DELETE FROM waterway.bottlenecks | 195 DELETE FROM waterway.bottlenecks |
204 WHERE bottleneck_id = $1 AND validity <@ $2 AND id <> ALL($3) | 196 WHERE bottleneck_id = $1 AND validity <@ $2 AND id <> ALL($3) |
529 // exists: | 521 // exists: |
530 var existing_bn_id *int64 | 522 var existing_bn_id *int64 |
531 err = findMatchingBNStmt.QueryRowContext(ctx, | 523 err = findMatchingBNStmt.QueryRowContext(ctx, |
532 bn.Bottleneck_id, | 524 bn.Bottleneck_id, |
533 &validity, | 525 &validity, |
534 bn.Fk_g_fid, | |
535 ).Scan(&existing_bn_id) | 526 ).Scan(&existing_bn_id) |
536 switch { | 527 switch { |
537 case err == sql.ErrNoRows: | 528 case err == sql.ErrNoRows: |
538 existing_bn_id = nil | 529 existing_bn_id = nil |
539 case err != nil: | 530 case err != nil: |