Project

General

Profile

1
SELECT '# projects' AS ___;
2
SELECT count(*) AS count FROM projects;
3

    
4
SELECT 'projects' AS ___;
5
SELECT CAST(project_name AS text) AS projectname
6
FROM projects
7
ORDER BY projectname
8
;
9

    
10
SELECT 'each project''s # locations' AS ___;
11
SELECT
12
    CAST(project_name AS text) AS projectname
13
    , count("PlotID") AS locations_count
14
FROM projects
15
JOIN "plotMetadata" ON "plotMetadata".project_id = projects.project_id
16
GROUP BY projectname
17
ORDER BY projectname
18
;
19

    
20
SELECT '# top-level locations' AS ___;
21
SELECT count(*) AS count FROM "plotMetadata";
22

    
23
SELECT '# top-level location events' AS ___;
24
SELECT count(*) AS count FROM "plotMetadata";
25

    
26
SELECT '# locations' AS ___;
27
SELECT count(DISTINCT ROW ("PlotID", "Line")) AS count
28
FROM "plotMetadata"
29
LEFT JOIN "plotObservations" USING ("PlotID")
30
;
31

    
32
SELECT '# location events' AS ___;
33
SELECT count(DISTINCT ROW ("PlotID", "Line", census_date)) AS count
34
FROM "plotMetadata"
35
LEFT JOIN "plotObservations" USING ("PlotID")
36
;
37

    
38
SELECT '1st methods' AS ___;
39
SELECT DISTINCT
40
    CAST(project_name AS text) AS projectname
41
    , "PlotMethod" AS method
42
FROM projects
43
JOIN "plotMetadata" ON "plotMetadata".project_id = projects.project_id
44
ORDER BY projectname, method
45
LIMIT 10
46
;
47

    
48
SELECT '1st observation measures' AS ___;
49
SELECT DISTINCT
50
    CAST(project_name AS text) AS projectname
51
    , "lookup_MethodCode"."Description" AS observationmeasure
