SWERB Data (Group-level Geolocation Data)

QUADS (map Quadrants)

Contains one row for every row in QUAD_DATA.

This view is useful for querying and maintaining the QUAD_DATA table when it is convenient to have X and Y coordinates as separate values instead of geospatial points.

Definition

Figure 6.116. Query Defining the QUADS View


SELECT quad_data.quad AS quad
     , ST_X(quad_data.xyloc) AS x
     , ST_Y(quad_data.xyloc) AS y
     , quad_data.aerial AS aerial
  FROM quad_data
;


Figure 6.117. Entity Relationship Diagram of the QUADS View

If we could we would display here the diagram showing how the QUADS view is constructed.


Table 6.56. Columns in the QUADS View

Column From Description
Quad QUAD_DATA.Quad Identifier of the map quadrant.
X ST_X(QUAD_DATA.XYLoc) X coordinate of the XYLoc -- X coordinate of the centroid of the map quadrant.
Y ST_Y(QUAD_DATA.XYLoc) Y coordinate of the XYLoc -- Y coordinate of the centroid of the map quadrant.
Aerial AERIALS.Aerial Code indicating the aerial photo in which the map quadrant is located.

Operations Allowed

INSERT

Inserting a row into QUADS inserts a row into QUAD_DATA as expected.

UPDATE

The QUADS view may be updated and QUAD_DATA is updated as expected.

DELETE

Deleting a row from QUADS deletes a row from QUAD_DATA as expected.

SWERB (Group level gps point samples)

Contains one row for every row in SWERB_DATA.

This view is useful for querying the SWERB_DATA table because it unifies data that is distributed throughout the various SWERB tables. It is also useful when it is convenient to have X and Y coordinates as separate values instead of geospatial points.

Note

For more information on the X and Y coordinates see the description of the columns in the underlying tables, see the SWERB Data overview, and see the Amboseli Baboon Research Project Monitoring Guide.

Definition

Figure 6.118. Query Defining the SWERB View


SELECT swerb_data.swid AS swid
     , swerb_departs_data.did AS did
     , swerb_departs_data.date AS date
     , swerb_data.time AS time
     , swerb_bes.beid AS beid
     , swerb_bes.focal_grp AS focal_grp
     , swerb_bes.seq AS seq
     , swerb_data.event AS event
     , swerb_data.seen_grp AS seen_grp
     , swerb_data.lone_animal AS lone_animal
     , swerb_data.quad AS quad
     , CASE
         WHEN swerb_data.quad IS NOT NULL
           THEN 'quad'
         WHEN swerb_data.xyloc IS NULL
           THEN 'n/a'
         ELSE 'gps'
       END AS xysource
     , COALESCE(ST_X(swerb_data.xyloc), ST_X(quad_data.xyloc))
         AS x
     , COALESCE(ST_Y(swerb_data.xyloc), ST_Y(quad_data.xyloc))
         AS y
     , swerb_data.altitude AS altitude
     , swerb_data.pdop AS pdop
     , swerb_data.accuracy AS accuracy
     , swerb_data.subgroup AS subgroup
     , swerb_data.ogdistance AS ogdistance
     , swerb_data.gps_datetime AS gps_datetime
     , swerb_data.garmincode AS garmincode
     , swerb_data.predator AS predator
     , swerb_loc_data.loc AS loc
     , swerb_loc_data.adcode AS adcode
     , adcodes.adn AS adn
     , swerb_loc_data.loc_status AS loc_status
     , swerb_loc_data.adtime AS adtime
     , ST_X(swerb_loc_gps.xyloc) AS second_x
     , ST_Y(swerb_loc_gps.xyloc) AS second_y
     , swerb_loc_gps.altitude AS second_altitude
     , swerb_loc_gps.pdop AS second_pdop
     , swerb_loc_gps.accuracy AS second_accuracy
     , swerb_loc_gps.gps_datetime AS second_gps_datetime
     , swerb_loc_gps.garmincode AS second_garmincode
     , swerb_bes.start AS start
     , swerb_bes.btimeest AS btimeest
     , swerb_bes.bsource AS bsource
     , swerb_bes.stop AS stop
     , swerb_bes.etimeest AS etimeest
     , swerb_bes.esource AS esource
     , swerb_bes.is_effort AS is_effort
     , swerb_departs_gps.gps AS gps
     , swerb_bes.notes AS notes
  FROM swerb_data
       LEFT OUTER JOIN quad_data 
                       ON (quad_data.quad = swerb_data.quad)
       JOIN swerb_bes
            ON (swerb_bes.beid = swerb_data.beid)
       JOIN swerb_departs_data
            ON (swerb_departs_data.did = swerb_bes.did)
       LEFT OUTER JOIN swerb_departs_gps
                       ON (swerb_departs_gps.did = swerb_bes.did)
       LEFT OUTER JOIN swerb_loc_data
                       ON (swerb_loc_data.swid = swerb_data.swid)
       LEFT OUTER JOIN adcodes ON (adcodes.adcode = swerb_loc_data.adcode)
       LEFT OUTER JOIN swerb_loc_gps
                       ON (swerb_loc_gps.swid = swerb_loc_data.swid)
;


Figure 6.119. Entity Relationship Diagram of the SWERB View

If we could we would display here the diagram showing how the SWERB view is constructed.


Table 6.57. Columns in the SWERB View

Column From Description
SWId SWERB_DATA.SWId Identifier of the record of the SWERB event.
DId SWERB_DEPARTS_DATA.DId Identifier of the record of departure from camp of the observation team which recorded the SWERB event.
Date SWERB_DEPARTS_DATA.Date The date of the observation.
Time SWERB_DATA.Time The time of the observation.
BEId SWERB_BES.BEId Identifier of the bout of uninterrupted observation of the focal group containing observed SWERB event.
Focal_grp SWERB_BES.Focal_grp Identifier of the focal group, the group the observation team set out to watch.
Seq SWERB_BES.Seq A sequence number indicating the ordering of the bouts of uninterrupted observation of each group each day -- ordering of BEId per Focal_grp per Date.
Event SWERB_DATA.Event Code identifying the type of SWERB event observed.
Seen_grp SWERB_DATA.Seen_grp Identifier of the observed group.
Lone_Animal SWERB_DATA.Lone_Animal Sname of the observed lone animal or NULL when either there is none or an unknown lone male was observed.
Quad SWERB_DATA.Quad The code identifying the map quadrant locating the recorded event.
XYSource

