From b5a770953b1a32b8b3b606cb1bdd0583a58b8a16 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Thu, 2 Oct 2025 22:18:05 +0000 Subject: [PATCH] Convert follow -> follow_observers There are a number of references to problem numbers, where we automate fixes. These have not been approved by the PIs and may change. --- conversion/load_data.sql | 220 +++++++++++++++++++++++++++++++++++++++ 1 file changed, 220 insertions(+) diff --git a/conversion/load_data.sql b/conversion/load_data.sql index 91abd99..207cc9f 100644 --- a/conversion/load_data.sql +++ b/conversion/load_data.sql @@ -73,6 +73,12 @@ INSERT INTO people (person, name, description, active) FROM clean.people; +-- obs_periods +INSERT INTO obs_periods (period, description) + VALUES ('AM', 'Morning') + , ('PM', 'Afternoon') + , ('UNK', 'Unknown'); + -- Data tables -- biography -> biography_data @@ -275,6 +281,220 @@ SELECT upper( -- problem #31) FROM clean.follow ORDER BY fol_b_animid, fol_date; +-- +-- follow -> FOLLOW_OBSERVERS +-- + +-- Load all the new people into PEOPLE +INSERT INTO people (person, name, description, active) + WITH new_people AS ( + SELECT BTRIM(follow.fol_am_observer_1) AS person + FROM clean.follow + WHERE follow.fol_am_observer_1 IS NOT NULL + GROUP BY BTRIM(follow.fol_am_observer_1) + UNION + SELECT BTRIM(follow.fol_am_observer_2) AS person + FROM clean.follow + WHERE follow.fol_am_observer_2 IS NOT NULL + GROUP BY BTRIM(follow.fol_am_observer_2) + UNION + SELECT BTRIM(follow.fol_pm_observer_1) AS person + FROM clean.follow + WHERE follow.fol_pm_observer_1 IS NOT NULL + GROUP BY BTRIM(follow.fol_pm_observer_1) + UNION + SELECT BTRIM(follow.fol_pm_observer_2) AS person + FROM clean.follow + WHERE follow.fol_pm_observer_2 IS NOT NULL + GROUP BY BTRIM(follow.fol_pm_observer_2) + ) + SELECT new_people.person, new_people.person, '', TRUE + FROM new_people + WHERE new_people.person <> '' + GROUP BY new_people.person + ON CONFLICT (person) + DO NOTHING; + +-- AM rows +INSERT INTO follow_observers ( + fid +, period +, obs_brec +, obs_tiki) +WITH obs AS ( +SELECT upper( -- problem #31 + btrim( -- problem #32 + follow.fol_b_animid)) AS fol_b_animid + , follow.fol_date + , CASE + WHEN BTRIM(follow.fol_am_observer_1) IS NOT DISTINCT FROM '' + THEN NULL + ELSE + BTRIM(follow.fol_am_observer_1) + END AS am_obs1 + , CASE + WHEN BTRIM(follow.fol_am_observer_2) IS NOT DISTINCT FROM '' + THEN NULL + ELSE + BTRIM(follow.fol_am_observer_2) + END AS am_obs2 + , CASE + WHEN BTRIM(follow.fol_pm_observer_1) IS NOT DISTINCT FROM '' + THEN NULL + ELSE + BTRIM(follow.fol_pm_observer_1) + END AS pm_obs1 + , CASE + WHEN BTRIM(follow.fol_pm_observer_2) IS NOT DISTINCT FROM '' + THEN NULL + ELSE + BTRIM(follow.fol_pm_observer_2) + END AS pm_obs2 + FROM clean.follow +) +SELECT + (SELECT follows.fid + FROM follows + WHERE follows.focal = obs.fol_b_animid + AND follows.date = obs.fol_date) +, 'AM' +, COALESCE(obs.am_obs1, 'NONE') +, COALESCE(obs.am_obs2, 'NONE') + FROM obs + -- Don't try to load follows that haven't been converted + JOIN follows ON (follows.focal = obs.fol_b_animid + AND follows.date = obs.fol_date) + WHERE COALESCE(obs.am_obs1, obs.am_obs2) + IS NOT NULL; + +-- PM rows +INSERT INTO follow_observers ( + fid +, period +, obs_brec +, obs_tiki) +WITH obs AS ( +SELECT upper( -- problem #31 + btrim( -- problem #32 + follow.fol_b_animid)) AS fol_b_animid + , follow.fol_date + , CASE + WHEN BTRIM(follow.fol_am_observer_1) IS NOT DISTINCT FROM '' + THEN NULL + ELSE + BTRIM(follow.fol_am_observer_1) + END AS am_obs1 + , CASE + WHEN BTRIM(follow.fol_am_observer_2) IS NOT DISTINCT FROM '' + THEN NULL + ELSE + BTRIM(follow.fol_am_observer_2) + END AS am_obs2 + , CASE + WHEN BTRIM(follow.fol_pm_observer_1) IS NOT DISTINCT FROM '' + THEN NULL + ELSE + BTRIM(follow.fol_pm_observer_1) + END AS pm_obs1 + , CASE + WHEN BTRIM(follow.fol_pm_observer_2) IS NOT DISTINCT FROM '' + THEN NULL + ELSE + BTRIM(follow.fol_pm_observer_2) + END AS pm_obs2 + FROM clean.follow +) +SELECT + (SELECT follows.fid + FROM follows + WHERE follows.focal = obs.fol_b_animid + AND follows.date = obs.fol_date) +, 'PM' +, COALESCE(obs.pm_obs1, 'NONE') +, COALESCE(obs.pm_obs2, 'NONE') + FROM obs + -- Don't try to load follows that haven't been converted + JOIN follows ON (follows.focal = obs.fol_b_animid + AND follows.date = obs.fol_date) + WHERE COALESCE(obs.pm_obs1, obs.pm_obs2) + IS NOT NULL; + +-- The UNK time period +INSERT INTO follow_observers ( + fid +, period +, obs_brec +, obs_tiki) +SELECT + (SELECT follows.fid + FROM follows + WHERE follows.focal = upper( -- problem #31 + btrim( -- problem #32 + follow.fol_b_animid)) + AND follows.date = follow.fol_date) +, 'UNK' +, 'UNK' +, 'UNK' + FROM clean.follow + WHERE + -- Problem #37 + (fol_date = '1974-01-10' AND fol_b_animid = 'SW') + OR (fol_date = '1974-01-18' AND fol_b_animid = 'PL') + OR (fol_date = '1974-08-13' AND fol_b_animid = 'PM') + OR (fol_date = '1974-12-18' AND fol_b_animid = 'HG') + OR (fol_date = '1975-01-08' AND fol_b_animid = 'NV') + OR (fol_date = '1975-01-15' AND fol_b_animid = 'ST') + OR (fol_date = '1975-01-16' AND fol_b_animid = 'FB') + OR (fol_date = '1975-02-05' AND fol_b_animid = 'GB') + OR (fol_date = '1975-04-20' AND fol_b_animid = 'LB') + OR (fol_date = '1977-08-03' AND fol_b_animid = 'ML') + OR (fol_date = '1977-09-21' AND fol_b_animid = 'ML') + OR (fol_date = '1977-12-02' AND fol_b_animid = 'HR') + OR (fol_date = '1978-04-14' AND fol_b_animid = 'SH') + OR (fol_date = '1978-05-16' AND fol_b_animid = 'HM') + OR (fol_date = '1978-06-05' AND fol_b_animid = 'FG') + OR (fol_date = '1978-06-28' AND fol_b_animid = 'MU') + OR (fol_date = '1978-07-05' AND fol_b_animid = 'FF') + OR (fol_date = '1978-07-06' AND fol_b_animid = 'FG') + OR (fol_date = '1978-07-10' AND fol_b_animid = 'GB') + OR (fol_date = '1978-07-24' AND fol_b_animid = 'GB') + OR (fol_date = '1978-08-11' AND fol_b_animid = 'GB') + OR (fol_date = '1978-12-30' AND fol_b_animid = 'EV') + OR (fol_date = '1979-03-02' AND fol_b_animid = 'HR') + OR (fol_date = '1979-03-14' AND fol_b_animid = 'FG') + OR (fol_date = '1979-04-14' AND fol_b_animid = 'FG') + OR (fol_date = '1979-04-26' AND fol_b_animid = 'SH') + OR (fol_date = '1980-10-27' AND fol_b_animid = 'ML') + OR (fol_date = '1980-10-31' AND fol_b_animid = 'ML') + OR (fol_date = '1981-07-22' AND fol_b_animid = 'MF') + OR (fol_date = '1981-07-26' AND fol_b_animid = 'MU') + OR (fol_date = '1981-07-27' AND fol_b_animid = 'MF') + OR (fol_date = '1981-08-17' AND fol_b_animid = 'MF') + OR (fol_date = '1981-08-18' AND fol_b_animid = 'MF') + OR (fol_date = '1982-02-09' AND fol_b_animid = 'EV') + OR (fol_date = '1982-08-13' AND fol_b_animid = 'ML') + OR (fol_date = '1984-02-08' AND fol_b_animid = 'FF') + OR (fol_date = '1984-05-27' AND fol_b_animid = 'GB') + OR (fol_date = '1984-05-28' AND fol_b_animid = 'GM') + OR (fol_date = '1984-06-06' AND fol_b_animid = 'ST') + OR (fol_date = '1984-08-28' AND fol_b_animid = 'GM') + OR (fol_date = '1986-03-20' AND fol_b_animid = 'FD') + OR (fol_date = '1987-09-19' AND fol_b_animid = 'PI') + OR (fol_date = '1988-07-21' AND fol_b_animid = 'ME') + OR (fol_date = '1988-09-06' AND fol_b_animid = 'GB') + OR (fol_date = '1989-01-09' AND fol_b_animid = 'AL') + OR (fol_date = '1993-04-01' AND fol_b_animid = 'PF') + OR (fol_date = '1995-05-19' AND fol_b_animid = 'TB') + OR (fol_date = '1998-07-13' AND fol_b_animid = 'WL') + OR (fol_date = '1998-09-28' AND fol_b_animid = 'SL') + OR (fol_date = '1999-01-03' AND fol_b_animid = 'FF') + OR (fol_date = '2012-08-27' AND fol_b_animid = 'KON') + OR (fol_date = '2014-10-27' AND fol_b_animid = 'WEM') + OR (fol_date = '2014-11-23' AND fol_b_animid = 'WEM') + OR (fol_date = '2014-12-23' AND fol_b_animid = 'EZA') + OR (fol_date = '2015-01-05' AND fol_b_animid = 'VAN') + OR (fol_date = '2015-01-17' AND fol_b_animid = 'SI'); + -- -- Finish -- 2.34.1