Revision 5982
Added by Aaron Marcuse-Kubitza over 12 years ago
| inputs/SALVIAS/verify/plots.ref.sql | ||
|---|---|---|
| 2 | 2 |
SELECT count(*) AS count FROM projects; |
| 3 | 3 |
|
| 4 | 4 |
SELECT 'projects' AS ___; |
| 5 |
SELECT CAST(project_name AS text) AS project FROM projects ORDER BY project;
|
|
| 5 |
SELECT CAST(project_name AS text) AS projectname FROM projects ORDER BY projectname;
|
|
| 6 | 6 |
|
| 7 | 7 |
SELECT 'each project''s # locations' AS ___; |
| 8 | 8 |
SELECT |
| 9 |
CAST(project_name AS text) AS project |
|
| 9 |
CAST(project_name AS text) AS projectname
|
|
| 10 | 10 |
, count("PlotID") AS locations_count
|
| 11 | 11 |
FROM projects |
| 12 | 12 |
JOIN "plotMetadata" ON "plotMetadata".project_id = projects.project_id |
| 13 |
GROUP BY project |
|
| 14 |
ORDER BY project |
|
| 13 |
GROUP BY projectname
|
|
| 14 |
ORDER BY projectname
|
|
| 15 | 15 |
; |
| 16 | 16 |
|
| 17 | 17 |
SELECT '# locations' AS ___; |
| ... | ... | |
| 23 | 23 |
|
| 24 | 24 |
SELECT '1st methods' AS ___; |
| 25 | 25 |
SELECT DISTINCT |
| 26 |
CAST(project_name AS text) AS project |
|
| 26 |
CAST(project_name AS text) AS projectname
|
|
| 27 | 27 |
, "PlotMethod" AS method |
| 28 | 28 |
FROM projects |
| 29 | 29 |
JOIN "plotMetadata" ON "plotMetadata".project_id = projects.project_id |
| 30 |
ORDER BY project, method |
|
| 30 |
ORDER BY projectname, method
|
|
| 31 | 31 |
LIMIT 10; |
| 32 | 32 |
|
| 33 | 33 |
SELECT '1st observation measures' AS ___; |
| 34 | 34 |
SELECT DISTINCT |
| 35 |
CAST(project_name AS text) AS project |
|
| 35 |
CAST(project_name AS text) AS projectname
|
|
| 36 | 36 |
, "lookup_MethodCode"."Description" AS observationmeasure |
| 37 | 37 |
FROM projects |
| 38 | 38 |
JOIN "plotMetadata" ON "plotMetadata".project_id = projects.project_id |
| 39 | 39 |
JOIN "lookup_MethodCode" ON "lookup_MethodCode"."MethodCode" = "plotMetadata"."MethodCode" |
| 40 |
ORDER BY project, observationmeasure |
|
| 40 |
ORDER BY projectname, observationmeasure
|
|
| 41 | 41 |
LIMIT 10; |
| 42 | 42 |
|
| 43 | 43 |
/* |
| inputs/SALVIAS/verify/plots.ref | ||
|---|---|---|
| 4 | 4 |
22 |
| 5 | 5 |
___ |
| 6 | 6 |
projects |
| 7 |
project |
|
| 7 |
projectname
|
|
| 8 | 8 |
ACA Amazon Forest Inventories |
| 9 | 9 |
Bonifacino Forest Transects |
| 10 | 10 |
Boyle Transects |
| ... | ... | |
| 29 | 29 |
test |
| 30 | 30 |
___ |
| 31 | 31 |
each project's # locations |
| 32 |
project locations_count |
|
| 32 |
projectname locations_count
|
|
| 33 | 33 |
ACA Amazon Forest Inventories 1 |
| 34 | 34 |
Bonifacino Forest Transects 4 |
| 35 | 35 |
Boyle Transects 37 |
| ... | ... | |
| 61 | 61 |
13663 |
| 62 | 62 |
___ |
| 63 | 63 |
1st methods |
| 64 |
project method |
|
| 64 |
projectname method
|
|
| 65 | 65 |
ACA Amazon Forest Inventories 1 ha, stems >= 10 cm dbh |
| 66 | 66 |
Bonifacino Forest Transects 0.1 ha transect, stems >= 2.5 cm dbh |
| 67 | 67 |
Boyle Transects 0.1 ha transect, stems >= 2.5 cm dbh |
| ... | ... | |
| 74 | 74 |
Enquist Lab Transect Dataset Pseudotransect: collection of leaf samples from species recorded in previous 0.1 ha transect. Species sampled from same location as previous transect "Cerro" (project "OTS Transects"); do not necessarily correspond to same individuals. |
| 75 | 75 |
___ |
| 76 | 76 |
1st observation measures |
| 77 |
project observationmeasure |
|
| 77 |
projectname observationmeasure
|
|
| 78 | 78 |
ACA Amazon Forest Inventories individuals |
| 79 | 79 |
Bonifacino Forest Transects individuals |
| 80 | 80 |
Boyle Transects individuals |
| mappings/verify.plots.sql | ||
|---|---|---|
| 6 | 6 |
; |
| 7 | 7 |
|
| 8 | 8 |
SELECT 'projects' AS ___; |
| 9 |
SELECT projectname AS project
|
|
| 9 |
SELECT projectname |
|
| 10 | 10 |
FROM project |
| 11 | 11 |
WHERE reference_id = |
| 12 | 12 |
(SELECT reference_id FROM reference WHERE shortname = :datasource) |
| 13 |
ORDER BY project |
|
| 13 |
ORDER BY projectname
|
|
| 14 | 14 |
; |
| 15 | 15 |
|
| 16 | 16 |
SELECT E'each project\'s # locations' AS ___; |
| 17 |
SELECT projectname AS project, count(*) AS locations_count
|
|
| 17 |
SELECT projectname, count(*) AS locations_count |
|
| 18 | 18 |
FROM project |
| 19 | 19 |
JOIN locationevent USING (project_id) |
| 20 | 20 |
WHERE project.reference_id = |
| 21 | 21 |
(SELECT reference_id FROM reference WHERE shortname = :datasource) |
| 22 |
GROUP BY project |
|
| 23 |
ORDER BY project |
|
| 22 |
GROUP BY projectname
|
|
| 23 |
ORDER BY projectname
|
|
| 24 | 24 |
; |
| 25 | 25 |
|
| 26 | 26 |
SELECT '# locations' AS ___; |
| ... | ... | |
| 41 | 41 |
|
| 42 | 42 |
SELECT '1st methods' AS ___; |
| 43 | 43 |
SELECT DISTINCT |
| 44 |
projectname AS project
|
|
| 44 |
projectname |
|
| 45 | 45 |
, method.name AS method |
| 46 | 46 |
FROM project |
| 47 | 47 |
JOIN locationevent USING (project_id) |
| 48 | 48 |
JOIN method USING (method_id) |
| 49 | 49 |
WHERE project.reference_id = |
| 50 | 50 |
(SELECT reference_id FROM reference WHERE shortname = :datasource) |
| 51 |
ORDER BY project, method |
|
| 51 |
ORDER BY projectname, method
|
|
| 52 | 52 |
LIMIT 10; |
| 53 | 53 |
|
| 54 | 54 |
SELECT '1st observation measures' AS ___; |
| 55 | 55 |
SELECT DISTINCT |
| 56 |
projectname AS project
|
|
| 56 |
projectname |
|
| 57 | 57 |
, method.observationmeasure AS observationmeasure |
| 58 | 58 |
FROM project |
| 59 | 59 |
JOIN locationevent USING (project_id) |
| 60 | 60 |
JOIN method USING (method_id) |
| 61 | 61 |
WHERE project.reference_id = |
| 62 | 62 |
(SELECT reference_id FROM reference WHERE shortname = :datasource) |
| 63 |
ORDER BY project, observationmeasure |
|
| 63 |
ORDER BY projectname, observationmeasure
|
|
| 64 | 64 |
LIMIT 10; |
Also available in: Unified diff
mappings/verify.plots.sql, inputs/SALVIAS/verify/plots.ref.sql: Renamed project column to projectname to avoid confusion with the project table