CASE
  WHEN swerb_data.quad IS NOT NULL
    THEN 'quad'
  WHEN swerb_data.xyloc IS NULL
    THEN 'n/a'
  ELSE 'gps'
END AS xysource

The source of the view's X and Y columns:

The XYSource column values

quad

Coordinates of the centroid of the related map quadrant.

gps

Coordinates recorded by a GPS unit. This the default when there are both GPS and map quadrant coordinates.

n/a

There are no coordinates for this row.

X QUAD_DATA.XYLoc or SWERB_DATA.XYLoc Whatever X geolocation coordinate exists.
Y QUAD_DATA.XYLoc or SWERB_DATA.XYLoc Whatever Y geolocation coordinate exists.
Altitude SWERB_DATA.Altitude The altitude of the SWERB event.
PDOP SWERB_DATA.PDOP The PDOP of the SWERB event.
Accuracy SWERB_DATA.Accuracy Accuracy of the SWERB event.
Subgroup SWERB_DATA.Subgroup Whether or not the SWERB event pertains to a subgroup.
OGDistance SWERB_DATA.Ogdistance The distance to the non-focal group (the Seen_grp) at the time the waypoint was taken.
GPS_Datetime SWERB_DATA.GPS_Datetime The timestamp, the date and time, automatically recorded by the GPS unit when the waypoint was entered into the GPS.
Garmincode SWERB_DATA.Garmincode The raw data entered by the observer recording the SWERB event.
Predator SWERB_DATA.Predator The type of predator seen, or NULL when there is none.
Loc SWERB_LOC_DATA.Loc Identifier of the related landscape feature, the SWERB_GWS.Loc.
ADcode SWERB_LOC_DATA.ADcode The code denoting the relationship between the group and the landscape feature.
ADN ADCODES.ADN Whether the relationship between the group and the landscape feature is an ascent into a sleeping grove (A), a descent from a sleeping grove (D), or neither (N).
Loc_Status SWERB_LOC_DATA.Loc_Status Code representing the status of the team's observation of the indicated landscape feature.
ADtime SWERB_LOC_DATA.ADtime Median time of group descent from or ascent into the sleeping grove.
Second_X SWERB_LOC_GPS.XYLoc The X geolocation coordinate of the 2nd waypoint entry required by the data entry protocol.
Second_Y SWERB_LOC_GPS.XYLoc The Y geolocation coordinate of the 2nd waypoint entry required by the data entry protocol.
Second_Altitude SWERB_LOC_GPS.Altitude The altitude of the 2nd waypoint entry required by the data entry protocol.
Second_PDOP SWERB_LOC_GPS.PDOP The PDOP of the 2nd waypoint entry required by the data entry protocol.
Second_Accuracy SWERB_LOC_GPS.Accuracy Accuracy of the 2nd waypoint entry required by the data entry protocol.
Second_GPS_Datetime SWERB_LOC_GPS.GPS_Datetime The timestamp, the date and time, automatically recorded by the GPS unit when the 2nd waypoint required by the data entry protocol was entered into the GPS.
Second_Garmincode SWERB_LOC_GPS.Garmincode The raw data entered by the observer in the 2nd waypoint entry required by the data entry protocol when recording the SWERB event.
Start SWERB_BES.Start The time the bout of observation began.
Btimeest SWERB_BES.Btimeest Whether or not the start time of the bout of observation was estimated.
Bsource SWERB_BES.Bsource The source of the bout start time value.
Stop SWERB_BES.Stop The time the bout of observation ended.
Etimeest SWERB_BES.Etimeest Whether or not the end time of the bout of observation was estimated.
Esource SWERB_BES.Esource The source of the bout end time value.
Is_Effort SWERB_BES.Is_Effort Whether or not the bout of observation counts toward total observer effort.
GPS SWERB_DEPARTS_GPS.GPS Identifier of the GPS device used to record the SWERB event.
Notes SWERB_BES.Notes Notes on the bout of observation.

Operations Allowed

Only SELECT is allowed on SWERB. INSERT, UPDATE, and DELETE are not allowed.

SWERB_DATA_XY (The SWERB_DATA table with separate X and Y coordinates)

Contains one row for every row in SWERB_DATA.

This view is useful when it is convenient to have X and Y coordinates as separate values instead of geospatial points. For this reason is it also useful when maintaining the SWERB_DATA table. Users querying the data may prefer the SWERB view.

Definition

Figure 6.120. Query Defining the SWERB_DATA_XY View


SELECT swerb_data.swid AS swid
     , swerb_data.beid AS beid
     , swerb_data.seen_grp AS seen_grp
     , swerb_data.lone_animal AS lone_animal
     , swerb_data.event AS event
     , swerb_data.time AS time
     , swerb_data.quad AS quad
     , ST_X(swerb_data.xyloc) AS x
     , ST_Y(swerb_data.xyloc) AS y
     , swerb_data.altitude AS altitude
     , swerb_data.pdop AS pdop
     , swerb_data.accuracy AS accuracy
     , swerb_data.subgroup AS subgroup
     , swerb_data.ogdistance AS ogdistance
     , swerb_data.gps_datetime AS gps_datetime
     , swerb_data.garmincode AS garmincode
     , swerb_data.predator AS predator
  FROM swerb_data
;


Figure 6.121. Entity Relationship Diagram of the SWERB_DATA_XY View

If we could we would display here the diagram showing how the SWERB_DATA_XY view is constructed.


Table 6.58. Columns in the SWERB_DATA_XY View

