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