Project

General

Profile

Plot change over time analysis

Queries were run on vegbiendev

datasources with reobservations

  • CTFS
  • FIA
  • Madidi?
  • TEAM?
datasource reobservations?
CTFS yes
CVS no
FIA yes
Madidi ?
NVS no
SALVIAS no
TEAM ?
VegBank no

CTFS

Note that CTFS does not provide stem height data. (Stem.HOM is the height the DBH is measured at3, not the stem height.)

3 "height where diameter measurement was taken" (HOM comments in CTFS data dictionary)
.

Coordinate change between two samplings

Stem coordinates were compared between events 1 and 2 of plot 1 subplot 1:

tag x1_m y1_m x2_m y2_m
260113 16 0.2 16 0.2
260112 18.5 6.1 18.5 6.1
260111 19.4 8.2 19.4 8.2
260110 18.9 9.8 18.9 9.8
260109 16.9 9.6 16.9 9.6
260108 15.8 9.8 15.8 9.8
260107 15.7 7.9 15.7 7.9
260102 19.7 12.5 19.7 12.5
260095 18.6 13.6 18.6 13.6
260094 18 14.1 18 14.1
260093 15.2 14.1 15.2 14.1
260092 16.3 13 16.3 13
260091 15.6 11.6 15.6 11.6
260090 16 11.4 16 11.4
260087 19.6 17 19.6 17
260070 11.3 11 11.3 11
260069 10.9 10.3 10.9 10.3
260063 10.7 4.8 10.7 4.8
260062 12.8 1.8 12.8 1.8
260061 13.5 2.4 13.5 2.4
260052 6.1 3.5 6.1 3.5
260051 9.7 5.2 9.7 5.2
260046 7.9 9.5 7.9 9.5
260045 7.8 9.2 7.8 9.2
260044 5.5 8 5.5 8
260043 5.1 5.2 5.1 5.2
260039 7 12.8 7 12.8
260038 7.4 12.8 7.4 12.8
260025 5.6 18.9 5.6 18.9
260024 5.9 19.2 5.9 19.2
260022 4.8 16.7 4.8 16.7
260016 3.7 10.2 3.7 10.2
260015 3.3 12.7 3.3 12.7
260008 4.6 9.1 4.6 9.1
260007 4.8 9.2 4.8 9.2

query:

SET search_path TO "public.2012-10-23-12-35-09.r5754";
SELECT
tag
, locationevent_1.xposition_m AS x1_m
, locationevent_1.yposition_m AS y1_m
, locationevent_2.xposition_m AS x2_m
, locationevent_2.yposition_m AS y2_m
FROM
(
    SELECT
    tag, xposition_m, yposition_m
    FROM location
    JOIN locationevent USING (location_id)
    JOIN location subplot ON subplot.parent_id = location.location_id
    JOIN locationevent subplot_event ON subplot_event.parent_id = locationevent.locationevent_id AND subplot_event.location_id = subplot.location_id
    JOIN taxonoccurrence ON taxonoccurrence.locationevent_id = subplot_event.locationevent_id
    JOIN aggregateoccurrence USING (taxonoccurrence_id)
    JOIN plantobservation USING (aggregateoccurrence_id)
    JOIN stemobservation USING (plantobservation_id)
    WHERE location.creator_id = 6
    AND location.sourceaccessioncode = '1'
    AND subplot.authorlocationcode = '1'
    AND locationevent.authoreventcode = '1'
    AND tag::integer > 2 -- 1-2 do not appear to be tag #s
) locationevent_1
JOIN
(
    SELECT
    tag, xposition_m, yposition_m
    FROM location
    JOIN locationevent USING (location_id)
    JOIN location subplot ON subplot.parent_id = location.location_id
    JOIN locationevent subplot_event ON subplot_event.parent_id = locationevent.locationevent_id AND subplot_event.location_id = subplot.location_id
    JOIN taxonoccurrence ON taxonoccurrence.locationevent_id = subplot_event.locationevent_id
    JOIN aggregateoccurrence USING (taxonoccurrence_id)
    JOIN plantobservation USING (aggregateoccurrence_id)
    JOIN stemobservation USING (plantobservation_id)
    WHERE location.creator_id = 6
    AND location.sourceaccessioncode = '1'
    AND subplot.authorlocationcode = '1'
    AND locationevent.authoreventcode = '2'
    AND tag::integer > 2 -- 1-2 do not appear to be tag #s
) locationevent_2
USING (tag)