Column From Description
SWId SWERB_DATA.SWId Identifier of the record of the SWERB event.
BEId SWERB_BES.BEId Identifier of the bout of uninterrupted observation of the focal group containing observed SWERB event.
Seen_grp SWERB_DATA.Seen_grp Identifier of the observed group.
Lone_Animal SWERB_DATA.Lone_Animal Sname of the observed lone male or NULL when either there is none or an unknown lone male was observed.
Event SWERB_DATA.Event Code identifying the kind of SWERB event observed.
Time SWERB_DATA.Time The time of the observation.
Quad SWERB_DATA.Quad The code identifying the map quadrant locating the recorded event.
X ST_X(SWERB_DATA.XYLoc) X coordinate of the XYLoc -- X coordinate of the event.
Y ST_Y(SWERB_DATA.XYLoc) Y coordinate of the XYLoc -- Y coordinate of the event.
Altitude SWERB_DATA.Altitude The altitude of the SWERB event.
PDOP SWERB_DATA.PDOP The PDOP of the SWERB event.
Accuracy SWERB_DATA.Accuracy Accuracy of the SWERB event.
Subgroup SWERB_DATA.Subgroup Whether or not the SWERB event pertains to a subgroup.
OGDistance SWERB_DATA.Ogdistance The distance to the non-focal group where the SWERB event takes place.
GPS_Datetime SWERB_DATA.GPS_Datetime The timestamp, the date and time, automatically recorded by the GPS unit when the waypoint was entered into the GPS.
Garmincode SWERB_DATA.Garmincode The raw data entered by the observer recording the SWERB event.
Predator SWERB_DATA.Predator The type of predator seen, or NULL when there is none..

Operations Allowed

INSERT

Inserting a row into SWERB_DATA_XY inserts a row into SWERB_DATA as expected.

UPDATE

Updating the SWERB_DATA_XY view updates the SWERB_DATA table as expected.

DELETE

Deleting a row from SWERB_DATA_XY deletes a row from SWERB_DATA as expected.

SWERB_DEPARTS (SWERB observation team Departures from camp)

Contains one row for every row in SWERB_DEPARTS_DATA. Each row contains the SWERB_DEPARTS_DATA data and the related SWERB_DEPARTS_GPS row, excepting the geolocation data which is converted into X and Y coordinates. In those cases where there is a SWERB_DEPARTS_DATA row but not a row from SWERB_DEPARTS_GPS the columns from SWERB_DEPARTS_GPS are NULL.

This view is useful when downloading departure data for analysis outside of the database, and useful for deleting all information related to specified departures.

Definition

Figure 6.122. Query Defining the SWERB_DEPARTS View


SELECT swerb_departs_data.did AS did
     , swerb_departs_data.date AS date
     , swerb_departs_data.time AS time
     , ST_X(swerb_departs_gps.xyloc) AS x
     , ST_Y(swerb_departs_gps.xyloc) AS y
     , swerb_departs_gps.altitude AS altitude
     , swerb_departs_gps.pdop AS pdop
     , swerb_departs_gps.accuracy AS accuracy
     , swerb_departs_gps.gps AS gps
     , swerb_departs_gps.garmincode AS garmincode
  FROM swerb_departs_data
       LEFT OUTER JOIN swerb_departs_gps
                       ON (swerb_departs_gps.did = swerb_departs_data.did)
;


Figure 6.123. Entity Relationship Diagram of the SWERB_DEPARTS View

If we could we would display here the diagram showing how the SWERB_DEPARTS view is constructed.


Table 6.59. Columns in the SWERB_DEPARTS View

Column From Description
Did SWERB_DEPARTS_DATA.DId Identifier of the team's departure row.
Date SWERB_DEPARTS_DATA.Date Date of departure.
Time SWERB_DEPARTS_DATA.Time Time of the team's departure.
X ST_X(SWERB_DEPARTS_GPS.XYLoc) X coordinate of the XYLoc -- X coordinate of the point of departure.
Y ST_Y(SWERB_DEPARTS_GPS.XYLoc) Y coordinate of the XYLoc -- Y coordinate of the point of departure.
Altitude SWERB_DEPARTS_GPS.Altitude Altitude at the point of departure.
PDOP SWERB_DEPARTS_GPS.PDOP Positional Dilution of Precision of the departure's geolocation.
Accuracy SWERB_DEPARTS_GPS.Accuracy Accuracy of the departure's geolocation expressed as distance in meters.
GPS SWERB_DEPARTS_GPS.GPS Identifier of the GPS device (GPS_UNITS.GPS) used by the team.
Garmincode SWERB_DEPARTS_GPS.Garmincode The information manually entered into the waypoint by the observer.

Operations Allowed

INSERT

Inserting a row into SWERB_DEPARTS inserts a row into SWERB_DEPARTS_DATA and a row into SWERB_DEPARTS_GPS as expected. Rows are inserted into SWERB_DEPARTS_GPS when any of the the relevant columns are present and contain non-NULL values.

UPDATE

The SWERB_DEPARTS view may be updated and SWERB_DEPARTS_DATA and SWERB_DEPARTS_GPS are (mostly) updated as expected.

Warning

Attempts to update SWERB_DEPARTS_GPS columns when no underlying row exists are silently ignored.

DELETE

Deleting a row from SWERB_DEPARTS deletes all SWERB data collected by the departing observation team; a row from SWERB_DEPARTS_DATA is deleted along with, if necessary, a row from SWERB_DEPARTS_GPS and multiple related rows from SWERB_OBSERVERS, multiple related rows from SWERB_BES, multiple rows related to these from SWERB_DATA, SWERB_LOC_DATA, and SWERB_LOC_GPS.

SWERB_GW_LOCS (SWERB Grove and Waterhole Locations)

Contains one row for every row in SWERB_GW_LOC_DATA.

This view is useful for querying the SWERB_GW_LOC_DATA table because it unifies data that are distributed between the SWERB_GW_LOC_DATA table and the QUAD_DATA table. It is also useful when it is convenient to have X and Y coordinates as separate values instead of geospatial points.

Note

For more information regarding the X and Y coordinates see the description of the columns in the underlying tables, and see the SWERB Data overview.

Definition

Figure 6.124. Query Defining the SWERB_GW_LOCS View


SELECT swerb_gw_loc_data.sgwlid AS sgwlid
     , swerb_gw_loc_data.loc AS loc
     , swerb_gw_loc_data.date AS date
     , swerb_gw_loc_data.time AS time
     , swerb_gw_loc_data.quad AS quad
     , CASE
         WHEN swerb_gw_loc_data.xyloc IS NULL
           THEN 'quad'
         ELSE swerb_gw_loc_data.xysource
       END AS xysource
     , COALESCE(ST_X(swerb_gw_loc_data.xyloc), ST_X(quad_data.xyloc))
         AS x
     , COALESCE(ST_Y(swerb_gw_loc_data.xyloc), ST_Y(quad_data.xyloc))
         AS y
     , swerb_gw_loc_data.altitude AS altitude
     , swerb_gw_loc_data.pdop AS pdop
     , swerb_gw_loc_data.accuracy AS accuracy
     , swerb_gw_loc_data.gps AS gps
     , swerb_gw_loc_data.notes AS notes
  FROM swerb_gw_loc_data
       LEFT OUTER JOIN quad_data 
                       ON (quad_data.quad = swerb_gw_loc_data.quad)
