Project

General

Profile

1
SELECT '# specimen replicates' AS ___;
2
SELECT count(*) AS count
3
FROM specimenreplicate
4
WHERE datasource_id =
5
    (SELECT party_id FROM party WHERE organizationname = :datasource)
6
;
7

    
8
SELECT '# families' AS ___;
9
SELECT count(DISTINCT plantname) AS count
10
FROM specimenreplicate
11
JOIN taxondetermination USING (taxonoccurrence_id)
12
JOIN plantconcept USING (plantconcept_id)
13
JOIN plantname
14
    ON plantname.plantname_id = plantconcept.plantname_id
15
    AND plantname.rank = 'Family'
16
WHERE datasource_id =
17
    (SELECT party_id FROM party WHERE organizationname = :datasource)
18
AND taxondetermination.isoriginal
19
;
20

    
21
SELECT '# genera' AS ___;
22
SELECT count(DISTINCT plantname) AS count
23
FROM specimenreplicate
24
JOIN taxondetermination USING (taxonoccurrence_id)
25
JOIN plantconcept USING (plantconcept_id)
26
JOIN plantname
27
    ON plantname.plantname_id = plantconcept.plantname_id
28
    AND plantname.rank = 'Genus'
29
WHERE datasource_id =
30
    (SELECT party_id FROM party WHERE organizationname = :datasource)
31
AND taxondetermination.isoriginal
32
;
33

    
34
SELECT '# species' AS ___;
35
SELECT
36
    count(DISTINCT genus_plantname.plantname||','||species_plantname.plantname)
37
        AS count
38
FROM specimenreplicate
39
JOIN taxondetermination AS genus_taxondet
40
    ON genus_taxondet.taxonoccurrence_id = specimenreplicate.taxonoccurrence_id
41
    AND genus_taxondet.isoriginal
42
JOIN plantconcept AS genus_plantconcept
43
    ON genus_plantconcept.plantconcept_id = genus_taxondet.plantconcept_id
44
JOIN plantname AS genus_plantname
45
    ON genus_plantname.plantname_id = genus_plantconcept.plantname_id
46
    AND genus_plantname.rank = 'Genus'
47
JOIN taxondetermination AS species_taxondet
48
    ON species_taxondet.taxonoccurrence_id =
49
        specimenreplicate.taxonoccurrence_id
50
    AND species_taxondet.isoriginal
51
JOIN plantconcept AS species_plantconcept
52
    ON species_plantconcept.plantconcept_id = species_taxondet.plantconcept_id
53
JOIN plantname AS species_plantname
54
    ON species_plantname.plantname_id = species_plantconcept.plantname_id
55
    AND species_plantname.rank = 'Species'
56
WHERE datasource_id =
57
    (SELECT party_id FROM party WHERE organizationname = :datasource)
58
;
59

    
60
SELECT '# binomials' AS ___;
61
SELECT count(DISTINCT plantname) AS count
62
FROM specimenreplicate
63
JOIN taxondetermination USING (taxonoccurrence_id)
64
JOIN plantconcept USING (plantconcept_id)
65
JOIN plantname
66
    ON plantname.plantname_id = plantconcept.plantname_id
67
    AND plantname.rank = 'Binomial'
68
WHERE datasource_id =
69
    (SELECT party_id FROM party WHERE organizationname = :datasource)
70
AND taxondetermination.isoriginal
71
;
72

    
73
SELECT 'collection codes' AS ___;
74
SELECT DISTINCT collectioncode_dwc
75
FROM specimenreplicate
76
WHERE datasource_id =
77
    (SELECT party_id FROM party WHERE organizationname = :datasource)
78
;
79

    
80
SELECT '# catalog numbers' AS ___;
81
SELECT count(DISTINCT catalognumber_dwc) AS count
82
FROM specimenreplicate
83
WHERE datasource_id =
84
    (SELECT party_id FROM party WHERE organizationname = :datasource)
85
;
86

    
87
SELECT 'duplicate catalog numbers' AS ___;
88
SELECT catalognumber_dwc, count(*) AS count
89
FROM specimenreplicate
90
WHERE datasource_id =
91
    (SELECT party_id FROM party WHERE organizationname = :datasource)
92
GROUP BY catalognumber_dwc
93
HAVING count(*) > 1
94
ORDER BY catalognumber_dwc NULLS FIRST/* to match MySQL */
95
;
96

    
97
SELECT '# counties' AS ___;
98
SELECT count(DISTINCT
99
    COALESCE(country_namedplace.placename, '')
100
    ||','|| COALESCE(state_namedplace.placename, '')
101
    ||','|| COALESCE(county_namedplace.placename, '')
102
    ) AS count
103
FROM specimenreplicate
104
JOIN taxonoccurrence USING (taxonoccurrence_id)
105
JOIN locationevent USING (locationevent_id)
106
JOIN locationdetermination AS country_locationdet
107
    ON country_locationdet.location_id = locationevent.location_id
108
JOIN namedplace AS country_namedplace
109
    ON country_namedplace.namedplace_id = country_locationdet.namedplace_id
110
    AND country_namedplace.placesystem = 'area|country|territory'
111
JOIN locationdetermination AS state_locationdet
112
    ON state_locationdet.location_id = locationevent.location_id
113
JOIN namedplace AS state_namedplace
114
    ON state_namedplace.namedplace_id = state_locationdet.namedplace_id
115
    AND state_namedplace.placesystem = 'region|state|province'
116
JOIN locationdetermination AS county_locationdet
117
    ON county_locationdet.location_id = locationevent.location_id
118
JOIN namedplace AS county_namedplace
119
    ON county_namedplace.namedplace_id = county_locationdet.namedplace_id
120
    AND county_namedplace.placesystem = 'county'
121
WHERE specimenreplicate.datasource_id =
122
    (SELECT party_id FROM party WHERE organizationname = :datasource)
123
;
(16-16/16)