search

akrherz / pyIEM

A collection of python support things that make my weather processing work, work.
MIT License
41 stars 9 forks source link

SBW storage should ignore CAN segment in case of two segment product update #888

Closed akrherz closed 4 months ago

akrherz commented 4 months ago

Consider the present SBW series storage for MEG SV.W 53

product_id polygon_begin polygon_end status
202404082321-KMEG-WUUS54-SVRMEG 2024-04-08 18:21:00-05 2024-04-08 18:41:00-05 NEW
202404082341-KMEG-WWUS54-SVSMEG 2024-04-08 18:41:00-05 2024-04-08 18:41:00-05 CAN
202404082341-KMEG-WWUS54-SVSMEG 2024-04-08 18:41:00-05 2024-04-08 18:50:00-05 CON
202404082350-KMEG-WWUS54-SVSMEG 2024-04-08 18:50:00-05 2024-04-08 18:50:00-05 CAN

so the 2341z SVS contains two segments with ye old CAN polygon only existing because a UGC is dropped (that's a different rant by me). Anyway, this CAN entry in the database is likely of no usage and should just be dropped from processing.

akrherz commented 4 months ago

SQL run for each year to cull the archive

with data as (
select count(*), max(case when status = 'CAN' then ctid else null end) as del_ctid, 
max(case when status = 'CAN' then 1 else 0 end) as has_can, 
max(case when status = 'CON' then 1 else 0 end) as has_con, wfo, eventid, phenomena, significance, product_id
from sbw_2024 GROUP by wfo, eventid, phenomena, significance, product_id)
delete from sbw_2024 s USING data d WHERE s.ctid = d.del_ctid  and   has_con = 1 and has_can = 1