;


Figure 6.125. Entity Relationship Diagram of the SWERB_GW_LOCS View

If we could we would display here the diagram showing how the SWERB_GW_LOCS view is constructed.


Table 6.60. Columns in the SWERB_GW_LOCS View

Column From Description
SGWLId SWERB_GW_LOC_DATA.SGWLId Identifier of the observation of a grove or waterhole's geolocation.
Loc SWERB_GW_LOC_DATA.Loc Identifier of the object, the grove or waterhole.
Date SWERB_GW_LOC_DATA.Date The date of the observation.
Time SWERB_GW_LOC_DATA.Time The time of the observation.
Quad SWERB_GW_LOC_DATA.Quad The code identifying the map quadrant containing the grove or waterhole.
XYSource

CASE
  WHEN swerb_gw_loc_data.xyloc IS NULL
    THEN 'quad'
  ELSE swerb_gw_loc_data.xysource
END AS xysource

The source of the view's X and Y columns. When quad the source of the X and Y columns are the coordinates of the centroid of the related map quadrant. Otherwise this is the value of the SWERB_GW_LOC_DATA.XYSource column.
X QUAD_DATA.XYLoc or SWERB_GW_LOC_DATA.XYLoc Whatever X geolocation coordinate exists.
Y QUAD_DATA.XYLoc or SWERB_GW_LOC_DATA.XYLoc Whatever Y geolocation coordinate exists.
Altitude SWERB_GW_LOC_DATA.Altitude The altitude of the object, the grove or waterhole.
PDOP SWERB_GW_LOC_DATA.PDOP The PDOP of the object's geolocation.
Accuracy SWERB_GW_LOC_DATA.Accuracy Accuracy of the object's geolocation.
GPS SWERB_GW_LOC_DATA.GPS Identifier of the GPS unit (GPS_UNITS) used to take the measurement.
Notes SWERB_GW_LOC_DATA.Notes Notes on the measurement.

Operations Allowed

Only SELECT is allowed on SWERB. INSERT, UPDATE, and DELETE are not allowed.

SWERB_GW_LOC_DATA_XY (The SWERB_GW_LOC_DATA table with separate X and Y coordinates)

Contains one row for every row in SWERB_GW_LOC_DATA.

This view is useful when it is convenient to have X and Y coordinates as separate values instead of geospatial points. For this reason is it also useful when maintaining the SWERB_GW_LOC_DATA table. Users querying the view may prefer the SWERB_GW_LOCS view.

Definition

Figure 6.126. Query Defining the SWERB_GW_LOC_DATA_XY View


SELECT swerb_gw_loc_data.sgwlid AS sgwlid
     , swerb_gw_loc_data.loc AS loc
     , swerb_gw_loc_data.date AS date
     , swerb_gw_loc_data.time AS time
     , swerb_gw_loc_data.quad AS quad
     , swerb_gw_loc_data.xysource AS xysource
     , ST_X(swerb_gw_loc_data.xyloc) AS x
     , ST_Y(swerb_gw_loc_data.xyloc) AS y
     , swerb_gw_loc_data.altitude AS altitude
     , swerb_gw_loc_data.pdop AS pdop
     , swerb_gw_loc_data.accuracy AS accuracy
     , swerb_gw_loc_data.gps AS gps
     , swerb_gw_loc_data.notes AS notes
  FROM swerb_gw_loc_data
;


Figure 6.127. Entity Relationship Diagram of the SWERB_GW_LOC_DATA_XY View

If we could we would display here the diagram showing how the SWERB_GW_LOC_DATA_XY view is constructed.


Table 6.61. Columns in the SWERB_GW_LOC_DATA_XY View

Column From Description
SGWLId SWERB_GW_LOC_DATA.SGWLId Identifier of the observation that geolocated the object, the grove or waterhole.
Loc SWERB_GW_LOC_DATA.Loc Identifier of the object, the grove or waterhole, that is located.
Date SWERB_GW_LOC_DATA.Date The date of the observation.
Time SWERB_GW_LOC_DATA.Time The time of the observation.
Quad SWERB_GW_LOC_DATA.Quad The code identifying the map quadrant containing the observed object, the grove or waterhole.
X ST_X(SWERB_GW_LOC_DATA.XYLoc) X coordinate of the XYLoc -- X coordinate of the object.
Y ST_Y(SWERB_GW_LOC_DATA.XYLoc) Y coordinate of the XYLoc -- Y coordinate of the object.
Altitude SWERB_GW_LOC_DATA.Altitude The altitude of the object.
PDOP SWERB_GW_LOC_DATA.PDOP The PDOP of the geolocation.
Accuracy SWERB_GW_LOC_DATA.Accuracy Accuracy of the SWERB geolocation.
GPS SWERB_GW_LOC_DATA.GPS The code identifying the GPS unit (GPS_UNITS) used to take the observation.
Notes SWERB_GW_LOC_DATA. Notes Notes on the observation.

Operations Allowed

INSERT

Inserting a row into SWERB_GW_LOC_DATA_XY inserts a row into SWERB_GW_LOC_DATA as expected.

UPDATE

Updating the SWERB_GW_LOC_DATA_XY view updates the SWERB_GW_LOC_DATA table as expected.

DELETE

Deleting a row from SWERB_GW_LOC_DATA_XY deletes a row from SWERB_GW_LOC_DATA as expected.

SWERB_LOC_GPS_XY (The SWERB_LOC_GPS table with separate X and Y coordinates)

Contains one row for every row in SWERB_LOC_GPS.

This view is useful when it is convenient to have X and Y coordinates as separate values instead of geospatial points. For this reason is it also useful when querying and maintaining the SWERB_LOC_GPS table.

Definition

Figure 6.128. Query Defining the SWERB_LOC_GPS_XY View


