Plot change over time analysis¶

Table of contents
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 at³, not the stem height.)

³ "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 subplot².

² 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

Files (0)

Project

General

Profile

Wiki

Plot change over time analysis¶

datasources with reobservations¶

CTFS¶

Coordinate change between two samplings¶

SALVIAS¶

Stem height change between two samplings¶

Madidi¶

VegBank¶

TEAM¶