Note that the stem coordinates did not change between the two samplings, as the following query returns no rows:

[insert above query]
WHERE locationevent_1.xposition_m != locationevent_2.xposition_m

The location.creator_id 6 was obtained with the following query:

SET search_path TO "public.2012-10-23-12-35-09.r5754";
SELECT party_id FROM party
WHERE COALESCE(organizationname, '\\N') = 'CTFS'
AND creator_id = party_id

SALVIAS

Stem height change between two samplings

SALVIAS does not store a unique identifier for the plot itself, only the plot event, so coordinates were used to identify plots instead.

The following plots had multiple samplings at the same coordinates, and provided stem tag and height data:

LatDec LongDec PlotIDs count
-14.4042 -61.1439 {480,481} 313
-13.9136 -60.8161 {471,472} 222
-15.2461 -61.2428 {458,459} 218
-13.8858 -60.0667 {473,474} 189

query:

SELECT "LatDec", "LongDec", array_agg(DISTINCT "PlotID") AS "PlotIDs", count(*)
FROM "SALVIAS"."plotMetadata" 
JOIN "SALVIAS"."plotObservations" USING ("PlotID")
WHERE
x_position IS NOT NULL
AND y_position IS NOT NULL
AND height_m IS NOT NULL
AND height_m != 9.9 -- invalid height
AND "Line" IS NOT NULL
GROUP BY "LatDec", "LongDec" 
HAVING array_length(array_agg(DISTINCT "PlotID"), 1) > 1
ORDER BY count(*) DESC

Unfortunately, none of these plots had any overlapping stem tags, indicating that they were not the same plot even though they had the same coordinates.

The following plots had multiple samplings at the same coordinates, and provided stem coordinate and height data:

LatDec LongDec PlotIDs count
-14.4042 -61.1439 {480,481} 312
-15.2461 -61.2428 {458,459} 218
-13.8858 -60.0667 {473,474} 189

query:

SELECT "LatDec", "LongDec", array_agg(DISTINCT "PlotID") AS "PlotIDs", count(*)
FROM "SALVIAS"."plotMetadata" 
JOIN "SALVIAS"."plotObservations" USING ("PlotID")
WHERE
x_position IS NOT NULL
AND y_position IS NOT NULL
AND height_m IS NOT NULL
AND height_m != 9.9 -- invalid height
AND "Line" IS NOT NULL
GROUP BY "LatDec", "LongDec" 
HAVING array_length(array_agg(DISTINCT "PlotID"), 1) > 1
ORDER BY count(*) DESC

Unfortunately, stem heights did not have a reasonable change between events, indicating that they were not the same plot even though they had the same coordinates:

xposition_m yposition_m height1_m height2_m
0 16 3 8
1 9 8 8
1 10 6 5
2 17 8 7
3 11 7 9
3 17 8 9
4 8 8 9
4 15 8 8
4 18 4 9
4 20 9 8
5 17 7 9
6 1 7 7
6 9 9 9
6 18 9 7
7 4 9 8
7 12 8 7
7 18 9 6
8 6 8 9
8 10 8 8
8 14 8 8
8 16 7 7
9 3 6 9
9 4 4 6
9 7 9 4
9 9 9 9
9 17 9 7
10 15 9 7
11 5 6 6
11 13 8 6
11 18 8 9
12 16 9 6
13 13 9 5
13 14 9 4
14 3 2 6
14 7 8 9
14 16 6 6
14 18 7 7
15 2 6 6
15 8 8 7
16 4 6 9
16 6 9 8
16 10 8 9
17 7 7 4
18 0 7 6
18 6 6 8
18 8 8 5
18 13 9 8
18 18 9 8
19 7 8 8
20 5 5 8
20 14 8 7

