Revision 1593
Added by Aaron Marcuse-Kubitza almost 13 years ago
| mappings/verify.specimens.sql | ||
|---|---|---|
| 6 | 6 |
; |
| 7 | 7 |
|
| 8 | 8 |
SELECT '# families' AS ___; |
| 9 |
SELECT count(DISTINCT plantname) AS count |
|
| 9 |
SELECT count(DISTINCT plantname.plantname_id) AS count
|
|
| 10 | 10 |
FROM specimenreplicate |
| 11 | 11 |
JOIN taxondetermination USING (taxonoccurrence_id) |
| 12 | 12 |
JOIN plantconcept USING (plantconcept_id) |
| 13 |
JOIN plantname_ancestor USING (plantname_id) |
|
| 13 | 14 |
JOIN plantname |
| 14 |
ON plantname.plantname_id = plantconcept.plantname_id
|
|
| 15 |
AND plantname.rank = 'Family'
|
|
| 15 |
ON plantname.plantname_id = plantname_ancestor.ancestor_id
|
|
| 16 |
AND plantname.rank = 'family'
|
|
| 16 | 17 |
WHERE datasource_id = |
| 17 | 18 |
(SELECT party_id FROM party WHERE organizationname = :datasource) |
| 18 | 19 |
AND taxondetermination.isoriginal |
| 19 | 20 |
; |
| 20 | 21 |
|
| 21 | 22 |
SELECT '# genera' AS ___; |
| 22 |
SELECT count(DISTINCT plantname) AS count |
|
| 23 |
SELECT count(DISTINCT plantname.plantname_id) AS count
|
|
| 23 | 24 |
FROM specimenreplicate |
| 24 | 25 |
JOIN taxondetermination USING (taxonoccurrence_id) |
| 25 | 26 |
JOIN plantconcept USING (plantconcept_id) |
| 27 |
JOIN plantname_ancestor USING (plantname_id) |
|
| 26 | 28 |
JOIN plantname |
| 27 |
ON plantname.plantname_id = plantconcept.plantname_id
|
|
| 28 |
AND plantname.rank = 'Genus'
|
|
| 29 |
ON plantname.plantname_id = plantname_ancestor.ancestor_id
|
|
| 30 |
AND plantname.rank = 'genus'
|
|
| 29 | 31 |
WHERE datasource_id = |
| 30 | 32 |
(SELECT party_id FROM party WHERE organizationname = :datasource) |
| 31 | 33 |
AND taxondetermination.isoriginal |
| ... | ... | |
| 33 | 35 |
|
| 34 | 36 |
SELECT '# species' AS ___; |
| 35 | 37 |
SELECT |
| 36 |
count(DISTINCT genus_plantname.plantname||','||species_plantname.plantname)
|
|
| 37 |
AS count |
|
| 38 |
count(DISTINCT genus_plantname.plantname_id
|
|
| 39 |
||','||species_plantname.plantname_id) AS count
|
|
| 38 | 40 |
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 |
|
| 41 |
JOIN taxondetermination USING (taxonoccurrence_id) |
|
| 42 |
JOIN plantconcept USING (plantconcept_id) |
|
| 43 |
JOIN plantname_ancestor USING (plantname_id) |
|
| 44 | 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 |
|
| 45 |
ON genus_plantname.plantname_id = plantname_ancestor.ancestor_id |
|
| 46 |
AND genus_plantname.rank = 'genus' |
|
| 53 | 47 |
JOIN plantname AS species_plantname |
| 54 |
ON species_plantname.plantname_id = species_plantconcept.plantname_id
|
|
| 55 |
AND species_plantname.rank = 'Species'
|
|
| 48 |
ON species_plantname.plantname_id = plantname_ancestor.ancestor_id
|
|
| 49 |
AND species_plantname.rank = 'species'
|
|
| 56 | 50 |
WHERE datasource_id = |
| 57 | 51 |
(SELECT party_id FROM party WHERE organizationname = :datasource) |
| 58 | 52 |
; |
| 59 | 53 |
|
| 60 | 54 |
SELECT '# binomials' AS ___; |
| 61 |
SELECT count(DISTINCT plantname) AS count |
|
| 55 |
SELECT count(DISTINCT plantname.plantname_id) AS count
|
|
| 62 | 56 |
FROM specimenreplicate |
| 63 | 57 |
JOIN taxondetermination USING (taxonoccurrence_id) |
| 64 | 58 |
JOIN plantconcept USING (plantconcept_id) |
| 59 |
JOIN plantname_ancestor USING (plantname_id) |
|
| 65 | 60 |
JOIN plantname |
| 66 |
ON plantname.plantname_id = plantconcept.plantname_id
|
|
| 67 |
AND plantname.rank = 'Binomial'
|
|
| 61 |
ON plantname.plantname_id = plantname_ancestor.ancestor_id
|
|
| 62 |
AND plantname.rank = 'binomial'
|
|
| 68 | 63 |
WHERE datasource_id = |
| 69 | 64 |
(SELECT party_id FROM party WHERE organizationname = :datasource) |
| 70 | 65 |
AND taxondetermination.isoriginal |
| ... | ... | |
| 95 | 90 |
; |
| 96 | 91 |
|
| 97 | 92 |
SELECT '# counties' AS ___; |
| 98 |
SELECT count(DISTINCT namedplace_id) AS count |
|
| 93 |
SELECT count(DISTINCT namedplace.namedplace_id) AS count
|
|
| 99 | 94 |
FROM specimenreplicate |
| 100 | 95 |
JOIN taxonoccurrence USING (taxonoccurrence_id) |
| 101 | 96 |
JOIN locationevent USING (locationevent_id) |
| 102 | 97 |
JOIN locationdetermination USING (location_id) |
| 103 |
JOIN namedplace USING (namedplace_id) |
|
| 98 |
JOIN namedplace_ancestor USING (namedplace_id) |
|
| 99 |
JOIN namedplace |
|
| 100 |
ON namedplace.namedplace_id = namedplace_ancestor.ancestor_id |
|
| 101 |
AND namedplace.rank = 'county' |
|
| 104 | 102 |
WHERE specimenreplicate.datasource_id = |
| 105 | 103 |
(SELECT party_id FROM party WHERE organizationname = :datasource) |
| 106 |
AND namedplace.rank = 'county' |
|
| 107 | 104 |
; |
Also available in: Unified diff
mappings/verify.specimens.sql: Updated for schema changes