Contains one row for every row in INTERACT_DATA. Each row contains a column for the
actor and a column for the actee. The actor and actee are
retrieved from the PARTS table, when there
is no related parts row the actor or actee is NULL.
This view is somewhat useful for the maintenance and analysis of social interaction data.[261] It's primarily optimized for speed and so finds its best use when writing queries.
This view indicates whether
each interaction can be considered "representative".
Representative interactions are those collected as part of a
"representative interactions" protocol, which involves
observers collecting all interactions in their line of sight
in the course of collecting focal samples on all individuals
of a given age-sex class in a random order. The observer
cannot possibly record all the
interactions that occur in the group, but the interactions
collected this way are considered a representative
sample of all the interactions. For each
interaction, if there are any focal samples (in SAMPLES) that are i) on the same Date, ii) with
the same Observer, iii) in the same group (Actor_Grp or
Actee_Grp), and iv) follow a representative interactions
protocol, then that interaction can be considered
representative and its Repr_Interxn will be TRUE. Only
representative interactions can be used to calculate the
relative frequencies with which interactions occur.
Note
It is still possible to see all-occurrences data for focal individuals, as described earlier.
Tip
Resist the urge to think of the "representative" label as an indication of the quality of the observation. If the quality of an observation were in question, it would not be added to Babase at all.
Figure 6.65. Query Defining the ACTOR_ACTEES View
WITH sampling AS (SELECT DISTINCT samples.date
, samples.observer
, members.grp
FROM samples
JOIN stypes
ON stypes.stype = samples.stype
AND stypes.repr_interxns
JOIN members
ON members.sname = samples.sname
AND members.date = samples.date)
SELECT interact_data.iid AS iid
, interact_data.sid AS sid
, interact_data.act AS act
, interact_data.date AS date
, interact_data.start AS start
, interact_data.stop AS stop
, interact_data.observer AS observer
, actor.partid AS actorid
, COALESCE(actor.sname, '998'::CHAR(3)) AS actor
, actorms.grp AS actor_grp
, actee.partid AS acteeid
, COALESCE(actee.sname, '998'::CHAR(3)) AS actee
, acteems.grp AS actee_grp
, interact_data.handwritten AS handwritten
, interact_data.exact_date AS exact_date
, EXISTS(SELECT 1
FROM sampling
WHERE sampling.date = interact_data.date
AND sampling.observer = interact_data.observer
AND sampling.grp IN (actorms.grp, acteems.grp)) AS repr_interxn
FROM interact_data
LEFT OUTER JOIN parts AS actor
ON (actor.iid = interact_data.iid AND actor.role = 'R')
LEFT OUTER JOIN parts AS actee
ON (actee.iid = interact_data.iid AND actee.role = 'E')
LEFT OUTER JOIN members AS actorms
ON (actorms.sname = actor.sname
AND actorms.date = interact_data.date)
LEFT OUTER JOIN members AS acteems
ON (acteems.sname = actee.sname
AND acteems.date = interact_data.date);
Table 6.30. Columns in the ACTOR_ACTEES View
| Column | From | Description |
|---|---|---|
| Iid | INTERACT_DATA.Iid | Identifier of the interaction. |
| Sid | INTERACT_DATA.Sid | Identifier of the sample, if any, during which the data was collected. |
| Act | INTERACT_DATA.Act | The kind of interaction. |
| Date | INTERACT_DATA.Date | The date of the interaction. |
| Start | INTERACT_DATA.Start | The time the interaction began. |
| Stop | INTERACT_DATA.Stop | The time the interaction ended. |
| Observer | INTERACT_DATA.Observer | The observer who recorded the interaction. |
| Actorid | PARTS.Partid | The Partid of the actor's PARTS row. |
| Actor | PARTS.Sname | The Sname of the
actor in the interaction, when there is a PARTS row for the actor. Otherwise, the
value 998. |
| Actor_Grp | MEMBERS.Grp | The Grp of the actor on the date of the interaction. |
| Acteeid | PARTS.Partid | The Partid of the actee's PARTS row. |
| Actee | PARTS.Sname | The Sname of the
actee in the interaction, when there is a PARTS row for the actee. Otherwise, the
value 998. |
| Actee_Grp | MEMBERS.Grp | The Grp of the actee on the date of the interaction. |
| Handwritten | INTERACT_DATA.Handwritten | Whether or not the interaction was recorded in handwritten records. |
| Exact_Date | INTERACT_DATA.Exact_Date | Whether this row's Date is the specific day
that the interaction occurred (TRUE) or only the
year and month of the interaction (FALSE). |
| Repr_Interxn | Subquery — see Figure 6.66. | Whether this Observer performed any randomized focal samples in this group (Actor_Grp or Actee_Grp) on this Date |
The Actor_Grp and Actee_Grp columns are computed.
Attempts to put a value into these columns raise an error if
the new value is not NULL or does not correspond to the
computed value.
The Repr_Interxns column is also computed. Attempts to put a value in this column are silently ignored.
Tip
Best practice is to omit computed columns from inserts and updates.
The Actor and Actee columns must not be NULL or
998 when inserting
into or updating this view.
When inserting or updating ACTOR_ACTEES the values of
the Actor_Grp and Actee_Grp columns must either be NULL or
match the group recorded for the individual for that day in
MEMBERS, if such a row exists. Inserting
and updating ACTOR_ACTEES cannot affect group
membership.
Tip
It is usually a good idea to leave the computed
columns unspecified (NULL) when maintaining social
interactions using this view.
Inserting a row into ACTOR_ACTEES inserts a row into INTERACT_DATA and two rows, one for the actor and one for the actee, into PARTS, as expected.
To insert an actor without an actee (or vice versa)
use a NULL value for the Actee (or Actor).
Tip
When entering a new social interaction it is
usually a good idea to leave Iid unspecified (or
specified as NULL). In this case Babase will compute
a new Iid and use it
appropriately in the new PARTS rows.
Likewise the Actorid and Acteeid columns are usually
best left NULL, in which case Babase will also create
appropriate values.
Updating a row in ACTOR_ACTEES updates the underlying columns in INTERACT_DATA and PARTS, as expected.
An actor or actees PARTS row can
be deleted or inserted by UPDATE of ACTOR_ACTEES. To
insert an new PARTS row supply a Sname for the actor or
actee where there was none. To delete a actor or or actee
set the Sname to NULL.
When deleting an actor or actee either the
corresponding Actorid/Acteeid value must be set to NULL,
or the corresponding Actorid/Acteeid value must be
unaltered.
Tip
The Actor and Actee cannot be switched with a update operation.[262] Delete the interaction and re-create it instead.
Deleting a row in ACTOR_ACTEES deletes the underlying row in INTERACT_DATA and the two underlying rows in PARTS, as expected.
Contains one row for every row in INTERACT_DATA. There is no difference between this view and the INTERACT_DATA table, other than the view extends the INTERACT_DATA table with additional date and time columns that transform the corresponding INTERACT_DATA columns in useful and interesting ways, and the view is sorted by Iid.
Figure 6.67. Query Defining the INTERACT View
SELECT iid AS iid
, interact_data.sid AS sid
, interact_data.act AS act
, acts.class AS class
, interact_data.date AS date
, julian(interact_data.date) AS jdate
, interact_data.start AS start
, spm(interact_data.start) AS startspm
, stop AS stop
, spm(interact_data.stop) AS stopspm
, interact_data.observer AS observer
, interact_data.handwritten AS handwritten
, interact_data.exact_date AS exact_date
FROM interact_data
JOIN acts
ON (acts.act = interact_data.act);
Table 6.31. Columns in the INTERACT View
| Column | From | Description |
|---|---|---|
| Iid | INTERACT_DATA.Iid | Identifier of the interaction. |
| Sid | INTERACT_DATA.Sid | Identifier of the point observation collection, if any, during which the data was collected. |
| Act | INTERACT_DATA.Act | The kind of interaction. |
| Date | INTERACT_DATA.Date | The date of the interaction. |
| Jdate | INTERACT_DATA.Date (computed) | The date of the interaction, in Julian date form. |
| Start | INTERACT_DATA.Start | The time the interaction began. |
| Startspm | INTERACT_DATA.Start (computed) | The time the interaction begin (Start), represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment. |
| Stop | INTERACT_DATA.Stop | The time the interaction ended. |
| Stopspm | INTERACT_DATA.Stop (computed) | The time the interaction ended (Stop), represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment. |
| Observer | INTERACT_DATA.Observer | The observer who recorded the interaction. |
| Handwritten | INTERACT_DATA.Handwritten | Whether or not the interaction was recorded in handwritten records. |
| Exact_Date | INTERACT_DATA.Exact_Date | Whether this row's Date is the specific day
that the interaction occurred
(TRUE) or only the year and month
of the interaction (FALSE). |
Warning
Any modifications to the Jdate, Startspm, or Stopspm columns are silently ignored.
Inserting a row into INTERACT inserts a row into INTERACT_DATA, as expected.
Updating a row in INTERACT updates the underlying columns in INTERACT_DATA, as expected.
Deleting a row in INTERACT deletes the underlying row in INTERACT_DATA.
Contains one row for every row in the INTERACT view. There is no difference between this view and the INTERACT view, other than that this view is sorted by Iid.
Figure 6.69. Query Defining the INTERACT_SORTED View
SELECT iid AS iid
, interact_data.sid AS sid
, interact_data.act AS act
, acts.class AS class
, interact_data.date AS date
, julian(interact_data.date) AS jdate
, interact_data.start AS start
, spm(interact_data.start) AS startspm
, interact_data.stop AS stop
, spm(interact_data.stop) AS stopspm
, interact_data.observer AS observer
, interact_data.handwritten AS handwritten
, interact_data.exact_date AS exact_date
FROM interact_data
JOIN acts
ON (acts.act = interact_data.act)
ORDER BY iid;
Table 6.32. Columns in the INTERACT_SORTED View
| Column | From | Description |
|---|---|---|
| Iid | INTERACT_DATA.Iid | Identifier of the interaction. |
| Sid | INTERACT_DATA.Sid | Identifier of the point observation collection, if any, during which the data was collected. |
| Act | INTERACT_DATA.Act | The kind of interaction. |
| Date | INTERACT_DATA.Date | The date of the interaction. |
| Jdate | INTERACT_DATA.Date (computed) | The date of the interaction, in Julian date form. |
| Start | INTERACT_DATA.Start | The time the interaction began. |
| Startspm | INTERACT_DATA.Start (computed) | The time the interaction begin (Start), represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment. |
| Stop | INTERACT_DATA.Stop | The time the interaction ended. |
| Stopspm | INTERACT_DATA.Stop (computed) | The time the interaction ended (Stop), represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment. |
| Observer | INTERACT_DATA.Observer | The observer who recorded the interaction. |
| Handwritten | INTERACT_DATA.Handwritten | Whether or not the interaction was recorded in handwritten records. |
| Exact_Date | INTERACT_DATA.Exact_Date | Whether this row's Date is the specific day
that the interaction occurred
(TRUE) or only the year and month
of the interaction (FALSE). |
The operations allowed are as described in the INTERACT view.
Contains one row for every row in MPI_DATA. Each row contains a column for the
actor and a column for the actee. The actor and actee are
retrieved from the MPI_PARTS table, when
there is no related parts row the actor or actee is NULL.
The MPIS table supplies the date of the
interaction and there are further self-joins to the MPI_DATA, MPIACTS, and MPI_PARTS tables to compute help request
circumstances and outcome.
This view is useful for the analysis of multiparty interaction data.[263].
Figure 6.71. Query Defining the MPI_EVENTS View
SELECT mpis.mpiid AS mpiid
, mpis.date AS date
, mpis.context_type AS context_type
, mpis.context AS context
, mpi_data.mpidid AS mpidid
, mpi_data.seq AS seq
, mpi_data.mpiact AS mpiact
, actor.mpipid AS actorid
, actor.sname AS actor
, actor.unksname AS unkactor
, actee.mpipid AS acteeid
, actee.sname AS actee
, actee.unksname AS unkactee
, CASE WHEN EXISTS(SELECT 1
FROM mpiacts
WHERE mpiacts.mpiact = mpi_data.mpiact
AND mpiacts.kind = 'H')
THEN
EXISTS(SELECT 1
FROM mpi_data AS request
, mpiacts
, mpi_parts AS requestor
, mpi_parts AS requestee
WHERE request.mpiid = mpi_data.mpiid
AND request.seq < mpi_data.seq
AND mpiacts.mpiact = request.mpiact
AND mpiacts.kind = 'R'
AND requestor.mpidid = request.mpidid
AND requestor.role = 'R'
AND requestor.sname = actee.sname
AND requestee.mpidid = request.mpidid
AND requestee.role = 'E'
AND requestee.sname = actor.sname)
ELSE
NULL
END AS solicited
, EXISTS(SELECT 1
FROM mpi_data AS initial,
mpiacts
WHERE initial.mpiid = mpi_data.mpiid
AND initial.seq = 1
AND mpiacts.mpiact = initial.mpiact
AND mpiacts.decided)
AS decided
, mpi_data.helped AS helped
, mpi_data.active AS active
FROM mpis
LEFT OUTER JOIN mpi_data ON (mpis.mpiid = mpi_data.mpiid)
LEFT OUTER JOIN mpi_parts AS actor
ON (actor.mpidid = mpi_data.mpidid AND actor.role = 'R')
LEFT OUTER JOIN mpi_parts AS actee
ON (actee.mpidid = mpi_data.mpidid AND actee.role = 'E');
Table 6.33. Columns in the MPI_EVENTS View
| Column | From | Description |
|---|---|---|
| Mpiid | MPIS.Mpiid | Identifier of the multiparty interaction collection. |
| Date | MPIS.Date | The date of the multiparty interaction collection. |
| Context_type | MPIS.Context_type | The context type of the multiparty interaction collection. |
| Context | MPIS.MPIS-Context | Text describing the context of the multiparty interaction collection. |
| Mpidid | MPI_DATA.Mpidid | Identifier of the dyadic interaction. |
| Seq | MPI_DATA.Seq | Number that orders the dyadic interaction in
time within the multiparty interaction collection.
The first dyadic interaction has a Seq value of
1, the second a Seq value of
2, etc. Identical Seq values
indicate interactions which occurred
simutainously. |
| Mpiact | MPI_DATA.MPIAct | The kind of dyadic interaction. |
| Actorid | MPI_PARTS.Mpipid | The Mpipid of the actor's MPI_PARTS row. |
| Actor | MPI_PARTS.Sname | The Sname of the actor in the interaction. |
| Unkactor | MPI_PARTS.Unksname | The PARTUNKS.Unksname code which denotes why the actor is unknown. |
| Acteeid | MPI_PARTS.Mpipid | The Mpipid of the actee's MPI_PARTS row. |
| Actee | MPI_PARTS.Sname | The Sname of the actee in the interaction. |
| Unkactee | MPI_PARTS.Unksname | The PARTUNKS.Unksname code which denotes why the actee is unknown. |
| Solicited | Computed | A boolean: TRUE or FALSE, or NULL.
NULL when the act is not an act of help (MPIACTS.MPIAct
is FALSE). Whether or not the help given was
solicited with a request for help -- whether MPIACTS.Kind
is R, from a
previous (having a smaller MPI_DATA.Seq)
MPI act's MPI_DATA.MPIAct value where the actor
and actee match. For more details see the Figure 6.71. |
| Decided | Computed | A boolean: TRUE or FALSE. Whether or not
the result of the MPI was decided when the event took
place -- obtained from the MPIACTS.Decided value of the MPI_DATA.MPIAct of the first (MPI_DATA.Seq =
1) interaction of the MPI. |
| Helped | MPI_DATA.Helped | Whether or not help was given in response to
the request for help. NULL when the event is not a
request for help. |
| Active | MPI_DATA.Active | Whether the help given was active or passive.
NULL when the event is not a request for help or no
help was forthcoming. |
Caution
Attempts to change the computed columns, Solicited and Decided, are silently ignored.
Tip
It is usually a good idea to leave the computed
columns, as well as the automatically assigned ID columns,
unspecified (NULL) when maintaining social interactions
using this view.
Inserting a row into MPI_EVENTs inserts a row into MPI_DATA and two rows, one for the actor and one for the actee, into MPI_PARTS, as expected. It also may insert a row into the MPIS table.
Warning
The presence or absence of a Mpiid value
determines whether or not a MPIS row
is created. When Mpiid is NULL (or the column is not
specified) a new MPIS row is created.
When a non-NULL value is supplied the given Mpiid
identifies the existing multiparty interaction
collection, a MPIS row, to which the
new dyadic interaction is added.
Warning
The value of the Date, Context_type, and MPIS-Context columns are ignored when the supplied Mpiid identifies an existing MPIS row.
Warning
The PostgreSQL nextval()
function cannot be part of an INSERT
expression which assigns a value to this view's Mpiid or
Mpidid columns.
Tip
When entering a new social interaction it is
usually a good idea to leave Mpidid unspecified (or
specified as NULL). In this case Babase will compute
a new Mpiid and use it
appropriately in the new MPI_PARTS
rows. Likewise the Actorid and Acteeid columns are
usually best left NULL, in which case Babase will also
create appropriate values.
Updating a row in MPI_EVENTS updates the underlying columns in MPIS, MPI_DATA, and MPI_PARTS as expected.
Tip
Because updates to the database occur one table at a time, and because Babase does not allow an interaction to have the same individual as both the actor and actee, it is impossible to switch the Actor and Actee with a update operation. Delete the interaction and re-create it instead.
This view returns no rows, it is used only to upload
multiparty interaction data into the MPIS,
MPI_DATA, MPI_PARTS, and
CONSORTS tables. Attempting to
SELECT rows from this view will raise an
error.
This view exists instead of a custom upload program.
Each line in the uploaded file corresponds to one or more dyadic interactions. Each multiparty interaction is represented in the input file by contiguous lines, with these lines ordered so that earlier interactions appear first in the file. The context of the multiparty interaction and the result of any consort context must appear on the first line, and only the first line, of those uploaded lines that make up the MPI.
A single line in the file usually corresponds to a
single dyadic interaction. The exception is when the first
line of the multiparty interaction has an MPI_DATA.MPIAct value
indicating that multiple initial interactions are allowed.
In this case the row represents multiple dyadic
interactions, one for each combination of the Snames in the actor
and actee columns. For example, if there
are 3 actors and 2 actees there will be a total of
6[264] dyadic interactions.
The uploaded file may contain leading or trailing empty lines. No data must be indicated by an empty cell.
The uploaded file must begin with a line of column headings with the names given below in the order given below. The column headings are validated but otherwise unused. This is to assist in the detection of data entry errors. The content of each column is as described.
-
mid -
A number that identifies the row within the uploaded file. These numbers must increase with each row and must be sequential within any one multiparty interaction. Gaps are allowed between multiparty interactions.
This column must contain a value.
This data is not recorded in the database but is checked for validity to assist in detection of data entry errors.
-
coal_id -
A number that identifies the coalition within the uploaded file. All the rows associated with a given multiparty interaction must share the same number. These numbers must not otherwise be re-used within the uploaded file.[265]
This column must contain a value.
This data is not recorded in the database but is checked for validity to assist in detection of data entry errors.
-
grp -
A GROUPS.Gid value. This data is not recorded in the database but is checked to ensure that each input line for a given multiparty interaction contains the same value. This check is done to assist in detection of data entry errors. The data in this column is not otherwise validated.
-
date -
The date of the multiparty interaction. This data is stored in the MPIS.Date database column.
All the rows associated with a given multiparty interaction must contain the same date value. This check is done to assist in detection of data entry errors.
-
actor -
The Snames of the actor(s) that is/are interacting. When there is more than one actor (see above) the Snames of the actors are separated by a comma (
,).This data is stored in the MPI_PARTS.Sname database column, unless the value is is one of those in PARTUNKS.Unksname in which case it is stored in the MPI_PARTS.Unksname column.
-
agg_act -
A code indicating the act performed. These codes are generally MPIACTS values, with the following exceptions.[266]
-
+ -
A
+is changed intoAH. -
? -
A
?is changed intoRE. -
P -
A
Pis changed into aPH.
-
-
recip -
The Snames of the actee(s) that is/are interacting. When there is more than one actee (see above) the Snames of the actees are separated by a comma (
,).This data is stored in the MPI_PARTS.Sname database column, unless the value is is one of those in PARTUNKS.Unksname in which case it is stored in the MPI_PARTS.Unksname column.
-
outcome -
The result of a request for help. The allowed values are:
- (blank)
-
Indicates no data -- the action was not a request for help. A blank entry results in
NULLvalues for MPI_DATA.Helped and MPI_DATA.Active. -
SUCC -
Indicates that active help was given in response to the help request. MPI_DATA.Helped and MPI_DATA.Active are set to
TRUE. -
FAIL -
Indicates an unsuccessful request for help. MPI_DATA.Helped and MPI_DATA.Active are set to
FALSE. -
PASS -
Indicates that passive help was given in response to the help request. MPI_DATA.Helped is set to
TRUEand MPI_DATA.Active set toFALSE.
-
form_passive_aid -
The values in this column are ignored.
-
context -
The MPIS.MPIS-Context value. A value may only appear on the first line of the lines making up the multiparty interaction. When the context_type is
Cthe context value must beCONSORTand aNULLwill be the value entered into the database. This check is done to assist detection of data entry errors. -
consort -
A record of the result of consortship context, if any. A value may only appear on the first line of the lines making up the multiparty interaction. If not blank the consort value has the form: “male1 WITH female;male2 GET female”, or the form “male1 WITH female;male2 KEEP female”. In either case mpi_upload checks to see that both occurrences in the “female” placeholder are identical. When any of the participants are unknown the individual should be a PARTUNKS.Unksname value. When the “KEEP” form is used mpi_upload checks to see that the “male1” and “male2” values are identical.[267]
The “male1” value is recorded in the CONSORTS.Had database column. The “male2” value is recorded in the CONSORTS.Got database column.
-
context_type -
The MPIS.Context_type code. A value may only appear on the first line of the lines making up the multiparty interaction.
Figure 6.73. Query Defining the MPI_UPLOAD View
SELECT NULL::INT AS mid
, NULL::INT AS coal_id
, NULL::TEXT AS grp
, NULL::date AS date
, NULL::TEXT AS actor
, NULL::TEXT AS agg_act
, NULL::TEXT AS recip
, NULL::TEXT AS outcome
, NULL::TEXT AS form_passive_aid
, NULL::TEXT AS context
, NULL::TEXT AS consort
, NULL::TEXT AS context_type
WHERE _raise_babase_exception(
'Cannot select MPI_UPLOAD'
|| ': The only use of the MPI_UPLOAD view is to insert'
|| ' new data into the MPI portion of babase');
Figure 6.74. Entity Relationship Diagram of the MPI_UPLOAD View
Contains one row for every row in POINT_DATA. There is no difference between this view and the POINT_DATA table, other than the view contains additional columns that may be useful derivatives of the Ptime column.
Tip
Use this view instead of the POINT_DATA table.
Figure 6.75. Query Defining the POINTS View
SELECT pntid AS pntid
, sid AS sid
, activity AS activity
, posture AS posture
, foodcode AS foodcode
, ptime AS ptime
, spm(ptime) AS ptimespm
FROM point_data;
Table 6.34. Columns in the POINTS View
| Column | From | Description |
|---|---|---|
| Pntid | POINT_DATA.Pntid | Identifier of the point observation. |
| Sid | POINT_DATA.Sid | Identifier of the sample during which the data was collected. |
| Activity | POINT_DATA.Activity | The kind of activity the focal was engaged in. |
| Posture | POINT_DATA.Posture | The posture of the focal. |
| Foodcode | POINT_DATA.Foodcode | The food eaten, if any. |
| Ptime | POINT_DATA.Ptime | The time the observation was recorded, with a precision of 1 second. |
| Ptimespm | POINT_DATA.Ptime (computed) | The time the point observation was recorded (Ptime) represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment. |
The Ptimespm column is computed. Attempts to put a
value into the Ptimespm column raise an error if the new
value is not NULL or does not correspond to the computed
value.
Tip
Best practice is to omit computed columns from inserts and updates.
Inserting a row into POINTS inserts a row into POINT_DATA, as expected.
Updating a row in POINTS updates the underlying columns in POINT_DATA, as expected.
Deleting a row in POINTS deletes the underlying row in POINT_DATA.
Contains one row for every row in POINTS. There is no difference between this view and the POINTS view, other than this view is sorted by Sid, and within that by Ptime.
Figure 6.77. Query Defining the POINTS_SORTED View
SELECT pntid AS pntid
, sid AS sid
, activity AS activity
, posture AS posture
, foodcode AS foodcode
, ptime AS ptime
, ptimespm AS ptimespm
FROM points
ORDER BY sid, ptime;
Table 6.35. Columns in the POINTS_SORTED View
| Column | From | Description |
|---|---|---|
| Pntid | POINT_DATA.Pntid | Identifier of the point observation. |
| Sid | POINT_DATA.Sid | Identifier of the sample during which the data was collected. |
| Activity | POINT_DATA.Activity | The kind of activity the focal was engaged in. |
| Posture | POINT_DATA.Posture | The posture of the focal. |
| Foodcode | POINT_DATA.Foodcode | The food eaten, if any. |
| Ptime | POINT_DATA.Ptime | The time the observation was recorded, with a precision of 1 second. |
| Ptimespm | POINT_DATA.Ptime (computed) | The time the point observation was recorded (Ptime) represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment. |
The operations allowed are as described in the POINTS view.
Contains one row for every row in SAMPLES. This row is identical to the SAMPLES table except that it has an additional column Grp_of_focal which contains the group of the focal on the sampling date.[268]
Figure 6.79. Query Defining the SAMPLES_GOFF View
SELECT samples.sid AS sid
, samples.date AS date
, samples.stime AS stime
, samples.observer AS observer
, samples.stype AS stype
, samples.grp AS grp
, samples.sname AS sname
, samples.mins AS mins
, samples.minsis AS minsis
, samples.programid AS programid
, samples.setupid AS setupid
, samples.collection_system AS collection_system
, members.grp AS grp_of_focal
FROM members, samples
WHERE members.sname = samples.sname
AND members.date = CAST(samples.date AS DATE);
Table 6.36. Columns in the SAMPLES_GOFF View
| Column | From | Description |
|---|---|---|
| Sid | SAMPLES.Sid | Identifier of the sample. |
| Date | SAMPLES.Date | Date of sample collection. |
| STime | SAMPLES.Stime | Time of sample collection. |
| Observer | SAMPLES.Observer | Observer who collected the sample. |
| SType | SAMPLES.SType | A code indicating the nature of the focal individual and the data collection procedure used. |
| Grp | SAMPLES.Grp | The group the observation team sampled. |
| Sname | SAMPLES.Sname | Identifier of sampled individual. |
| Mins | SAMPLES.Mins | Sample duration in minutes, from start to finish. |
| Minsis | SAMPLES.Minsis | Number of minutes of sample data. |
| Programid | SAMPLES.Programid | Identifer of the software ("program", if any) used with this row's Collection_System to collect the focal sample. |
| Setupid | SAMPLES.Setupid | The configuration file (if any) used by this row's Programid to collect this sample's data. |
| Collection_System | SAMPLES.Collection_System | The device or hardware configuration used to collect the sample. |
| Grp_of_focal | MEMBERS.Grp | The group of the sampled individual. |
[261] Babase should contain one and exactly one actor and one and exactly one actee for every interaction. The ACTOR_ACTEES view does left outer joins of the INTERACT_DATA table with the PARTS table so that invalid data can still be maintained in the event the stated relationships do not exist. To ignore invalid data, e.g. for purposes of analysis, write a query that does regular joins instead of the left outer joins used by the view.
[262] This restriction is leftover from implementation limitations in older versions of PostgreSQL and could now be removed if so desired.
[263] Babase should contain one and exactly one actor and one and exactly one actee for every interaction. The MPI_EVENTS view does left outer joins of the MPI_DATA table with the MPI_PARTS table so that invalid data can be observed in the event the stated relationships do not exist. To ignore invalid data, e.g. for purposes of analysis, write a query that does regular joins instead of the left outer joins used by the view.
[264] 3 * 2 = 6 (Doh!)
[265] Typically the coalition id numbers increase sequentially, but the program does not require this.
[266] It is not usually a good idea to have the upload program perform such simple transformations because it eliminates any flexibility in the codes chosen for use. However in this case there is advantage in having the uploaded files more closely resemble the field data. Right?
[267] Other integrity checks are left to the database to perform.
[268] This is expected to be the group that is sampled, but you never know.