Project

General

Profile

1 1244 aaronmk
SELECT '# specimen replicates' AS ___;
2 4403 aaronmk
SELECT count(*) AS count FROM "Specimen";
3 1244 aaronmk
4
SELECT '# families' AS ___;
5 4403 aaronmk
SELECT count(DISTINCT "Family") AS count FROM "Specimen";
6 1244 aaronmk
7 1246 aaronmk
SELECT '# genera' AS ___;
8 4403 aaronmk
SELECT count(DISTINCT "Genus") AS count FROM "Specimen";
9 1246 aaronmk
10
SELECT '# species' AS ___;
11 4403 aaronmk
SELECT count(DISTINCT "Genus"||':'||"Species") AS count FROM "Specimen";
12 1246 aaronmk
13 1254 aaronmk
SELECT '# binomials' AS ___;
14 4403 aaronmk
SELECT count(DISTINCT "ScientificName") AS count FROM "Specimen";
15 1254 aaronmk
16 1297 aaronmk
SELECT 'collection codes' AS ___;
17 4403 aaronmk
SELECT DISTINCT "CollectionCode" AS collectioncode_dwc FROM "Specimen";
18 1244 aaronmk
19 1297 aaronmk
SELECT '# catalog numbers' AS ___;
20 4403 aaronmk
SELECT count(DISTINCT "CatalogNumber") AS count FROM "Specimen";
21 1244 aaronmk
22 1300 aaronmk
SELECT 'duplicate catalog numbers' AS ___;
23 4403 aaronmk
SELECT "CatalogNumber" AS catalognumber_dwc, count(*) AS count
24
FROM "Specimen"
25
GROUP BY "CatalogNumber"
26
HAVING count(*) > 1
27 1300 aaronmk
;
28
29 1298 aaronmk
SELECT '# counties' AS ___;
30 4403 aaronmk
SELECT count(DISTINCT "Country"||':'||"StateProvince"||':'||"County") AS count FROM "Specimen";
31 1298 aaronmk
32 1297 aaronmk
/*
33 4403 aaronmk
SELECT COUNT(*) as totalRecords, COUNT(DISTINCT "CatalogNumber") as uniqueCatalogNumbers
34
FROM "Specimen";
35 1244 aaronmk
36
SELECT CatalogNumber, COUNT(*) AS records
37 4403 aaronmk
FROM "Specimen"
38 1244 aaronmk
GROUP BY CatalogNumber
39
HAVING records>1;
40
41
SELECT b.CatalogNumber, DateLastModified, ScientificName, Collector, FieldNumber,
42
CollectedDate
43 4403 aaronmk
FROM "Specimen" AS a JOIN (
44 1244 aaronmk
SELECT CatalogNumber, COUNT(*) AS records
45 4403 aaronmk
FROM "Specimen"
46 1244 aaronmk
WHERE CatalogNumber IS NOT NULL
47
GROUP BY CatalogNumber
48
HAVING records>1
49
) AS b
50
ON a.CatalogNumber=b.CatalogNumber
51
ORDER BY b.CatalogNumber ASC, DateLastModified DESC;
52
53
SELECT DateLastModified, ScientificName, Collector, FieldNumber,
54
CollectedDate
55 4403 aaronmk
FROM "Specimen"
56 1244 aaronmk
WHERE CatalogNumber IS NULL
57
ORDER BY CatalogNumber ASC, DateLastModified DESC
58
LIMIT 12;
59
60
SELECT
61
(
62
SELECT COUNT(*)
63 4403 aaronmk
FROM "Specimen"
64 1244 aaronmk
WHERE Latitude IS NOT NULL
65
) AS allLats,
66
(
67
SELECT COUNT(*)
68 4403 aaronmk
FROM "Specimen"
69 1244 aaronmk
WHERE Latitude REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$'
70
) AS decimalLats,
71
(
72
SELECT COUNT(*)
73 4403 aaronmk
FROM "Specimen"
74 1244 aaronmk
WHERE Longitude IS NOT NULL
75
) AS allLongs,
76
(
77
SELECT COUNT(*)
78 4403 aaronmk
FROM "Specimen"
79 1244 aaronmk
WHERE Longitude REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$'
80
) AS decimalLongs;
81
82
SELECT Latitude, Longitude
83 4403 aaronmk
FROM "Specimen"
84 1244 aaronmk
WHERE  Latitude IS NOT NULL AND  Longitude IS NOT NULL
85
LIMIT 10;
86
87
SELECT
88
(
89
SELECT COUNT(*)
90 4403 aaronmk
FROM "Specimen"
91 1244 aaronmk
WHERE Latitude>90 OR Latitude<-90
92
) AS badLats,
93
(
94
SELECT COUNT(*)
95 4403 aaronmk
FROM "Specimen"
96 1244 aaronmk
WHERE Longitude>180 OR Longitude<-180
97
) AS badLongs;
98
99
SELECT DayCollected, MonthCollected, YearCollected
100 4403 aaronmk
FROM "Specimen"
101 1244 aaronmk
WHERE DayCollected IS NOT NULL OR MonthCollected IS NOT NULL OR YearCollected IS NOT NULL
102
LIMIT 10;
103
104
SELECT
105
(
106
SELECT COUNT(*)
107 4403 aaronmk
FROM "Specimen"
108 1244 aaronmk
WHERE NOT(DayCollected REGEXP '^-?[0-9]+$')
109
OR NOT(MonthCollected REGEXP '^-?[0-9]+$')
110
OR NOT(YearCollected REGEXP '^-?[0-9]+$')
111
) AS badDateCollected,
112
(
113
SELECT COUNT(*)
114 4403 aaronmk
FROM "Specimen"
115 1244 aaronmk
WHERE NOT(DayIdentified REGEXP '^-?[0-9]+$')
116
OR NOT(MonthIdentified REGEXP '^-?[0-9]+$')
117
OR NOT(YearIdentified REGEXP '^-?[0-9]+$')
118
) AS badDateIdentified;
119
120
SELECT Locality
121 4403 aaronmk
FROM "Specimen"
122 1244 aaronmk
WHERE Locality IS NOT NULL
123
LIMIT 10;
124
125
SELECT PlantFungusDescription
126 4403 aaronmk
FROM "Specimen"
127 1244 aaronmk
WHERE PlantFungusDescription IS NOT NULL
128
LIMIT 10;
129
130
SELECT Habitat, Vegetation
131 4403 aaronmk
FROM "Specimen"
132 1244 aaronmk
WHERE Habitat IS NOT NULL OR Vegetation IS NOT NULL
133
LIMIT 10;
134
*/