SELECT swerb_loc_gps.swid AS swid
     , ST_X(swerb_loc_gps.xyloc) AS x
     , ST_Y(swerb_loc_gps.xyloc) AS y
     , swerb_loc_gps.altitude AS altitude
     , swerb_loc_gps.pdop AS pdop
     , swerb_loc_gps.accuracy AS accuracy
     , swerb_loc_gps.gps_datetime AS gps_datetime
     , swerb_loc_gps.garmincode AS garmincode
  FROM swerb_loc_gps
;


Figure 6.129. Entity Relationship Diagram of the SWERB_LOC_GPS_XY View

If we could we would display here the diagram showing how the SWERB_LOC_GPS_XY view is constructed.


Table 6.62. Columns in the SWERB_LOC_GPS_XY View

Column From Description
SWId SWERB_LOC_GPS.SWId Identifier of the GPS information involving an observation of a group at a particular time at a particular grove or waterhole. Also the SWERB_DATA.SWId value and the SWERB_LOC_DATA.SWId value
X ST_X(SWERB_LOC_GPS.XYLoc) X coordinate of the XYLoc -- X coordinate of the group.
Y ST_Y(SWERB_LOC_GPS.XYLoc) Y coordinate of the XYLoc -- Y coordinate of the group.
Altitude SWERB_LOC_GPS.Altitude The altitude of the group.
PDOP SWERB_LOC_GPS.PDOP The PDOP of the geolocation.
Accuracy SWERB_LOC_GPS.Accuracy Accuracy of the SWERB geolocation.
GPS_Datetime SWERB_LOC_GPS.GPS_Datetime The date and time recorded by the GPS unit.
Garmincode SWERB_LOC_GPS. Garmincode The information manually entered into the waypoint by the observer.

Operations Allowed

INSERT

Inserting a row into SWERB_LOC_GPS_XY inserts a row into SWERB_LOC_GPS as expected.

UPDATE

Updating the SWERB_LOC_GPS_XY view updates the SWERB_LOC_GPS table as expected.

DELETE

Deleting a row from SWERB_LOC_GPS_XY deletes a row from SWERB_LOC_GPS as expected.

SWERB_LOCS (placement of a group at a landscape feature)

Contains one row for every row in SWERB_LOC_DATA.

This view is useful for querying the SWERB_LOC_DATA table because makes explicit whether or not the landscape feature involves descent from or ascent into a sleeping grove.

Definition

Figure 6.130. Query Defining the SWERB_LOCS View


SELECT swerb_loc_data.swid AS swid
     , swerb_loc_data.loc AS loc
     , swerb_loc_data.adcode AS adcode
     , adcodes.adn AS adn
     , swerb_loc_data.loc_status AS loc_status
     , swerb_loc_data.adtime AS time
  FROM swerb_loc_data
       JOIN adcodes ON (adcodes.adcode = swerb_loc_data.adcode)
;


Figure 6.131. Entity Relationship Diagram of the SWERB_LOCS View

If we could we would display here the diagram showing how the SWERB_LOCS view is constructed.


Table 6.63. Columns in the SWERB_LOCS View

Column From Description
SWId SWERB_LOC_DATA.SWId Identifier of the placement of the group at the landscape feature and of the related of the SWERB event, the SWERB_DATA.SWId.
Loc SWERB_LOC_DATA.Loc Identifier of the related landscape feature, the SWERB_GWS.Loc.
ADcode SWERB_LOC_DATA.ADcode The code denoting the relationship between the group and the landscape feature.
ADN ADCODES.ADN Whether the relationship between the group and the landscape feature is an ascent into a sleeping grove (A), a descent from a sleeping grove (D), or neither (N).
Loc_Status SWERB_LOC_DATA.Loc_Status Code representing the status of the team's observation of the indicated landscape feature.
ADtime SWERB_LOC_DATA.ADtime Median time of group descent from or ascent into the sleeping grove.

Operations Allowed

Only SELECT is allowed on SWERB_LOCS. INSERT, UPDATE, and DELETE are not allowed.

SWERB_UPLOAD (facility for uploading data into SWERB)

This view returns no rows, it is used only to upload data into the SWERB portion of Babase. Attempting to SELECT rows from this view will raise an error.

This view exists instead of a custom upload program.

The SWERB_UPLOAD view uses G as the value for SWERB_BES.Bsource and SWERB_BES.Esource in the SWERB_BES rows it inserts, unless a different value is provided in this view's Source column.

Whenever the SWERB_UPLOAD view obtains a SWERB_DATA.Time from the GPS unit (the SWERB_DATA.GPS_Datetime value) instead of from operator entry (the SWERB_DATA.Garmincode value) the seconds portion of the timestamp is discarded.

When a median ascent/descent time is entered into the GPS unit by the observer the SWERB_UPLOAD program uses the values A and D for the ascent and descent SWERB_LOC_DATA.ADcode value, respectively.[281] When a drinking event is recorded the SWERB_UPLOAD view uses the value N for the SWERB_LOC_DATA.ADcode. (See ADCODES: Special Values.)

When SWERB_UPLOAD encounters a line which records a drinking event and the immediately preceding line (or pair of lines in the case of beginning of observation) is an observation of a subgroup and the line immediately following (or pair of lines in the case of end of observation) is an observation of a subgroup then the SWERB_DATA.Subgroup of the drinking event will be set to TRUE -- the drinking event will be recorded as that of a subgroup.[282] When considering whether the preceding and subsequent lines are of subgroups lines representing drinking events and lines representing observations of lone animals and other groups are ignored.

When SWERB_UPLOAD encounters a non-focal group observation that is not part of any bout of observation it automatically creates a bout of observation to contain the non-focal group observation. The created bout of observation has as its focal the unknown group (9.0). It begins and ends at the time of the non-focal group observation and so has a duration of 0 minutes. It is also marked as a bout of observation which should not count toward observer effort (SWERB_BES.Is_Effort is FALSE). Aside from the begin and end rows, the only SWERB observation (the only SWERB_DATA) row belonging to the bout is the non-focal group observation.

SWERB_UPLOAD Data Input Format

