Revision 1246
Added by Aaron Marcuse-Kubitza almost 13 years ago
| inputs/NYBG/verify/specimens.ref.sql | ||
|---|---|---|
| 4 | 4 |
SELECT '# families' AS ___; |
| 5 | 5 |
SELECT count(DISTINCT Family) AS count FROM nybg_raw; |
| 6 | 6 |
|
| 7 |
SELECT '# genera' AS ___; |
|
| 8 |
SELECT count(DISTINCT Genus) AS count FROM nybg_raw; |
|
| 9 |
|
|
| 10 |
SELECT '# species' AS ___; |
|
| 11 |
SELECT count(DISTINCT Genus, Species) AS count FROM nybg_raw; |
|
| 12 |
|
|
| 7 | 13 |
/* |
| 8 | 14 |
SELECT |
| 9 | 15 |
( |
| inputs/NYBG/verify/specimens.ref | ||
|---|---|---|
| 6 | 6 |
# families |
| 7 | 7 |
count |
| 8 | 8 |
741 |
| 9 |
___ |
|
| 10 |
# genera |
|
| 11 |
count |
|
| 12 |
6381 |
|
| 13 |
___ |
|
| 14 |
# species |
|
| 15 |
count |
|
| 16 |
40145 |
|
| mappings/verify.specimens.sql | ||
|---|---|---|
| 10 | 10 |
JOIN party ON party_id = datasource_id AND organizationname = :datasource |
| 11 | 11 |
JOIN taxondetermination USING (taxonoccurrence_id) |
| 12 | 12 |
JOIN plantconcept USING (plantconcept_id) |
| 13 |
JOIN plantname USING (plantname_id) |
|
| 14 |
WHERE rank = 'Family' |
|
| 13 |
JOIN plantname |
|
| 14 |
ON plantname.plantname_id = plantconcept.plantname_id |
|
| 15 |
AND plantname.rank = 'Family' |
|
| 16 |
WHERE taxondetermination.isoriginal |
|
| 15 | 17 |
; |
| 18 |
|
|
| 19 |
SELECT '# genera' AS ___; |
|
| 20 |
SELECT count(DISTINCT plantname) AS count |
|
| 21 |
FROM specimenreplicate |
|
| 22 |
JOIN party ON party_id = datasource_id AND organizationname = :datasource |
|
| 23 |
JOIN taxondetermination USING (taxonoccurrence_id) |
|
| 24 |
JOIN plantconcept USING (plantconcept_id) |
|
| 25 |
JOIN plantname |
|
| 26 |
ON plantname.plantname_id = plantconcept.plantname_id |
|
| 27 |
AND plantname.rank = 'Genus' |
|
| 28 |
WHERE taxondetermination.isoriginal |
|
| 29 |
; |
|
| 30 |
|
|
| 31 |
SELECT '# species' AS ___; |
|
| 32 |
SELECT count(DISTINCT genus.plantname || ',' || species.plantname) AS count |
|
| 33 |
FROM specimenreplicate |
|
| 34 |
JOIN party ON party_id = datasource_id AND organizationname = :datasource |
|
| 35 |
JOIN taxondetermination USING (taxonoccurrence_id) |
|
| 36 |
JOIN plantconcept USING (plantconcept_id) |
|
| 37 |
JOIN plantname AS genus |
|
| 38 |
ON genus.plantname_id = plantconcept.plantname_id |
|
| 39 |
AND genus.rank = 'Genus' |
|
| 40 |
JOIN plantname AS species |
|
| 41 |
ON species.plantname_id = plantconcept.plantname_id |
|
| 42 |
AND species.rank = 'Species' |
|
| 43 |
WHERE taxondetermination.isoriginal |
|
| 44 |
; |
|
Also available in: Unified diff
Specimens verification: Added # genera and # species