From c22f919c83b6fd760947871d8edbe00ca112e59f Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Fri, 10 Oct 2025 16:55:00 +0000 Subject: [PATCH] Initial converson of follow_arrival table --- conversion/clean.sql | 28 ++++++++ conversion/load_data.sql | 140 ++++++++++++++++++++++++++++++++++++++- 2 files changed, 167 insertions(+), 1 deletion(-) diff --git a/conversion/clean.sql b/conversion/clean.sql index 23df67f..37fdd4e 100644 --- a/conversion/clean.sql +++ b/conversion/clean.sql @@ -232,3 +232,31 @@ UPDATE follow UPDATE follow SET fol_b_animid = UPPER(fol_b_animid) WHERE fol_b_animid <> UPPER(fol_b_animid); + +-- +-- follow_arrival +-- + +-- +-- Update the follow_arrival.fa_type_of_cycle +-- + +-- There must be no NULL fa_type_of_cycle values. Problem #42. +UPDATE follow_arrival + SET fa_type_of_cycle = 'MISS' + WHERE fa_type_of_cycle IS NULL; + +-- The desired code is "n/a", not "NA" +UPDATE follow_arrival + SET fa_type_of_cycle = 'n/a' + WHERE fa_type_of_cycle = 'NA'; + +-- Standardize the fa_data_source +UPDATE follow_arrival + SET fa_data_source = 'Brec' + WHERE fa_data_source = 'BREC' + OR fa_data_source = 'brec'; + +UPDATE follow_arrival + SET fa_data_source = 'Tiki' + WHERE fa_data_source = 'TikI'; diff --git a/conversion/load_data.sql b/conversion/load_data.sql index 36db860..ae17855 100644 --- a/conversion/load_data.sql +++ b/conversion/load_data.sql @@ -1,4 +1,5 @@ --- Copyright (C) 2023, 2024 The Meme Factory, Inc. http://www.karlpinc.com/ +-- Copyright (C) 2023, 2024, 2025 The Meme Factory, Inc. +-- http://www.karlpinc.com/ -- -- This program is free software: you can redistribute it and/or modify -- it under the terms of the GNU Affero General Public License as @@ -21,6 +22,25 @@ SET search_path TO sokwedb, codes, lib, clean, libconv; -- Support tables +-- arrival_sources +-- From the follow_arrival part of the MS Word doc for the MS Access db +INSERT INTO arrival_sources (code, description) + VALUES ('Tiki', 'Data came from Tiki (either typed or written in)') + , ('Tiki_Mom' + , 'Juvenile was assumed to be with their mother.' + || ' Duplicated mother’s arrival for juvenile.') + , ('Tiki_ID' + , 'Juvenile was present with another chimp (e.g. sister).' + || ' Used that chimp’s tiki arrival for juvenile.') + , ('Brec' + , 'Juvenile arrival reconstructed from Brec notes.'); + +-- certainties +-- From the MS Word chimp document +INSERT INTO certainties (certainty, description) + VALUES ('0', 'Chimp suspected to be nearby (maybe hidden for the moment)') + , ('1', 'Chimp definitely seen'); + -- comm_membs_sources -- Obtain these from community_membership @@ -49,6 +69,17 @@ insert into comm_ids (commid, name, notes, membcriteria) values ('KL', 'Kalande', '', '') , ('KL_KK', 'Kasekela/kalande', '', ''); +-- cycle_states +INSERT INTO cycle_states (code, description) + VALUES ('0', 'Not swollen') + , ('0.25', '1/4 swollen') + , ('0.5', '1/2 swollen') + , ('0.75', '3/4 swollen') + , ('1', 'Fully swollen') + , ('U', 'Adolescent swelling') + , ('n/a', 'Male') + , ('MISS', 'Missing data'); -- problem #42 + -- departtypes INSERT INTO departtypes (departtype, description) VALUES ('D', 'Death') @@ -79,6 +110,12 @@ INSERT INTO obs_periods (period, description) , ('PM', 'Afternoon') , ('UNK', 'Unknown'); +-- role_codes +INSERT INTO role_codes (role, description) + VALUES ('Actor', 'The initator in a directed dyadic interaction') + , ('Actee', 'The receipient in a directed dyadic interaction') + , ('Solo', 'A participant in an event, non-directed when there are mulitple participants'); + -- Data tables -- biography -> biography_data @@ -465,6 +502,107 @@ SELECT OR (fol_date = '2015-01-17' AND fol_b_animid = 'SI'); +-- +-- follow_arrival +-- + +-- The processing of follow_arrival is done row-by-row. +-- This makes it possible to associate the correct events +-- with the correct roles, etc., which might not be possible +-- if, say, events are created without roles and then there +-- are multiple arriving chimps that each need to be related +-- to "their own" event. In any case, we are optimizing for +-- clarity and accuracy, not speed, so this approach works +-- best. + +DO $$ +DECLARE + this_fa clean.follow_arrival%ROWTYPE; + related_fid follows.fid%TYPE; + +BEGIN + + FOR this_fa IN + SELECT * + FROM clean.follow_arrival + WHERE EXISTS -- Problem #43 + (SELECT 1 + FROM follows + WHERE follows.focal = follow_arrival.fa_fol_b_focal_animid + AND follows.date = follow_arrival.fa_fol_date) + ORDER BY follow_arrival.fa_fol_date + , follow_arrival.fa_fol_b_focal_animid + , follow_arrival.fa_b_arr_animid + LOOP + -- Which follow belongs to the follow_arrival? + SELECT follows.fid + INTO STRICT related_fid + FROM follows + WHERE follows.focal = this_fa.fa_fol_b_focal_animid + AND follows.date = this_fa.fa_fol_date; + + -- follow_arrival -> EVENTS + INSERT INTO events ( + fid + , behavior + , start + , stop + , certainty + , notes) + VALUES ( + related_fid + , 'ARR' + , this_fa.fa_time_start + , this_fa.fa_time_end + , this_fa.fa_type_of_certainty + , ''); + + -- follow_arrival -> ROLES + INSERT INTO roles ( + eid + , role + , participant) + VALUES ( + currval('events_eid_seq') + , 'Solo' + , this_fa.fa_b_arr_animid); + + -- follow_arrival -> ARRIVALS + INSERT INTO arrivals ( + eid + , neststart + , nestend + , cycle + , datasource) + VALUES ( + currval('events_eid_seq') + , CASE + WHEN this_fa.fa_type_of_nesting IS NOT DISTINCT FROM 0 + THEN FALSE + WHEN this_fa.fa_type_of_nesting IS NOT DISTINCT FROM 1 + THEN TRUE + WHEN this_fa.fa_type_of_nesting IS NOT DISTINCT FROM 2 + THEN FALSE + WHEN this_fa.fa_type_of_nesting IS NOT DISTINCT FROM 3 + THEN TRUE + END + , CASE + WHEN this_fa.fa_type_of_nesting IS NOT DISTINCT FROM 0 + THEN FALSE + WHEN this_fa.fa_type_of_nesting IS NOT DISTINCT FROM 1 + THEN FALSE + WHEN this_fa.fa_type_of_nesting IS NOT DISTINCT FROM 2 + THEN TRUE + WHEN this_fa.fa_type_of_nesting IS NOT DISTINCT FROM 3 + THEN TRUE + END + , this_fa.fa_type_of_cycle + , this_fa.fa_data_source); + + END LOOP; +END; +$$; -- follow_arrival DO block + -- -- Finish -- -- 2.34.1