SIGHTINGS

SIGHTINGS contains one row for each chimpanzee for every day the individual was observed. It records the community to which the individual was assigned on each day. SIGHTINGS is typically used to assist with censoring calculations.

The SIGHTINGS table is constructed by the system whenever the build_sightings() function is executed and cannot be manually maintained. The table’s rows are computed from the SokweDB tables’ content.

Manual adjustments to the SIGHTINGS table can be made by adjusting other database content. The NON_BREC_SIGHTING_SOURCES table is intended to be the primary way of making manual adjustments to SIGHTINGS. It is used to add arbitrary sightings of a chimpanzee. To remove a sighting, use the Step column to determine the source of the faulty data and correct the data.

The NON_BREC_SIGHTING_SOURCES table is used to add additional records of daily sightings into SIGHTINGS, records from “external” data sources, when the other parts of SokweDB do not indicate that an individual was sighted on a particular day. There is no way to remove rows from SIGHTINGS when SokweDB tables, other than the NON_BREC_SIGHTING_SOURCES table, indicate that an individual was sighted. But if the only reason a row appears in SIGHTINGS is due to rows in NON_BREC_SIGHTING_SOURCES, the row can be removed from SIGHTINGS by removing the NON_BREC_SIGHTING_SOURCES rows.

Rows may exist in NON_BREC_SIGHTING_SOURCES to record additional sightings of an individual on a given day when other tables in SokweDB also imply a sighting of the individual. In this case, the rows in NON_BREC_SIGHTING_SOURCES have no effect. However, should the SokweDB db’s content change in a way that indicates the individual was not sighted, the relevant rows in NON_BREC_SIGHTING_SOURCES will then cause the SIGHTINGS row to be retained – recording the individual as “sighted”.

The Step column, in conjunction with the SIGHTING_CONTROLS table, contains detail on the source of the sighting. But an individual may be sighted more than once per day. When there is more than one sighting per day, the SIGHTING_CONTROLS table contains detail on the “highest priority” data source, the table or other place where the “most important” sighting was recorded.

The following query reports sightings and their sources[1]:

Chimpanzee sightings, with detail on sighting data source

SELECT sightings.*, sighting_controls.*
  FROM sightings
    JOIN sighting_controls
         ON (sighting_controls.step = sightings.step)
  ORDER BY sightings.animid, sightings.date;

The combination of Date and AnimID must be unique.

Tracking Daily Community Membership

The COMM_MEMBS table tracks daily community membership. But it is somewhat hard to use. Because it does not contain a row for every chimpanzee for every day, querying COMM_MEMBS generally involves using inequality operators to compare both the COMM_MEMBS. StartDate and the COMM_MEMBS.EndDate to a date value from some other table. For example:

WHERE comm_membs.startdate <= pantgrunts_view.date
      AND pantgrunts_view.date <= comm_membs.enddate
      AND comm_membs.animid = pantgrunts_view.actor

This can be tedious, if not error prone.

The system can be configured, via SIGHTING_CONTROLS so that SIGHTINGS has a row for every chimpanzee for every day they exist. This incorporates the information in COMM_MEMBS into SIGHTINGS. Rows that exist in SIGHTINGS only because of COMM_MEMBS can then be excluded on a per-query basis.[2]

Such a configuration provides a way to query both the days an individual was actually observed, the primary function of SIGHTINGS, and provides an easier way to query community membership on a daily basis. The above query fragment would then be simplified to:

WHERE pantgrunts_view.date = sightings.date
      AND comm_membs.animid = pantgrunts_view.actor

SightID (SIGHTings IDentifier)

A unique, automatically generated, positive integer which serves to identify the row. The value of this column cannot be changed. This column may not be NULL.

Date

The date the individual was sighted. The date may not be before 1960-07-14. This column may not be NULL.

AnimID (Animal IDentifier)

The BIOGRAPHY_DATA.AnimID of the individual sighted. This column may not be NULL.

CommID (Community IDentifier)

The COMM_IDS.CommID of the community to which the individual was assigned for the day. This column may not be NULL.

Step

A code that provides detail on the source of the sighting information. A SIGHTING_CONTROLS.Step value. See above, and the documentation of the SIGHTINGS table and build_sightings() function for more information on how the value of this column is determined.

This column may not be NULL.

Footnotes

[1]

The query uses the shorthand tablename.* to display all a table’s columns. It may be more useful to list only those columns of interest.

[2]

You would exclude all the SIGHTINGS rows that exist only because there is a row in COMM_MEMBS by referencing the SIGHTING_CONTROLS.Step value, with a query containing something like:

WHERE sightings.step <> 4

Should this be burdensome, a view can be constructed to make the exclusion of COMM_MEMBS-sourced rows automatic.

Page last generated: 2026-08-13 23:02:22 UTC