DYADS

Each row represents a dyadic interaction between two individuals. Having both individuals appear in one row can make the data eaiser to work with.

The view puts the individual performing an action and the individual receiving the action in separate columns. There is a flag to indicate whether the action is mutual; when TRUE ,there is not meaning to the placement of the interacting individuals into these two columns.

This view extends the OBS view with columns for the individual performing the action and the individual receiving the action.

The DYADS view can be a core component when querying dyadic interactions.

For an example use of the DYADS view, see the PANTGRUNTS_VIEW documentation. It joins the DYADS view with the PANTGRUNTS table to provide a more-or-less complete report on pantgrunt data. It uses DYADS to supply the date, time, and participants and adds to that the detailed data on pantgrunts found in PANTGRUNTS. Queries similar to the SELECT statement found within the PANTGRUNTS_VIEW definition can be written to report on the other behaviors recorded as dyadic interactions – groomings, matings, aggressions, etc.

Definition

CREATE OR REPLACE VIEW dyads (
  wid
 ,date
 ,focal
 ,type
 ,commid
 ,eid
 ,behavior
 ,start
 ,stop
 ,certainty
 ,actor_pid
 ,actor
 ,recipient_pid
 ,recipient
 ,twosided
 ,notes
 ,event_notes
  )
  AS
    SELECT
      obs.wid
     ,obs.date
     ,obs.focal
     ,obs.type
     ,obs.commid
     ,obs.eid
     ,obs.behavior
     ,obs.start
     ,obs.stop
     ,obs.certainty
     ,actors.pid AS actor_pid
     ,actors.participant AS actor
     ,recipients.pid AS recipient_pid
     ,recipients.participant AS recipient
     ,CASE
        WHEN actors.role = 'Mutual' THEN
          TRUE
        ELSE
          FALSE
      END AS twosided
     ,obs.notes
     ,obs.event_notes
     FROM obs
       JOIN roles AS actors
            ON (actors.eid = obs.eid
                AND (actors.role = 'Actor'
                     OR actors.role = 'Mutual'))
       JOIN roles AS recipients
            ON (recipients.eid = obs.eid
                AND (recipients.role = 'Actee'
                     OR recipients.role = 'Mutual'))
     WHERE (actors.role = 'Actor'
            OR -- Without further conditions, when dyads are mutual,
               -- the two individuals appear as 4 rows: paired, paired
               -- in reverse order, and each matched with themselves.
               -- Assure consistent placement, among query executions,
               -- in the actor or recipient columns.
               actors.pid < recipients.pid);

ER Diagram

DYADS

Columns of the DYADS View

The columns of the DYADS view

Column	From	Description
WID	WATCHES.WID	Identifier of the related WATCHES row
Date	WATCHES.Date	Date of the event
Focal	WATCHES.Focal	Focal of follow, or focal of a non-existant follow, or an un-interesting AnimID
Type	WATCHES.Type	Category of observation, often determining the data collection protocol: follow, feeding station attendance, groom scans, etc.
CommID	WATCHES.CommID	The community identifier associated with the Date/Focal/Type
EID	EVENTS.EID	Identifier of the EVENTS row
Behavior	EVENTS.Behavior	Code designating the type of event observed
Start	EVENTS.Start	Time the event started (inclusive)
Stop	EVENTS.Stop	Time the event finished (inclusive)
Certainty	EVENTS.Certainty	Certainty of the event observation, when meaningful
Actor_PID	ROLES.PID	Identifier of the ROLES row containing the individual performing the action, or mutually acting
Actor	ROLES.Participant	The BIOGRAPHY_DATA.AnimID of the individual performing the action, or mutually actintg
Recipient	ROLES.Participant	The BIOGRAPHY_DATA.AnimID of the individual receiving the action, or mutually acting
Recipient_PID	ROLES.PID	Identifier of the ROLES row containig the individual receiving the action, or mutually acting
TwoSided	An expression based on ROLES.Role	Boolean, TRUE when both the Actor and the Recipient were performing the action
Notes	WATCHES.Notes	Textual notes on the observation for the Date/Focal/Type
Event_Notes	EVENTS.Notes	Textual notes on the event

Operations Allowed

INSERT

INSERTing a row into DYADS inserts two rows into ROLES. One row may also be inserted into EVENTS, and one may be inserted into WATCHES.

The columns EID, WID, Focal, Date, Type, Behavior, Start, and Stop may all be used, in the various combinations described below, to relate the new ROLES rows to existing EVENTS rows, or to create new EVENTS and WATCHES rows when they do not already exist.

If an EID is supplied, that is all that is required. The identified EVENTS row must already exist.

If an EID is not supplied, the columns WID, Focal, Date, Type, Behavior, Start, and Stop are used to query OBS. The Behavior, Start, and Stop columns must be supplied in this case.

If a WID is supplied it is used, along with Behavior, Start, and Stop, to match against database content. If a WID is not supplied, the Focal, Date, and Type columns must be supplied. Then they are used, along with Behavior, Start, and Stop, to match against database content.

If no match is found, a row is inserted into OBS. This means the rules regarding what data must be supplied when INSERTing into OBS, and when OBS re-uses existing rows or creates new rows, also apply to DYADS.

If an existing EVENTS row matches, the new ROLES rows are related to the EVENTS row so discovered. Otherwise they are related to the newly created EVENTS row.

When existing rows are found in the database, all (non-NULL) data values supplied must match the data values that already exist.

The WID, EID, Actor_PID, and Recipient_PID columns do not have their values inserted into new rows. If non-NULL values are supplied for these columns they must match the values already existing in, or inserted into, the database.

UPDATE

This operation is not allowed.

DELETE

This operation is not allowed.

Page last generated: 2026-07-01 00:11:55 UTC