The format of the data that is inserted into the SWERB_UPLOAD view is complex. This section provides an overview and remarks on unusual features[283] and the tasks required of the data entry manager to convert the raw SWERB data into an uploadable format. The description of the SWERB_UPLOAD view in the following sections describes how the various uploaded columns map into the columns of Babase's tables. Because, excepting variances described in this section, the uploaded data comes directly from the GPS units used to collect SWERB data the reader should rely on the description of the SWERB data collection protocol in the Amboseli Baboon Research Project Monitoring Guide for a complete description of the data format.[284]

Each upload into the SWERB_UPLOAD view must consist of the data collected on a single GPS unit by a single observation team during the course of a single day.

The data is uploaded as a collection of lines containing tab-delimited text. Each line represents a waypoint recorded by the operator. The lines are expected to be in chronological order, the first line being the first waypoint recorded and the last line being the last, with the exceptions that the lines contriving any one bout of observation of any one (sub)group must be contiguous and that the begin and end lines which record the sleeping grove must immediately precede the begin and end lines which record the descent or ascent time.[285] Consequently the following constraints are imposed on the data: the first line(s) must record the observation team's departure from camp; the lines representing a bout of observation must be contiguous; the line denoting the beginning of a bout of observation must precede all of the bout's other lines; the line denoting the end of a bout of observation must follow all of the bout's other lines; the line recording the median descent time, when present, must immediately follow the line denoting beginning of the bout of observation and the previous night's sleeping grove; the line recording the median ascent time, when present, must immediately follow the line denoting end of the bout of observation and the night's sleeping grove; in those cases where a group utilizes more than one sleeping grove the sleeping grove information must consist of contiguous pairs of lines (as just described) with no intervening lines of another sort; notwithstanding anything to the contrary above, lines denoting observations of the non-focal group may appear at any point after the lines representing departure from camp.[286]

Note

When there is more than one line representing departure of the team from camp the only GPS information recorded in SWERB_DEPARTS_GPS is that of the first departure line. The GPS information (XY coordinates, altitude, pdop, timestamp, etc.) recorded in successive departure rows is discarded; successive departure lines serve only to supply additional observers and their roles for insertion into the SWERB_OBSERVERS table.

The first 2 lines of the uploaded file are required to be departure lines, lines which record information about the departing observation team. The first line must begin with a D, it lists the observers. The initials supplied on this first line control the SWERB_OBSERVERS.Role value used, the value used being the referenced OBSERVERS.SWERB_Observer_Role column's value. The second line must begin with DD, it lists the drivers. The initials supplied on the second line control the SWERB_OBSERVERS.Role value used, the value used being the referenced OBSERVERS.SWERB_Driver_Role column's value.

It is an error if all the lines representing departure from camp indicate the use of more than one GPS unit. Each data upload into SWERB_DEPARTS must come from a single GPS unit.

It is an error if observer codes cannot be unambiguously interpreted in the departure lines. This means that when there is an observer code which is shorter and match in its entirety the beginning of other observer codes then none of these observer codes, neither the shorter nor the longer, can be reliably used in SWERB departure waypoints.[287] If there is ambiguity the offending observer code must be manually removed from the departure line and manually inserted into SWERB_OBSERVERS after uploading the data file.

When the field team records coordinates for the start or stop of a bout of observation but somehow fails to record the time, then that time must be estimated by the data entry staff and included in the Description. In this case the columns Timeest and Source must be added to the data file by the data manager. The data manager should supply values for these columns only in the begin and end lines.

Note

The SWERB_LOC_DATA.ADcode value is sometimes obtained directly from the GPS waypoint data (the Name column) entered by the observer, from the second begin/end line recording median ascent/descent time. This occurs when, for whatever reason, the operator does not record a time following after entering the letters MAT or MAT. Whatever is entered in place of a time (which is required to be entered as 4 digits), is used for the SWERB_LOC_DATA.ADcode value.

When the field team fails to record the start or stop of a bout of observation at all, the data manager needs to create one. In these cases, coordinates cannot be estimated, but the start/stop time may be known or estimated from other data. However, when a date and time are provided (when Description is not NULL), it is normally a rule that coordinates must also be provided (Position cannot be NULL). To manage this conflict, the boolean column BE_Has_Coords is used. When FALSE, the Position must be NULL and the Description is allowed to be non-NULL[288]. When BE_Has_Coords is TRUE or NULL and the Description is not NULL, the Position cannot be NULL, as usual.

The BE_Has_Coords column is only used for begins and ends of observation bouts. This column must be NULL for all other rows.

The data collection protocols require that each observation team record ascent and descent times and groves for their first and last observations of each group for the day. When more than one observation team observes a single group on a given day then the data manager must choose which observation team's ascent/descent information is to be used.[289]The unused ascent and descent information must be removed from the uploaded data. This requires removing the grove from the end of the waypoint text, in the Name column, and deleting the line denoting median descent/ascent time. This should leave a single beginning of observation/end of observation line in the place within the file from which the sleeping grove ascent/descent information has been purged.

The SWERB_UPLOAD view treats a leading P character before grove codes written into the uploaded begin and end lines as an indication that the sleeping grove is probable (SWERB_LOC_DATA.Loc_Status is P) unless the result of removing the leading P produces a code which is not in SWERB_GWS as a grove. In this case the leading P is considered part of the grove code.

Although the field operators enter information after the B and E codes in those waypoints recording the beginning and ending of bouts of observation that do not denote descent from or ascent into sleeping groves, the SWERB_UPLOAD view is unable to process this additional information. The data manager must remove this information from those begin and end lines that occur when observation of the focal group is interrupted for some reason.

When the field team records secondary ascents or descents, when there are subgroups and more than one begin or end is recorded for the group, the data managers must add additional lines to the uploaded data to convert these extra begins or ends into independent bouts of observation[290]. All of these lines, the original secondary ascent or descent lines and the additional lines added by the data manager, must be marked with a TRUE value in a Secondary_AD column. (This column will also have to be added by the data manager.)

The bout of observation created for the secondary ascent or descent must consist only of begin and end rows, no other kinds of observation are allowed. The SWERB_UPLOAD view will generate an error if other kinds of observations are interspersed between the begin and end rows of a secondary ascent or descent.[291]

Secondary ascents and descents must occur during a regular bout of observation -- uploaded rows with a TRUE Secondary_AD value must be preceded by non-secondary begin rows and followed by non-secondary end rows.

