BREC_NOTES_VIEW (BRECord NOTES VIEW)

This view contains one row for each row on the BRECORD_NOTES table. The view simplifies writing queries which show BRECORD_NOTES together with the content of other EVENTS related tables, for the purpose of comparing each with the other. The rows of the BREC_NOTES_VIEW are BRECORD_NOTES rows, but extended with the time, date, AnimID, and other information found on EVENTS and WATCHES.

The BREC_NOTES_VIEW view is convenient when writing queries.[1]

Below is a query that shows some BRECORD_NOTES information alongside pantgrunt information, when a note was taken during the same follow, at exactly the same time, as the pantgrunt occurred.

B-Record observations related to pantgrunt data

SELECT brec_notes_view.wid, brec_notes_view.eid
     , brec_notes_view.date
     , brec_notes_view.animid, brec_notes_view.time
     , brec_notes_view.commid, brec_notes_view.observation
     , pantgrunts_view.actor, pantgrunts_view.recipient
     , pantgrunts_view.style, pantgrunts_view.commid
     , pantgrunts_view.source
  FROM brec_notes_view
    JOIN pantgrunts_view
      ON (pantgrunts_view.date = brec_notes_view.date
          AND pantgrunts_view.animid = brec_notes_view.animid
          AND pantgrunts_view.time = brec_notes_view.time)
  ORDER BY brec_notes_view.animid, brec_notes_view.date
         , brec_notes_view.time, brec_notes_view.eid;

Here is a second query that shows some BRECORD_NOTES information alongside grooming information, when a note was taken during the same follow as the grooming was recorded, while the grooming was ongoing.

B-Record observations related to grooming data

SELECT brec_notes_view.wid, brec_notes_view.eid
     , brec_notes_view.date
     , brec_notes_view.animid, brec_notes_view.time
     , brec_notes_view.commid, brec_notes_view.observation
     , dyads.start, dyads.stop
     , dyads.actor, dyads.recipient
     , dyads.style, dyads.commid
     , groomings.initiator, groomings.terminator
  FROM brec_notes_view
    JOIN dyads
      ON (dyads.date = brec_notes_view.date
          AND dyads.animid = brec_notes_view.animid)
    JOIN groomings
  WHERE dyads.start <= brec_notes_view.time
        AND brec_notes_view.time <= dyads.stop
        AND dyads.behavior = 'GROOM'
  ORDER BY brec_notes_view.animid, brec_notes_view.date
         , brec_notes_view.time, brec_notes_view.eid;

There is, at the time of this writing, no view incorporating GROOMINGS, to make it eaiser to query. So the above query explicitly uses DYADS and GROOMINGS.

And, here is a query showing some BRECORD_NOTES information alongside arrival information, when a note was taken during the same follow, within 5 minutes of the time the arriving chimpanzee arrived.

B-Record observations related to arrival data

SELECT brec_notes_view.wid, brec_notes_view.eid, brec_notes_view.date
     , brec_notes_view.animid, brec_notes_view.time
     , brec_notes_view.commid, brec_notes_view.observation
     , roles.participant AS whoarrived
     , obs.start AS arrivaltime
     , obs.stop AS departuretime
     , obs.commid
     , arrivals.seq, arrivals.neststart, arrivals.nestend
     , arrivals.cycle, arrivals.datasource
  FROM brec_notes_view
    JOIN obs
      ON (obs.date = brec_notes_view.date
          AND obs.animid = brec_notes_view.animid)
    JOIN roles
      ON (roles.eid = obs.eid)
    JOIN arrivals
      ON (arrivals.eid = obs.eid)
  WHERE obs.start - '5 minutes'::INTERVAL <= brec_notes_view.time
        AND brec_notes_view.time <= obs.start + '5 minutes'::INTERVAL
        AND obs.behavior = 'ARR'
  ORDER BY brec_notes_view.animid, brec_notes_view.date
         , brec_notes_view.time, brec_notes_view.eid
         , obs.start, obs.eid;

