Project

General

Profile

« Previous | Next » 

Revision 5974

mappings/verify.specimens.sql: Updated for current schema

View differences:

mappings/verify.specimens.sql
1 1
SELECT '# specimen replicates' AS ___;
2 2
SELECT count(*) AS count
3 3
FROM specimenreplicate
4
WHERE datasource_id =
5
    (SELECT party_id FROM party WHERE organizationname = :datasource)
4
WHERE reference_id =
5
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
6 6
;
7 7

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

  
22 16
SELECT '# genera' AS ___;
23
SELECT count(DISTINCT plantname.plantname_id) AS count
24
FROM specimenreplicate
25
JOIN taxondetermination USING (taxonoccurrence_id)
26
JOIN plantconcept USING (plantconcept_id)
27
JOIN plantname_ancestor USING (plantname_id)
28
JOIN plantname
29
    ON plantname.plantname_id = plantname_ancestor.ancestor_id
30
    AND plantname.rank = 'genus'
31
WHERE datasource_id =
32
    (SELECT party_id FROM party WHERE organizationname = :datasource)
33
AND taxondetermination.isoriginal
17
SELECT count(DISTINCT genus) AS count
18
FROM taxonlabel
19
JOIN taxonverbatim USING (taxonlabel_id)
20
WHERE reference_id =
21
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
34 22
;
35 23

  
36 24
SELECT '# species' AS ___;
37
SELECT count(DISTINCT plantname.plantname_id) AS count
38
FROM specimenreplicate
39
JOIN taxondetermination USING (taxonoccurrence_id)
40
JOIN plantconcept USING (plantconcept_id)
41
JOIN plantname_ancestor USING (plantname_id)
42
JOIN plantname
43
    ON plantname.plantname_id = plantname_ancestor.ancestor_id
44
    AND plantname.rank = 'species'
45
WHERE datasource_id =
46
    (SELECT party_id FROM party WHERE organizationname = :datasource)
47
AND taxondetermination.isoriginal
25
SELECT count(DISTINCT specific_epithet) AS count
26
FROM taxonlabel
27
JOIN taxonverbatim USING (taxonlabel_id)
28
WHERE reference_id =
29
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
48 30
;
49 31

  
50 32
SELECT '# binomials' AS ___;
51
SELECT count(DISTINCT plantname.plantname_id) AS count
52
FROM specimenreplicate
53
JOIN taxondetermination USING (taxonoccurrence_id)
54
JOIN plantconcept USING (plantconcept_id)
55
JOIN plantname_ancestor USING (plantname_id)
56
JOIN plantname
57
    ON plantname.plantname_id = plantname_ancestor.ancestor_id
58
    AND plantname.rank = 'binomial'
59
WHERE datasource_id =
60
    (SELECT party_id FROM party WHERE organizationname = :datasource)
61
AND taxondetermination.isoriginal
33
SELECT count(DISTINCT taxonverbatim_id) AS count
34
FROM taxonlabel
35
JOIN taxonverbatim USING (taxonlabel_id)
36
WHERE reference_id =
37
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
62 38
;
63 39

  
64 40
SELECT 'collection codes' AS ___;
65 41
SELECT DISTINCT collectioncode_dwc
66 42
FROM specimenreplicate
67
WHERE datasource_id =
68
    (SELECT party_id FROM party WHERE organizationname = :datasource)
43
WHERE reference_id =
44
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
69 45
;
70 46

  
71 47
SELECT '# catalog numbers' AS ___;
72 48
SELECT count(DISTINCT catalognumber_dwc) AS count
73 49
FROM specimenreplicate
74
WHERE datasource_id =
75
    (SELECT party_id FROM party WHERE organizationname = :datasource)
50
WHERE reference_id =
51
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
76 52
;
77 53

  
78 54
SELECT 'duplicate catalog numbers' AS ___;
79 55
SELECT catalognumber_dwc, count(*) AS count
80 56
FROM specimenreplicate
81
WHERE datasource_id =
82
    (SELECT party_id FROM party WHERE organizationname = :datasource)
57
WHERE reference_id =
58
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
83 59
GROUP BY catalognumber_dwc
84 60
HAVING count(*) > 1
85
ORDER BY catalognumber_dwc NULLS FIRST/* to match MySQL */
61
ORDER BY catalognumber_dwc
86 62
;
87 63

  
88 64
SELECT '# counties' AS ___;
89
SELECT count(DISTINCT namedplace.namedplace_id) AS count
90
FROM specimenreplicate
91
JOIN taxonoccurrence USING (taxonoccurrence_id)
92
JOIN locationevent USING (locationevent_id)
93
JOIN locationdetermination USING (location_id)
94
JOIN namedplace_ancestor USING (namedplace_id)
95
JOIN namedplace
96
    ON namedplace.namedplace_id = namedplace_ancestor.ancestor_id
97
    AND namedplace.rank = 'county'
98
WHERE specimenreplicate.datasource_id =
99
    (SELECT party_id FROM party WHERE organizationname = :datasource)
65
SELECT count(DISTINCT county) AS count
66
FROM place
67
WHERE reference_id =
68
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
100 69
;

Also available in: Unified diff