Caution

Although row-wise ordering of secondary bouts of observation is enforced by SWERB_UPLOAD there is no enforcement of time-wise ordering.

Lone animal sightings must be flagged as such in the SWERB_UPLOAD.Lone_Animal column. The sex of the individual must match the sex indicated in the SWERB_UPLOAD.Lone_Animal column.

The SWERB_UPLOAD view looks up the the sname for lone animal entered as part of the garmincode in the Unksname column of the UNKSNAMES table. If found and the related UNKSNAMES.Lonemale value is M the sname of the lone male is stored in SWERB_DATA.Lone_Animal as a NULL.

Note

As usual not all columns need be present in the uploaded data file and, while the column headings are significant, the order of the columns is not. In particular it is expected that older data using the quad coordinate system will use the Quad column in place of the Position column.

Note

Many columns are may be included in the uploaded data but are ignored. This is to reduce the amount of data manipulation which the data manager must perform on the raw data downloaded from the GPS units.

It is an error to include values in both the Description and the Date columns on the same line.

Definition

Figure 6.132. Query Defining the SWERB_UPLOAD View


SELECT NULL::TEXT AS header
     , NULL::TEXT    AS name
     , NULL::TEXT    AS description
     , NULL::TEXT    AS type
     , NULL::TEXT    AS position
     , NULL::TEXT    AS altitude
     , NULL::TEXT    AS depth
     , NULL::TEXT    AS proximity
     , NULL::TEXT    AS display_mode
     , NULL::TEXT    AS color
     , NULL::TEXT    AS symbol
     , NULL::TEXT    AS facility
     , NULL::TEXT    AS city
     , NULL::TEXT    AS state
     , NULL::TEXT    AS country
     , NULL::TEXT    AS pdop
     , NULL::TEXT    AS accuracy
     , NULL::TEXT    AS quad
     , NULL::TEXT    AS date
     , NULL::TEXT    AS timeest
     , NULL::TEXT    AS source
     , NULL::TEXT    AS lone_animal
     , NULL::TEXT    AS is_effort
     , NULL::BOOLEAN AS secondary_ad
     , NULL::BOOLEAN AS be_has_coords
     , NULL::TEXT    AS notes
  WHERE _raise_babase_exception(
          'Cannot select SWERB_UPLOAD'
          || ': The only use of the SWERB_UPLOAD view is to insert'
          || ' new data into the SWERB portion of babase')
;


Figure 6.133. Entity Relationship Diagram of the SWERB_UPLOAD View

The SWERB_UPLOAD view is used only to insert data into the SWERB portion of Babase. Since it cannot be queried and the semantics of the uploaded file varies by line it has no ER diagram.


Table 6.64. Columns in the SWERB_UPLOAD View

Column Uploads into Description
Header Data in this column is not inserted into Babase. A record of which button was pushed on the GPS unit. It is an error if this value is not either NULL, as would be the case when the column is omitted from the uploaded data, or Waypoint.
Name One or more columns of one or more of SWERB_DEPARTS_DATA, SWERB_DEPARTS_GPS, SWERB_BES, SWERB_DATA, and SWERB_LOC_DATA The data entered by the field operator when recording the waypoint. The entered text not only supplies data but also drives which tables and columns receive the line's data. See above and the Amboseli Baboon Research Project Monitoring Guide.
Description SWERB_DATA.GPS_Datetime and sometimes also SWERB_DATA.Time, or SWERB_DEPARTS_DATA.Date and SWERB_DEPARTS_DATA. Time, or ignored

This is the timestamp, date and time, the GPS unit automatically supplies when the waypoint is taken. With a few exceptions — camp departure rows, median ascent or descent time rows, and observations begins or ends whose BE_Has_Coords is FALSE — this value is stored in SWERB_DATA.GPS_Datetime. In the case of the first of those lines representing departure from camp this is the date and time of departure. In the case of departure lines other than the first this value is ignored. In the case of those lines recording median ascent or descent times, the second begin or end line, the value is stored in SWERB_LOC_GPS.GPS_Datetime. In the case of the first begin and end lines, the line recording the sleeping grove, this value is converted to a time, the seconds truncated to 0, and the result is stored in SWERB_DATA.Time, SWERB_DATA.GPS_Datetime (if BE_Has_Coords is TRUE or NULL), and in SWERB_BES; either in SWERB_BES.Start or SWERB_BES.Stop depending on whether the line is a begin or end line.

If this column is blank ('') or NULL in a departure line then a SWERB_DEPARTS_GPS row will not be created.

The format of this data is yyyy/mm/dd space HH:MM.