52
FROM projects
53
JOIN "plotMetadata" ON "plotMetadata".project_id = projects.project_id
54
JOIN "lookup_MethodCode" ON "lookup_MethodCode"."MethodCode" = "plotMetadata"."MethodCode"
55
ORDER BY projectname, observationmeasure
56
LIMIT 10
57
;
58

    
59
/*
60
SELECT p.PlotID, p.SiteCode, COUNT(*)
61
FROM projects pr JOIN "plotMetadata" p JOIN plotObservations o
62
ON pr.project_id=p.project_id AND p.PlotID=o.PlotID
63
WHERE p.project_id=2
64
GROUP BY p.PlotID, p.SiteCode;
65

    
66
SELECT NoInd, COUNT(*) AS observations
67
FROM plotObservations o JOIN "plotMetadata" p 
68
ON o.PlotID=p.PlotID
69
WHERE project_id=2
70
GROUP BY NoInd;
71

    
72
SELECT p.PlotID, p.SiteCode, SUM(NoInd) AS Individuals
73
FROM projects pr JOIN "plotMetadata" p JOIN plotObservations o
74
ON pr.project_id=p.project_id AND p.PlotID=o.PlotID
75
WHERE p.project_id=2
76
GROUP BY p.PlotID, p.SiteCode;
77

    
78
SELECT DISTINCT "pr".project_id, project_name, "PlotMethod", m."MethodCode", m."Description"
79
FROM projects pr JOIN "plotMetadata" p JOIN "lookup_MethodCode" m
80
ON pr.project_id=p.project_id AND p."MethodCode"=m."MethodCode"
81
WHERE m."MethodCode"=8;
82

    
83
SELECT NoInd, COUNT(*) AS observations
84
FROM plotObservations o JOIN "plotMetadata" p JOIN "lookup_MethodCode" m
85
ON o.PlotID=p.PlotID AND p."MethodCode"=m."MethodCode"
86
WHERE project_id=2
87
GROUP BY NoInd;
88

    
89
SELECT p.PlotID, p.SiteCode, SUM(NoInd) AS Individuals
90
FROM projects pr JOIN "plotMetadata" p JOIN plotObservations o
91
ON pr.project_id=p.project_id AND p.PlotID=o.PlotID
92
WHERE p.project_id=1
93
GROUP BY p.PlotID, p.SiteCode
94
LIMIT 10;
95

    
96
SELECT "PlotMethod", "MethodCode", "Description", COUNT(project_id) as projects
97
FROM (
98
SELECT DISTINCT "p"."PlotMethod", p."MethodCode", p."Description", p.project_id
99
FROM (
100
SELECT project_id, PlotID, "PlotMethod", m."MethodCode", m."Description"
101
FROM "plotMetadata" p JOIN "lookup_MethodCode" m
102
ON p."MethodCode"=m."MethodCode"
103
) p JOIN plotObservations o JOIN stems s
104
ON p.PlotID=o.PlotID AND o.PlotObsID=s.PlotObsID
105
GROUP BY project_id
106
) AS a
107
GROUP BY "PlotMethod", "MethodCode", "Description";
108

    
109
SELECT DISTINCT "pr".project_id, project_name
110
FROM projects pr JOIN "plotMetadata" p JOIN plotObservations o JOIN stems s
111
ON pr.project_id=p.project_id AND p.PlotID=o.PlotID AND o.PlotObsID=s.PlotObsID
112
WHERE p."MethodCode"=3;
113

    
114
SELECT p.PlotID, SiteName as plot, COUNT(*) AS stems
115
FROM "plotMetadata" p JOIN plotObservations o JOIN stems s
116
ON p.PlotID=o.PlotID AND o.PlotObsID=s.PlotObsID
117
WHERE p.project_id=2
118
GROUP BY p.PlotID, SiteName
119
LIMIT 10;
120

    
121
SELECT p.PlotID, SiteName AS plot, COUNT(DISTINCT "Family", Genus, Species) AS SpeciesCount
122
FROM "plotMetadata" p JOIN plotObservations o
123
ON p.PlotID=o.PlotID
124
WHERE project_id=2
125
GROUP BY p.PlotID, SiteName;
126

    
127
SELECT DISTINCT "p".PlotID, SiteName AS plot, Family, CONCAT_WS(' ',IFNULL(Genus,''), IFNULL(Species,'')) AS SpeciesName
128
FROM "plotMetadata" p JOIN plotObservations o
129
ON p.PlotID=o.PlotID
130
WHERE p.PlotID=298;
131

    
132
SELECT name_status, count(*) FROM plotObservations GROUP BY name_status;
133

    
134
SELECT DISTINCT "p".PlotID, SiteName AS plot, Family, 
135
CONCAT_WS(' ',IFNULL(Genus,''), IFNULL(Species,'')) AS SpeciesName, name_status
136
FROM "plotMetadata" p JOIN plotObservations o
137
ON p.PlotID=o.PlotID
138
WHERE p.PlotID=298;
139

    
140
SELECT DISTINCT "p".PlotID, SiteName AS plot, Family, 
141
CONCAT_WS(' ',IFNULL(Genus,''), IFNULL(Species,'')) AS SpeciesName, name_status
142
FROM "plotMetadata" p JOIN plotObservations o
143
ON p.PlotID=o.PlotID
144
WHERE p.PlotID=298 AND name_status=1;
145

    
146
SELECT p.PlotID, SiteCode AS plot, o.Line as subplot, COUNT(*) AS individuals
147
FROM "plotMetadata" p JOIN plotObservations o
148
ON p.PlotID=o.PlotID
149
WHERE p.PlotID=298 
150
GROUP BY p.PlotID, plot, subplot;
151

    
152
SELECT p.PlotID, SiteCode AS plot, o.Line as subplot, SUM(NoInd) AS individuals
153
FROM "plotMetadata" p JOIN plotObservations o
154
ON p.PlotID=o.PlotID
155
WHERE p.PlotID=298
156
GROUP BY p.PlotID, plot, subplot;
157

    
158
SELECT p.PlotID, SiteCode AS plot, o.Line as subplot, SUM(NoInd) AS individuals
159
FROM "plotMetadata" p JOIN plotObservations o
160
ON p.PlotID=o.PlotID
161
WHERE p.PlotID=1
162
GROUP BY p.PlotID, plot, subplot;
163

    
164
SELECT COUNT(*) FROM "plotMetadata" WHERE "MethodCode"=1;
165

    
166
SELECT PlotID FROM "plotMetadata" WHERE "MethodCode"=1 LIMIT 1;
167

    
168
SELECT p.PlotID, SiteCode AS plot, Family, 
169
CONCAT_WS(' ',IFNULL(Genus,''), IFNULL(Species,'')) AS SpeciesName, cover_percent
170
FROM "plotMetadata" p JOIN plotObservations o
171
ON p.PlotID=o.PlotID
172
WHERE p.PlotID=24589;
173

    
174
SELECT p.PlotID, p.SiteCode as plot, Country, PolDiv1 AS stateProvince, pol2 AS countyParish, 
175
LatDec as latitude, LongDec as longitude, Elev as elevation
176
FROM projects pr JOIN "plotMetadata" p
177
ON pr.project_id=p.project_id
178
WHERE pr.project_id=1
179
LIMIT 20;
180

    
181
SELECT AccessCode, COUNT(*)
182
FROM "plotMetadata"
183
GROUP BY AccessCode;
184

    
185
SELECT pr.project_id, project_name, project_pi, allow_download_all, PlotID, SiteCode, Country
186
FROM projects pr JOIN "plotMetadata" p
187
ON pr.project_id=p.project_id
188
WHERE p.AccessCode=1;
189
*/
(2-2/2)