Project

General

Profile

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

    
4
SELECT 'projects' AS ___;
5
SELECT CAST(project_id AS text) AS project FROM projects ORDER BY project;
6

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

    
17
SELECT '# locations' AS ___;
18
SELECT count(DISTINCT COALESCE("LatDec", 'NaN')||':'||COALESCE("LongDec", 'NaN')) AS count FROM "plotMetadata"
19
;
20

    
21
SELECT '# location events' AS ___;
22
SELECT count(*) AS count FROM "plotMetadata";
23

    
24
SELECT '1st methods' AS ___;
25
SELECT DISTINCT
26
    CAST(projects.project_id AS text) AS project
27
    , "PlotMethod" AS method
28
FROM projects
29
JOIN "plotMetadata" ON "plotMetadata".project_id = projects.project_id
30
ORDER BY project, method
31
LIMIT 10;
32

    
33
SELECT '1st observation measures' AS ___;
34
SELECT DISTINCT
35
    CAST(projects.project_id AS text) AS project
36
    , "lookup_MethodCode"."Description" AS observationmeasure
37
FROM projects
38
JOIN "plotMetadata" ON "plotMetadata".project_id = projects.project_id
39
JOIN "lookup_MethodCode" ON "lookup_MethodCode"."MethodCode" = "plotMetadata"."MethodCode"
40
ORDER BY project, observationmeasure
41
LIMIT 10;
42

    
43
/*
44
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
*/
(2-2/2)