Mercurial > gemma

comparison pkg/imports/bn.go @ 3698:063a1883b5cb

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Detect and handle unchanged BN during import.
author Sascha Wilde <wilde@intevation.de>
date Wed, 19 Jun 2019 11:20:47 +0200
parents c9e1848a516a
children b07511ff859e
comparison
equal deleted inserted replaced
3697:9ef98342ffe7 3698:063a1883b5cb
102 date_info = EXCLUDED.date_info, 102 date_info = EXCLUDED.date_info,
103 source_organization = EXCLUDED.source_organization 103 source_organization = EXCLUDED.source_organization
104 RETURNING id 104 RETURNING id
105 ` 105 `
106 106
107 findExactMatchBottleneckSQL = `
108 WITH
109 bounds (b) AS (VALUES (isrs_fromText($6)), (isrs_fromText($7))),
110 r AS (SELECT isrsrange(
111 (SELECT b FROM bounds ORDER BY b USING <~ FETCH FIRST ROW ONLY),
112 (SELECT b FROM bounds ORDER BY b USING >~ FETCH FIRST ROW ONLY)) AS r)
113 SELECT id FROM waterway.bottlenecks
114 WHERE (
115 bottleneck_id,
116 validity,
117 gauge_location,
118 gauge_validity,
119 objnam,
120 nobjnm,
121 stretch,
122 area,
123 rb,
124 lb,
125 responsible_country,
126 revisiting_time,
127 limiting,
128 date_info,
129 source_organization,
130 staging_done
131 ) = ( SELECT
132 $1,
133 validity * $2, -- intersections with gauge validity ranges
134 location,
135 validity,
136 $4,
137 $5,
138 (SELECT r FROM r),
139 ISRSrange_area(
140 ISRSrange_axis((SELECT r FROM r),
141 $15),
142 (SELECT ST_Collect(CAST(area AS geometry))
143 FROM waterway.waterway_area)),
144 $8,
145 $9,
146 $10,
147 $11::smallint,
148 $12,
149 $13::timestamptz,
150 $14,
151 true
152 FROM waterway.gauges
153 WHERE location = isrs_fromText($3) AND validity && $2
154 )
155 `
156
107 // Alignment with gauge validity might have generated new entries 157 // Alignment with gauge validity might have generated new entries
108 // for the same time range. Thus, remove the old ones 158 // for the same time range. Thus, remove the old ones
109 deleteObsoleteBNSQL = ` 159 deleteObsoleteBNSQL = `
110 DELETE FROM waterway.bottlenecks 160 DELETE FROM waterway.bottlenecks
111 WHERE bottleneck_id = $1 AND validity <@ $2 AND id <> ALL($3) 161 WHERE bottleneck_id = $1 AND validity <@ $2 AND id <> ALL($3)
234 return nil, err 284 return nil, err
235 } 285 }
236 286
237 feedback.Info("Found %d bottlenecks for import", len(bns)) 287 feedback.Info("Found %d bottlenecks for import", len(bns))
238 288
239 var insertStmt, deleteObsoleteBNStmt, fixValidityStmt, 289 var insertStmt, findExactMatchingBNStmt, deleteObsoleteBNStmt,
240 deleteMaterialStmt, insertMaterialStmt, trackStmt *sql.Stmt 290 fixValidityStmt, deleteMaterialStmt, insertMaterialStmt,
291 trackStmt *sql.Stmt
241 292
242 for _, x := range []struct { 293 for _, x := range []struct {
243 sql string 294 sql string
244 stmt **sql.Stmt 295 stmt **sql.Stmt
245 }{ 296 }{
246 {insertBottleneckSQL, &insertStmt}, 297 {insertBottleneckSQL, &insertStmt},
298 {findExactMatchBottleneckSQL, &findExactMatchingBNStmt},
247 {deleteObsoleteBNSQL, &deleteObsoleteBNStmt}, 299 {deleteObsoleteBNSQL, &deleteObsoleteBNStmt},
248 {fixBNValiditySQL, &fixValidityStmt}, 300 {fixBNValiditySQL, &fixValidityStmt},
249 {deleteBottleneckMaterialSQL, &deleteMaterialStmt}, 301 {deleteBottleneckMaterialSQL, &deleteMaterialStmt},
250 {insertBottleneckMaterialSQL, &insertMaterialStmt}, 302 {insertBottleneckMaterialSQL, &insertMaterialStmt},
251 {trackImportSQL, &trackStmt}, 303 {trackImportSQL, &trackStmt},
262 feedback.Info("Tolerance used to snap waterway axis: %g", tolerance) 314 feedback.Info("Tolerance used to snap waterway axis: %g", tolerance)
263 315
264 for _, bn := range bns { 316 for _, bn := range bns {
265 if err := storeBottleneck( 317 if err := storeBottleneck(
266 ctx, importID, conn, feedback, bn, &nids, tolerance, 318 ctx, importID, conn, feedback, bn, &nids, tolerance,
267 insertStmt, deleteObsoleteBNStmt, fixValidityStmt, 319 insertStmt, findExactMatchingBNStmt, deleteObsoleteBNStmt,
268 deleteMaterialStmt, insertMaterialStmt, trackStmt); err != nil { 320 fixValidityStmt, deleteMaterialStmt, insertMaterialStmt,
321 trackStmt); err != nil {
269 return nil, err 322 return nil, err
270 } 323 }
271 } 324 }
272 if len(nids) == 0 { 325 if len(nids) == 0 {
273 return nil, UnchangedError("No new bottlenecks inserted") 326 return nil, UnchangedError("No new bottlenecks inserted")
289 conn *sql.Conn, 342 conn *sql.Conn,
290 feedback Feedback, 343 feedback Feedback,
291 bn *ifbn.BottleNeckType, 344 bn *ifbn.BottleNeckType,
292 nids *[]string, 345 nids *[]string,
293 tolerance float64, 346 tolerance float64,
294 insertStmt, deleteObsoleteBNStmt, fixValidityStmt, 347 insertStmt, findExactMatchingBNStmt, deleteObsoleteBNStmt,
295 deleteMaterialStmt, insertMaterialStmt, trackStmt *sql.Stmt, 348 fixValidityStmt, deleteMaterialStmt, insertMaterialStmt,
349 trackStmt *sql.Stmt,
296 ) error { 350 ) error {
297 feedback.Info("Processing %s (%s)", bn.OBJNAM, bn.Bottleneck_id) 351 feedback.Info("Processing %s (%s)", bn.OBJNAM, bn.Bottleneck_id)
298 352
299 var tfrom, tto pgtype.Timestamptz 353 var tfrom, tto pgtype.Timestamptz
300 var uBound pgtype.BoundType 354 var uBound pgtype.BoundType
387 materials = append(materials, string(*material)) 441 materials = append(materials, string(*material))
388 } 442 }
389 } 443 }
390 } 444 }
391 445
446 // Check if an bottleneck identical to the one we would insert already
447 // exists:
448 bns, err := findExactMatchingBNStmt.QueryContext(ctx,
449 bn.Bottleneck_id,
450 &validity,
451 bn.Fk_g_fid,
452 bn.OBJNAM,
453 bn.NOBJNM,
454 bn.From_ISRS, bn.To_ISRS,
455 rb,
456 lb,
457 country,
458 revisitingTime,
459 limiting,
460 bn.Date_Info,
461 bn.Source,
462 tolerance,
463 )
464
465 if err != nil {
466 return err
467 }
468 defer bns.Close()
469 if bns.Next() {
470 feedback.Info("unchanged")
471 return nil
472 }
473
392 tx, err := conn.BeginTx(ctx, nil) 474 tx, err := conn.BeginTx(ctx, nil)
393 if err != nil { 475 if err != nil {
394 return err 476 return err
395 } 477 }
396 defer tx.Rollback() 478 defer tx.Rollback()
397 479
398 var bnIds []int64 480 var bnIds []int64
399 bns, err := tx.StmtContext(ctx, insertStmt).QueryContext(ctx, 481 bns, err = tx.StmtContext(ctx, insertStmt).QueryContext(ctx,
400 bn.Bottleneck_id, 482 bn.Bottleneck_id,
401 &validity, 483 &validity,
402 bn.Fk_g_fid, 484 bn.Fk_g_fid,
403 bn.OBJNAM, 485 bn.OBJNAM,
404 bn.NOBJNM, 486 bn.NOBJNM,