Revision 1249
Added by Aaron Marcuse-Kubitza over 12 years ago
| mappings/verify.plots.sql | ||
|---|---|---|
| 1 | 1 |
SELECT '# projects' AS ___; |
| 2 | 2 |
SELECT count(*) AS count |
| 3 | 3 |
FROM project |
| 4 |
JOIN party ON party_id = datasource_id AND organizationname = :datasource |
|
| 4 |
WHERE datasource_id = |
|
| 5 |
(SELECT party_id FROM party WHERE organizationname = :datasource) |
|
| 5 | 6 |
; |
| 6 | 7 |
|
| 7 | 8 |
SELECT 'projects' AS ___; |
| 8 | 9 |
SELECT projectname AS project |
| 9 | 10 |
FROM project |
| 10 |
JOIN party ON party_id = datasource_id AND organizationname = :datasource |
|
| 11 |
WHERE datasource_id = |
|
| 12 |
(SELECT party_id FROM party WHERE organizationname = :datasource) |
|
| 11 | 13 |
ORDER BY project |
| 12 | 14 |
; |
| 13 | 15 |
|
| 14 | 16 |
SELECT E'each project\'s # locations' AS ___; |
| 15 | 17 |
SELECT projectname AS project, count(*) AS locations_count |
| 16 | 18 |
FROM project |
| 17 |
JOIN party ON party_id = datasource_id AND organizationname = :datasource |
|
| 18 | 19 |
JOIN locationevent USING (project_id) |
| 20 |
WHERE project.datasource_id = |
|
| 21 |
(SELECT party_id FROM party WHERE organizationname = :datasource) |
|
| 19 | 22 |
GROUP BY project |
| 20 | 23 |
ORDER BY project |
| 21 | 24 |
; |
| ... | ... | |
| 23 | 26 |
SELECT '# locations' AS ___; |
| 24 | 27 |
SELECT count(DISTINCT location_id) AS count |
| 25 | 28 |
FROM locationevent |
| 26 |
JOIN party ON party_id = datasource_id AND organizationname = :datasource |
|
| 27 |
WHERE parent_id IS NULL -- no subplots |
|
| 29 |
WHERE datasource_id = |
|
| 30 |
(SELECT party_id FROM party WHERE organizationname = :datasource) |
|
| 31 |
AND parent_id IS NULL -- no subplots |
|
| 28 | 32 |
; |
| 29 | 33 |
|
| 30 | 34 |
SELECT '# location events' AS ___; |
| 31 | 35 |
SELECT count(*) AS count |
| 32 | 36 |
FROM locationevent |
| 33 |
JOIN party ON party_id = datasource_id AND organizationname = :datasource |
|
| 37 |
WHERE datasource_id = |
|
| 38 |
(SELECT party_id FROM party WHERE organizationname = :datasource) |
|
| 34 | 39 |
AND parent_id IS NULL -- no subplots |
| 35 | 40 |
; |
| 36 | 41 |
|
| ... | ... | |
| 39 | 44 |
projectname AS project |
| 40 | 45 |
, method.name AS method |
| 41 | 46 |
FROM project |
| 42 |
JOIN party ON party_id = datasource_id AND organizationname = :datasource |
|
| 43 | 47 |
JOIN locationevent USING (project_id) |
| 44 | 48 |
JOIN method USING (method_id) |
| 49 |
WHERE project.datasource_id = |
|
| 50 |
(SELECT party_id FROM party WHERE organizationname = :datasource) |
|
| 45 | 51 |
ORDER BY project, method |
| 46 | 52 |
LIMIT 10; |
| 47 | 53 |
|
| ... | ... | |
| 50 | 56 |
projectname AS project |
| 51 | 57 |
, method.observationmeasure AS observationmeasure |
| 52 | 58 |
FROM project |
| 53 |
JOIN party ON party_id = datasource_id AND organizationname = :datasource |
|
| 54 | 59 |
JOIN locationevent USING (project_id) |
| 55 | 60 |
JOIN method USING (method_id) |
| 61 |
WHERE project.datasource_id = |
|
| 62 |
(SELECT party_id FROM party WHERE organizationname = :datasource) |
|
| 56 | 63 |
ORDER BY project, observationmeasure |
| 57 | 64 |
LIMIT 10; |
| mappings/verify.specimens.sql | ||
|---|---|---|
| 1 | 1 |
SELECT '# specimen replicates' AS ___; |
| 2 | 2 |
SELECT count(*) AS count |
| 3 | 3 |
FROM specimenreplicate |
| 4 |
JOIN party ON party_id = datasource_id AND organizationname = :datasource |
|
| 4 |
WHERE datasource_id = |
|
| 5 |
(SELECT party_id FROM party WHERE organizationname = :datasource) |
|
| 5 | 6 |
; |
| 6 | 7 |
|
| 7 | 8 |
SELECT '# families' AS ___; |
| 8 | 9 |
SELECT count(DISTINCT plantname) AS count |
| 9 | 10 |
FROM specimenreplicate |
| 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 | 13 |
JOIN plantname |
| 14 | 14 |
ON plantname.plantname_id = plantconcept.plantname_id |
| 15 | 15 |
AND plantname.rank = 'Family' |
| 16 |
WHERE taxondetermination.isoriginal |
|
| 16 |
WHERE datasource_id = |
|
| 17 |
(SELECT party_id FROM party WHERE organizationname = :datasource) |
|
| 18 |
AND taxondetermination.isoriginal |
|
| 17 | 19 |
; |
| 18 | 20 |
|
| 19 | 21 |
SELECT '# genera' AS ___; |
| ... | ... | |
| 25 | 27 |
JOIN plantname |
| 26 | 28 |
ON plantname.plantname_id = plantconcept.plantname_id |
| 27 | 29 |
AND plantname.rank = 'Genus' |
| 28 |
WHERE taxondetermination.isoriginal |
|
| 30 |
WHERE datasource_id = |
|
| 31 |
(SELECT party_id FROM party WHERE organizationname = :datasource) |
|
| 32 |
AND taxondetermination.isoriginal |
|
| 29 | 33 |
; |
| 30 | 34 |
|
| 31 | 35 |
SELECT '# species' AS ___; |
| 32 |
SELECT count(DISTINCT genus.plantname || ',' || species.plantname) AS count
|
|
| 36 |
SELECT count(DISTINCT genus.plantname||','||species.plantname) AS count
|
|
| 33 | 37 |
FROM specimenreplicate |
| 34 |
JOIN party ON party_id = datasource_id AND organizationname = :datasource |
|
| 35 | 38 |
JOIN taxondetermination USING (taxonoccurrence_id) |
| 36 | 39 |
JOIN plantconcept USING (plantconcept_id) |
| 37 | 40 |
JOIN plantname AS genus |
| ... | ... | |
| 40 | 43 |
JOIN plantname AS species |
| 41 | 44 |
ON species.plantname_id = plantconcept.plantname_id |
| 42 | 45 |
AND species.rank = 'Species' |
| 43 |
WHERE taxondetermination.isoriginal |
|
| 46 |
WHERE datasource_id = |
|
| 47 |
(SELECT party_id FROM party WHERE organizationname = :datasource) |
|
| 48 |
AND taxondetermination.isoriginal |
|
| 44 | 49 |
; |
Also available in: Unified diff
mappings/verify.*: Use nested SELECT instead of JOIN on party to get datasource_id, so that party will not be joined on after other joins have already occurred (which slows things down)