query:

SET search_path TO "public.2012-10-23-12-35-09.r5754";
SELECT
  xposition_m
, yposition_m
, event_1.height_m AS height1_m
, event_2.height_m AS height2_m
FROM
(
    SELECT
    DISTINCT ON (xposition_m, yposition_m)
    xposition_m, yposition_m, height_m
    FROM location
    JOIN location subplot ON subplot.parent_id = location.location_id
    JOIN locationevent subplot_event ON subplot_event.location_id = subplot.location_id
    JOIN taxonoccurrence ON taxonoccurrence.locationevent_id = subplot_event.locationevent_id
    JOIN aggregateoccurrence USING (taxonoccurrence_id)
    JOIN plantobservation USING (aggregateoccurrence_id)
    JOIN stemobservation USING (plantobservation_id)
    WHERE location.creator_id = 18
    AND height_m IS NOT NULL
    AND height_m != 9.9 -- invalid height
    AND location.sourceaccessioncode = '480'
) event_1
JOIN
(
    SELECT
    DISTINCT ON (xposition_m, yposition_m)
    xposition_m, yposition_m, height_m
    FROM location
    JOIN location subplot ON subplot.parent_id = location.location_id
    JOIN locationevent subplot_event ON subplot_event.location_id = subplot.location_id
    JOIN taxonoccurrence ON taxonoccurrence.locationevent_id = subplot_event.locationevent_id
    JOIN aggregateoccurrence USING (taxonoccurrence_id)
    JOIN plantobservation USING (aggregateoccurrence_id)
    JOIN stemobservation USING (plantobservation_id)
    WHERE location.creator_id = 18
    AND height_m IS NOT NULL
    AND height_m != 9.9 -- invalid height
    AND location.sourceaccessioncode = '481'
) event_2
USING (xposition_m, yposition_m)

The census_date also cannot be used to distinguish resamplings of subplots, because there is only one census_date for each subplot2.

2 The following query returns no rows:

SELECT "PlotID", "Line", count(DISTINCT census_date) AS census_date_count
FROM "SALVIAS".organisms
WHERE "Line" IS NOT NULL AND census_date IS NOT NULL
GROUP BY "PlotID", "Line" 
HAVING count(DISTINCT census_date) > 1

The location.creator_id 18 was obtained with the following query:

SET search_path TO "public.2012-10-23-12-35-09.r5754";
SELECT party_id FROM party
WHERE COALESCE(organizationname, '\\N') = 'SALVIAS'
AND creator_id = party_id

Madidi

Madidi does not store a unique identifier for the plot itself, only the plot event, so coordinates were used to identify plots instead.

The following plots had multiple samplings at the same coordinates, and provided stem tag and height data:

latitude_deg longitude_deg locations count
-16.2166667 -66.4 {PT_Mosete_443,PT_Mosete_446} 725

query:

SET search_path TO /*r9897*/public;
SELECT latitude_deg, longitude_deg, array_agg(DISTINCT location.authorlocationcode) AS "locations", count(*)
FROM location
JOIN locationplace USING (location_id)
JOIN place USING (place_id)
JOIN coordinates USING (coordinates_id)
JOIN location subplot ON subplot.parent_id = location.location_id
JOIN locationevent subplot_event ON subplot_event.location_id = subplot.location_id
JOIN taxonoccurrence ON taxonoccurrence.locationevent_id = subplot_event.locationevent_id
JOIN aggregateoccurrence USING (taxonoccurrence_id)
JOIN plantobservation USING (aggregateoccurrence_id)
JOIN stemobservation USING (plantobservation_id)
WHERE location.source_id = source_by_shortname('Madidi')
AND tag IS NOT NULL
AND height_m IS NOT NULL
GROUP BY latitude_deg, longitude_deg
HAVING array_length(array_agg(DISTINCT location.authorlocationcode), 1) > 1
ORDER BY count(*) DESC

VegBank

Each plot has exactly one plot event (observation), so plot reobservations cannot be used to find resamplings

TEAM

Permanent plots, so should include resamplings