Type Ignored The GPS unit supplies more information about the pressed button in this column. This information is ignored.
Position SWERB_DEPARTS_GPS.XYLoc or SWERB_DATA.XYLoc or ignored The geolocation coordinates supplied by the GPS unit. In the case of the first of those lines representing departure from camp this is the place of departure. In most of the remainder of this lines this is the location where the data waypoint was taken. See above for when this information is discarded. The format of this data is 37 M space X-coordinate space Y-coordinate. The XY units are in meters and are always positive.
Altitude SWERB_DEPARTS_GPS.Altitude or SWERB_DATA.Altitude or ignored The altitude supplied by the GPS unit. In the case of the first of those lines representing departure from camp this is the altitude of the place of departure. In most of the remainder of this lines this is the altitude where the data waypoint was taken. See above for when this information is discarded. The format of this data is numeric, possibly followed by a space and then either the characters ft or the character m. This value is in meters, unless the characters ft are present in which case the value is in feet. The SWERB_UPLOAD view converts feet to meters for storage in the database by multiplying by 0.3048.
Depth Ignored This information is ignored.
Proximity Ignored This information is ignored.
Display_Mode Ignored This information is ignored.
Color Ignored This information is ignored.
Symbol Ignored This information is ignored.
Facility Ignored This information is ignored.
City Ignored This information is ignored.
State Ignored This information is ignored.
Country Ignored This information is ignored.
Pdop SWERB_DEPARTS_GPS.PDOP or SWERB_DATA.PDOP or ignored The PDOP supplied by the GPS unit. In the case of the first of those lines representing departure from camp this is the PDOP of the departure reading. In most of the remainder of this lines this is the PDOP of the geolocation reading where the data waypoint was taken. See above for when this information is discarded. The format of this data is numeric.
Accuracy SWERB_DEPARTS_GPS.Accuracy or SWERB_DATA.Accuracy or ignored The accuracy supplied by the GPS unit. In the case of the first of those lines representing departure from camp this is the accuracy of the departure reading. In most of the remainder of this lines this is the accuracy of the geolocation reading where the data waypoint was taken. See above for when this information is discarded. The format of this data is numeric. The units are meters.
Quad SWERB_DATA.Quad or ignored The quad coordinates of the SWERB waypoint reading. It is an error to supply a quad value for departure lines or for the 2nd begin or end lines. In most of the remainder of this lines this is the location where the data waypoint was taken. See above for when this information is discarded. This data must be a valid QUADS.Quad value.
Date Ignored or SWERB_DEPARTS_DATA.Date The date of manually recorded SWERB data, data collected before GPS units were put in service. In the case of departure lines this is the departure date. Data supplied manually by the data manager in order that uploaded data conform to the required rules, as when the field team accidentally omits a begin or end record, may use a value on the Date column in lieu of data values in all the columns automatically supplied by the GPS units. In all other lines the value of this column is ignored. The date may be in any format accepted by PostgreSQL.
Timeest Ignored or SWERB_BES.Btimeest or SWERB_BES.Etimeest Whether the begin or end line was entered by the data manager and contains an estimated time. The column must contain no value for those lines that represent something other than the beginning or end of a bout of observation. Since the begin/end time is from the first of the 2 (if 2 are present) begin/end lines only the first begin and end line can have an estimated time. The format is any boolean representation recognized by PostgreSQL. The empty string, an omitted value, is taken to be FALSE.
Source Ignored or SWERB_BES.Bsource or SWERB_BES.Esource How the data manager obtained the begin or end time. This must be a SWERB_TIME_SOURCES.Source value, or blank. This column must contain no value (the empty string or NULL) for those lines that represent something other than the beginning or end of a bout of observation. Since the the begin/end time is obtained from the first of the 2 (if 2 are present) begin/end lines only the first begin and end line can have a source value. The default value, when the empty string or NULL, is G.
Lone_Animal Nowhere; controls interpretation of the row A legal BIOGRAPH.Sex value. When non-NULL and not empty the row represents a lone animal sighting and is interpreted as such. When NULL or empty the row is not a lone animal sighting.
Is_Effort SWERB_BES.Is_Effort Must be NULL or the empty string unless the line represents the start of a bout of observation, and is the first start line when there are more than one. Whether the bout of observation is to be counted toward total observer effort. Defaults to FALSE when not supplied with the first line representing the beginning of a bout of observation.[a]
Secondary_AD Nowhere; controls interpretation of the row A boolean value. All PostgreSQL boolean representations are accepted. When TRUE the row represents a secondary ascent or descent, presumably of a subgroup, and a separate bout of observation will be created. When FALSE or NULL the row is not part of a secondary ascent or descent observation.
BE_Has_Coords Nowhere; controls interpretation of the row A boolean value. All PostgreSQL boolean representations are accepted. When FALSE the row represents a start or stop of observation that was not recorded in a GPS unit but whose time was nontheless estimated from other available data. This is the only case where a Description can be provided while the Position is NULL. When TRUE or NULL, the row is not one of these unusual starts/stops.
Notes SWERB_BES.Notes Must be NULL or the empty string unless the line is the first line representing the start of a bout of observation. Any notes regarding the bout of observation. Replaces any existing value. Defaults to NULL and is changed to NULL when the empty string.

[a] Lines representing sightings of "other" (non-focal) groups and lone individuals are not interpreted as lines beginning observation bouts, even though these events are recorded in SWERB_BES as independent bouts of observation. Because of this, an Is_Effort value cannot be supplied for these events, and therefore will default to FALSE.


Operations Allowed

Only INSERT is allowed on SWERB_UPLOAD, SELECT, UPDATE, and DELETE are not allowed. Inserting a row into SWERB_UPLOAD inserts a rows into SWERB tables as described above.



[281] Because sleeping grove information is entered into the GPS units as 2 separate waypoints, which spreads the sleeping grove information over two lines of uploaded data, the A and D codes are always used when the SWERB_LOC_DATA row is created by the SWERB_UPLOAD view. The SWERB_LOC_DATA.ADcode value is then updated to the correct value when the 2nd SWERB waypoint is processed.

The alternative to this temporary use of the A and D codes is to allow the SWERB_LOC_DATA.ADcode to be NULL until the transaction is committed, and to defer related checks until transaction commit.

[282] This is due to the 10 character data entry limit in the GPS units. The entry of an S character when recording drinking events would cause the 10 character limit to be exceeded (when the waterhole codes are 4 characters, as they often are) so the SWERB_UPLOAD view uses this method to guess whether a subgroup was observed..

[283] Unusual to those familiar with the data collection protocol. We don't mention things that might be surprising to the casual observer.

[284] This is not ideal. Or rather, the approach is sound but the practice deficient. The Amboseli Baboon Research Project Monitoring Guide's description of the waypoint text entered by the operator could use some work.

[285] The only time this is an issue is when a team is observing more than one group at one time. In this case manual intervention on the part of the data manager is also required to avoid double counting observer effort. See the Is_Effort column.

[286] The SWERB_UPLOAD view does not actively test for these conditions, it assumes that they exist. In the normal course of events it is unlikely, but possible, to insert invalid data into SWERB when these conditions are violated.

[287] It is possible to improve the algorithm used to better discern valid observer codes. This would reduce the need for manual intervention on the part of the data manager at the cost of increased complexity in the code.

[288] In this case, it is assumed but not required that the data manager will also make good use of the Timeest and Source columns.

[289] Or at least this is the ideal. In actual practice it is difficult for the data managers to know when a group has been observed by more than one team on any given day.

[290] Bouts of observation of zero-length, although the system does not require this. Note that the Is_Effort column may be of interest in these cases.

[291] Note that the rule which requires a strict time-wise ordering of the uploaded rows does not apply to the begin and end rows marked as secondary ascents and descents. This allows the creation of secondary ascent and descent bouts which are of non-zero length, should such need to exist.


Page generated: 2024-03-06T15:02:38-05:00.