There is, at the time of this writing, no view using ARRIVALS to make it easy to query. So the above query explicitly uses OBS and ARRIVALS, instead of DYADS as in the grooming query above, because arrivals involve a single individual instead of two individuals. Further, OBS does not contain any data from ROLES (because that varies depending on the event), so ROLES must be added to the query to show the BIOGRAPHY_DATA.|BIOGRAPHY_DATA.AnimID| of the individual who arrived.

Definition

CREATE OR REPLACE VIEW brec_notes_view (
  wid
 ,date
 ,animid
 ,type
 ,commid
 ,eid
 ,time
 ,observation
 ,comments
 ,observer
 ,translator
 ,transcribedby
 ,voc
 ,vocid
 ,groomingaggression
 ,duplicate
 ,notes
 ,event_notes
  )
  AS
  SELECT obs.wid
       , obs.date
       , obs.animid
       , obs.type
       , obs.commid
       , obs.eid
       , obs.start AS time
       , brecord_notes.observation
       , brecord_notes.comments
       , brecord_notes.observer
       , brecord_notes.translator
       , brecord_notes.transcribedby
       , brecord_notes.voc
       , brecord_notes.vocid
       , brecord_notes.groomingaggression
       , brecord_notes.duplicate
       , obs.notes
       , obs.event_notes
   FROM obs
     JOIN brecord_notes ON (brecord_notes.eid = obs.eid);

ER Diagram

BREC_NOTES_VIEW

Columns of the BREC_NOTES_VIEW View

The columns of the BREC_NOTES_VIEW view

Column	From	Description
WID	OBS.WID	Identifier of the related WATCHES row
Date	OBS.Date	Date of the event
AnimID	OBS.AnimID	Focal of follow, or focal of a non-existent follow, or an un-interesting AnimID
Type	OBS.Type	Category of observation, often determining the data collection protocol: follow, feeding station attendance, groom scans, etc.
CommID	OBS.CommID	The community identifier associated with the Date/AnimID/Type
EID	OBS.EID	Identifier of the EVENTS row
Behavior	OBS.Behavior	Code designating the type of event observed
Time	OBS.Start	Time the B-Record note was recorded
Observation	BRECORD_NOTES.Observation	A narrative account of the observation
Comments	BRECORD_NOTES.Comments	Additional comments
Observer	BRECORD_NOTES.Observer	Name of field assistant who recorded the notes
Translator	BRECORD_NOTES.Translator	Name of translator
TranscribedBy	BRECORD_NOTES.TranscribedBy	Name of person who transcribed the translation
Voc	BRECORD_NOTES.Voc	Text to do with chimpanzee vocalizations
VocID	BRECORD_NOTES.VocID	More text to do with chimpanzee vocalization
GroomingAggression	BRECORD_NOTES.GroomingAggression	The English translation of the text of the observation
Duplicate	BRECORD_NOTES.Duplicate	Text related to duplicate data tracking
Notes	OBS.Notes	Textual notes on the observation for the Date/AnimID/Type
Event_Notes	OBS.Event_Notes	Textual notes on the event

Operations Allowed

None.

Footnotes

[1]

This is particularly true because, in order to join BRECORD_NOTES with other EVENTS-based tables, the join is done on Date, Time, and AnimID, all columns found on the EVENTS and WATCHES tables. (Or found via the OBS or DYADS views.) But in order to join on, say, Date, the WATCHES row related to the BRECORD_NOTES row must be part of the query, as must the WATCHES row related to the behavioral event of interest. The WATCHES row must appear twice in the same query. Doing this requires aliasing the name of the WATCHES table using the AS syntax, a somewhat advanced technique.

When the BREC_NOTES_VIEW is used, the columns of the WATCHES and EVENTS (and OBS) tables are already “aliased” under the “BREC_NOTES_VIEW” name, simplifying query construction whenever any of these relations must also be written into the query.

Page last generated: 2026-07-26 00:00:49 UTC