Revision 4469
Added by Aaron Marcuse-Kubitza about 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_id AS char) AS project FROM projects ORDER BY project;
|
|
| 5 |
SELECT CAST(project_id AS text) AS project FROM projects ORDER BY project;
|
|
| 6 | 6 |
|
| 7 |
SELECT 'each project\'s # locations' AS ___;
|
|
| 7 |
SELECT 'each project''s # locations' AS ___;
|
|
| 8 | 8 |
SELECT |
| 9 |
CAST(projects.project_id AS char) AS project
|
|
| 10 |
, count(PlotID) AS locations_count
|
|
| 9 |
CAST(projects.project_id AS text) AS project
|
|
| 10 |
, count("PlotID") AS locations_count
|
|
| 11 | 11 |
FROM projects |
| 12 |
JOIN plotMetadata ON plotMetadata.project_id = projects.project_id
|
|
| 12 |
JOIN "plotMetadata" ON "plotMetadata".project_id = projects.project_id
|
|
| 13 | 13 |
GROUP BY project |
| 14 | 14 |
ORDER BY project |
| 15 | 15 |
; |
| 16 | 16 |
|
| 17 | 17 |
SELECT '# locations' AS ___; |
| 18 |
SELECT count(DISTINCT LatDec, LongDec) AS count FROM plotMetadata
|
|
| 18 |
SELECT count(DISTINCT "LatDec"||':'||"LongDec") AS count FROM "plotMetadata"
|
|
| 19 | 19 |
; |
| 20 | 20 |
|
| 21 | 21 |
SELECT '# location events' AS ___; |
| 22 |
SELECT count(*) AS count FROM plotMetadata;
|
|
| 22 |
SELECT count(*) AS count FROM "plotMetadata";
|
|
| 23 | 23 |
|
| 24 | 24 |
SELECT '1st methods' AS ___; |
| 25 | 25 |
SELECT DISTINCT |
| 26 |
CAST(projects.project_id AS char) AS project
|
|
| 27 |
, PlotMethod AS method
|
|
| 26 |
CAST(projects.project_id AS text) AS project
|
|
| 27 |
, "PlotMethod" AS method
|
|
| 28 | 28 |
FROM projects |
| 29 |
JOIN plotMetadata ON plotMetadata.project_id = projects.project_id
|
|
| 29 |
JOIN "plotMetadata" ON "plotMetadata".project_id = projects.project_id
|
|
| 30 | 30 |
ORDER BY project, method |
| 31 | 31 |
LIMIT 10; |
| 32 | 32 |
|
| 33 | 33 |
SELECT '1st observation measures' AS ___; |
| 34 | 34 |
SELECT DISTINCT |
| 35 |
CAST(projects.project_id AS char) AS project
|
|
| 36 |
, lookup_MethodCode.Description AS observationmeasure
|
|
| 35 |
CAST(projects.project_id AS text) AS project
|
|
| 36 |
, "lookup_MethodCode"."Description" AS observationmeasure
|
|
| 37 | 37 |
FROM projects |
| 38 |
JOIN plotMetadata ON plotMetadata.project_id = projects.project_id
|
|
| 39 |
JOIN lookup_MethodCode ON lookup_MethodCode.MethodCode = plotMetadata.MethodCode
|
|
| 38 |
JOIN "plotMetadata" ON "plotMetadata".project_id = projects.project_id
|
|
| 39 |
JOIN "lookup_MethodCode" ON "lookup_MethodCode"."MethodCode" = "plotMetadata"."MethodCode"
|
|
| 40 | 40 |
ORDER BY project, observationmeasure |
| 41 | 41 |
LIMIT 10; |
| 42 | 42 |
|
| 43 | 43 |
/* |
| 44 | 44 |
SELECT p.PlotID, p.SiteCode, COUNT(*) |
| 45 |
FROM projects pr JOIN plotMetadata p JOIN plotObservations o
|
|
| 45 |
FROM projects pr JOIN "plotMetadata" p JOIN plotObservations o
|
|
| 46 | 46 |
ON pr.project_id=p.project_id AND p.PlotID=o.PlotID |
| 47 | 47 |
WHERE p.project_id=2 |
| 48 | 48 |
GROUP BY p.PlotID, p.SiteCode; |
| 49 | 49 |
|
| 50 | 50 |
SELECT NoInd, COUNT(*) AS observations |
| 51 |
FROM plotObservations o JOIN plotMetadata p
|
|
| 51 |
FROM plotObservations o JOIN "plotMetadata" p
|
|
| 52 | 52 |
ON o.PlotID=p.PlotID |
| 53 | 53 |
WHERE project_id=2 |
| 54 | 54 |
GROUP BY NoInd; |
| 55 | 55 |
|
| 56 | 56 |
SELECT p.PlotID, p.SiteCode, SUM(NoInd) AS Individuals |
| 57 |
FROM projects pr JOIN plotMetadata p JOIN plotObservations o
|
|
| 57 |
FROM projects pr JOIN "plotMetadata" p JOIN plotObservations o
|
|
| 58 | 58 |
ON pr.project_id=p.project_id AND p.PlotID=o.PlotID |
| 59 | 59 |
WHERE p.project_id=2 |
| 60 | 60 |
GROUP BY p.PlotID, p.SiteCode; |
| 61 | 61 |
|
| 62 |
SELECT DISTINCT pr.project_id, project_name, PlotMethod, m.MethodCode, m.Description
|
|
| 63 |
FROM projects pr JOIN plotMetadata p JOIN lookup_MethodCode m
|
|
| 64 |
ON pr.project_id=p.project_id AND p.MethodCode=m.MethodCode
|
|
| 65 |
WHERE m.MethodCode=8;
|
|
| 62 |
SELECT DISTINCT "pr".project_id, project_name, "PlotMethod", m."MethodCode", m."Description"
|
|
| 63 |
FROM projects pr JOIN "plotMetadata" p JOIN "lookup_MethodCode" m
|
|
| 64 |
ON pr.project_id=p.project_id AND p."MethodCode"=m."MethodCode"
|
|
| 65 |
WHERE m."MethodCode"=8;
|
|
| 66 | 66 |
|
| 67 | 67 |
SELECT NoInd, COUNT(*) AS observations |
| 68 |
FROM plotObservations o JOIN plotMetadata p JOIN lookup_MethodCode m
|
|
| 69 |
ON o.PlotID=p.PlotID AND p.MethodCode=m.MethodCode
|
|
| 68 |
FROM plotObservations o JOIN "plotMetadata" p JOIN "lookup_MethodCode" m
|
|
| 69 |
ON o.PlotID=p.PlotID AND p."MethodCode"=m."MethodCode"
|
|
| 70 | 70 |
WHERE project_id=2 |
| 71 | 71 |
GROUP BY NoInd; |
| 72 | 72 |
|
| 73 | 73 |
SELECT p.PlotID, p.SiteCode, SUM(NoInd) AS Individuals |
| 74 |
FROM projects pr JOIN plotMetadata p JOIN plotObservations o
|
|
| 74 |
FROM projects pr JOIN "plotMetadata" p JOIN plotObservations o
|
|
| 75 | 75 |
ON pr.project_id=p.project_id AND p.PlotID=o.PlotID |
| 76 | 76 |
WHERE p.project_id=1 |
| 77 | 77 |
GROUP BY p.PlotID, p.SiteCode |
| 78 | 78 |
LIMIT 10; |
| 79 | 79 |
|
| 80 |
SELECT PlotMethod, MethodCode, Description, COUNT(project_id) as projects
|
|
| 80 |
SELECT "PlotMethod", "MethodCode", "Description", COUNT(project_id) as projects
|
|
| 81 | 81 |
FROM ( |
| 82 |
SELECT DISTINCT p.PlotMethod, p.MethodCode, p.Description, p.project_id
|
|
| 82 |
SELECT DISTINCT "p"."PlotMethod", p."MethodCode", p."Description", p.project_id
|
|
| 83 | 83 |
FROM ( |
| 84 |
SELECT project_id, PlotID, PlotMethod, m.MethodCode, m.Description
|
|
| 85 |
FROM plotMetadata p JOIN lookup_MethodCode m
|
|
| 86 |
ON p.MethodCode=m.MethodCode
|
|
| 84 |
SELECT project_id, PlotID, "PlotMethod", m."MethodCode", m."Description"
|
|
| 85 |
FROM "plotMetadata" p JOIN "lookup_MethodCode" m
|
|
| 86 |
ON p."MethodCode"=m."MethodCode"
|
|
| 87 | 87 |
) p JOIN plotObservations o JOIN stems s |
| 88 | 88 |
ON p.PlotID=o.PlotID AND o.PlotObsID=s.PlotObsID |
| 89 | 89 |
GROUP BY project_id |
| 90 | 90 |
) AS a |
| 91 |
GROUP BY PlotMethod, MethodCode, Description;
|
|
| 91 |
GROUP BY "PlotMethod", "MethodCode", "Description";
|
|
| 92 | 92 |
|
| 93 |
SELECT DISTINCT pr.project_id, project_name
|
|
| 94 |
FROM projects pr JOIN plotMetadata p JOIN plotObservations o JOIN stems s
|
|
| 93 |
SELECT DISTINCT "pr".project_id, project_name
|
|
| 94 |
FROM projects pr JOIN "plotMetadata" p JOIN plotObservations o JOIN stems s
|
|
| 95 | 95 |
ON pr.project_id=p.project_id AND p.PlotID=o.PlotID AND o.PlotObsID=s.PlotObsID |
| 96 |
WHERE p.MethodCode=3;
|
|
| 96 |
WHERE p."MethodCode"=3;
|
|
| 97 | 97 |
|
| 98 | 98 |
SELECT p.PlotID, SiteName as plot, COUNT(*) AS stems |
| 99 |
FROM plotMetadata p JOIN plotObservations o JOIN stems s
|
|
| 99 |
FROM "plotMetadata" p JOIN plotObservations o JOIN stems s
|
|
| 100 | 100 |
ON p.PlotID=o.PlotID AND o.PlotObsID=s.PlotObsID |
| 101 | 101 |
WHERE p.project_id=2 |
| 102 | 102 |
GROUP BY p.PlotID, SiteName |
| 103 | 103 |
LIMIT 10; |
| 104 | 104 |
|
| 105 |
SELECT p.PlotID, SiteName AS plot, COUNT(DISTINCT Family, Genus, Species) AS SpeciesCount
|
|
| 106 |
FROM plotMetadata p JOIN plotObservations o
|
|
| 105 |
SELECT p.PlotID, SiteName AS plot, COUNT(DISTINCT "Family", Genus, Species) AS SpeciesCount
|
|
| 106 |
FROM "plotMetadata" p JOIN plotObservations o
|
|
| 107 | 107 |
ON p.PlotID=o.PlotID |
| 108 | 108 |
WHERE project_id=2 |
| 109 | 109 |
GROUP BY p.PlotID, SiteName; |
| 110 | 110 |
|
| 111 |
SELECT DISTINCT p.PlotID, SiteName AS plot, Family, CONCAT_WS(' ',IFNULL(Genus,''), IFNULL(Species,'')) AS SpeciesName
|
|
| 112 |
FROM plotMetadata p JOIN plotObservations o
|
|
| 111 |
SELECT DISTINCT "p".PlotID, SiteName AS plot, Family, CONCAT_WS(' ',IFNULL(Genus,''), IFNULL(Species,'')) AS SpeciesName
|
|
| 112 |
FROM "plotMetadata" p JOIN plotObservations o
|
|
| 113 | 113 |
ON p.PlotID=o.PlotID |
| 114 | 114 |
WHERE p.PlotID=298; |
| 115 | 115 |
|
| 116 | 116 |
SELECT name_status, count(*) FROM plotObservations GROUP BY name_status; |
| 117 | 117 |
|
| 118 |
SELECT DISTINCT p.PlotID, SiteName AS plot, Family,
|
|
| 118 |
SELECT DISTINCT "p".PlotID, SiteName AS plot, Family,
|
|
| 119 | 119 |
CONCAT_WS(' ',IFNULL(Genus,''), IFNULL(Species,'')) AS SpeciesName, name_status
|
| 120 |
FROM plotMetadata p JOIN plotObservations o
|
|
| 120 |
FROM "plotMetadata" p JOIN plotObservations o
|
|
| 121 | 121 |
ON p.PlotID=o.PlotID |
| 122 | 122 |
WHERE p.PlotID=298; |
| 123 | 123 |
|
| 124 |
SELECT DISTINCT p.PlotID, SiteName AS plot, Family,
|
|
| 124 |
SELECT DISTINCT "p".PlotID, SiteName AS plot, Family,
|
|
| 125 | 125 |
CONCAT_WS(' ',IFNULL(Genus,''), IFNULL(Species,'')) AS SpeciesName, name_status
|
| 126 |
FROM plotMetadata p JOIN plotObservations o
|
|
| 126 |
FROM "plotMetadata" p JOIN plotObservations o
|
|
| 127 | 127 |
ON p.PlotID=o.PlotID |
| 128 | 128 |
WHERE p.PlotID=298 AND name_status=1; |
| 129 | 129 |
|
| 130 | 130 |
SELECT p.PlotID, SiteCode AS plot, o.Line as subplot, COUNT(*) AS individuals |
| 131 |
FROM plotMetadata p JOIN plotObservations o
|
|
| 131 |
FROM "plotMetadata" p JOIN plotObservations o
|
|
| 132 | 132 |
ON p.PlotID=o.PlotID |
| 133 | 133 |
WHERE p.PlotID=298 |
| 134 | 134 |
GROUP BY p.PlotID, plot, subplot; |
| 135 | 135 |
|
| 136 | 136 |
SELECT p.PlotID, SiteCode AS plot, o.Line as subplot, SUM(NoInd) AS individuals |
| 137 |
FROM plotMetadata p JOIN plotObservations o
|
|
| 137 |
FROM "plotMetadata" p JOIN plotObservations o
|
|
| 138 | 138 |
ON p.PlotID=o.PlotID |
| 139 | 139 |
WHERE p.PlotID=298 |
| 140 | 140 |
GROUP BY p.PlotID, plot, subplot; |
| 141 | 141 |
|
| 142 | 142 |
SELECT p.PlotID, SiteCode AS plot, o.Line as subplot, SUM(NoInd) AS individuals |
| 143 |
FROM plotMetadata p JOIN plotObservations o
|
|
| 143 |
FROM "plotMetadata" p JOIN plotObservations o
|
|
| 144 | 144 |
ON p.PlotID=o.PlotID |
| 145 | 145 |
WHERE p.PlotID=1 |
| 146 | 146 |
GROUP BY p.PlotID, plot, subplot; |
| 147 | 147 |
|
| 148 |
SELECT COUNT(*) FROM plotMetadata WHERE MethodCode=1;
|
|
| 148 |
SELECT COUNT(*) FROM "plotMetadata" WHERE "MethodCode"=1;
|
|
| 149 | 149 |
|
| 150 |
SELECT PlotID FROM plotMetadata WHERE MethodCode=1 LIMIT 1;
|
|
| 150 |
SELECT PlotID FROM "plotMetadata" WHERE "MethodCode"=1 LIMIT 1;
|
|
| 151 | 151 |
|
| 152 | 152 |
SELECT p.PlotID, SiteCode AS plot, Family, |
| 153 | 153 |
CONCAT_WS(' ',IFNULL(Genus,''), IFNULL(Species,'')) AS SpeciesName, cover_percent
|
| 154 |
FROM plotMetadata p JOIN plotObservations o
|
|
| 154 |
FROM "plotMetadata" p JOIN plotObservations o
|
|
| 155 | 155 |
ON p.PlotID=o.PlotID |
| 156 | 156 |
WHERE p.PlotID=24589; |
| 157 | 157 |
|
| 158 | 158 |
SELECT p.PlotID, p.SiteCode as plot, Country, PolDiv1 AS stateProvince, pol2 AS countyParish, |
| 159 | 159 |
LatDec as latitude, LongDec as longitude, Elev as elevation |
| 160 |
FROM projects pr JOIN plotMetadata p
|
|
| 160 |
FROM projects pr JOIN "plotMetadata" p
|
|
| 161 | 161 |
ON pr.project_id=p.project_id |
| 162 | 162 |
WHERE pr.project_id=1 |
| 163 | 163 |
LIMIT 20; |
| 164 | 164 |
|
| 165 | 165 |
SELECT AccessCode, COUNT(*) |
| 166 |
FROM plotMetadata
|
|
| 166 |
FROM "plotMetadata"
|
|
| 167 | 167 |
GROUP BY AccessCode; |
| 168 | 168 |
|
| 169 | 169 |
SELECT pr.project_id, project_name, project_pi, allow_download_all, PlotID, SiteCode, Country |
| 170 |
FROM projects pr JOIN plotMetadata p
|
|
| 170 |
FROM projects pr JOIN "plotMetadata" p
|
|
| 171 | 171 |
ON pr.project_id=p.project_id |
| 172 | 172 |
WHERE p.AccessCode=1; |
| 173 | 173 |
*/ |
Also available in: Unified diff
inputs/SALVIAS/verify/plots.ref.sql: Retrofitted to work with PostgreSQL staging tables