Revision 1325
Added by Aaron Marcuse-Kubitza over 12 years ago
| verify.specimens.sql | ||
|---|---|---|
| 95 | 95 |
; |
| 96 | 96 |
|
| 97 | 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 |
|
| 98 |
SELECT count(DISTINCT namedplace_id) AS count |
|
| 103 | 99 |
FROM specimenreplicate |
| 104 | 100 |
JOIN taxonoccurrence USING (taxonoccurrence_id) |
| 105 | 101 |
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' |
|
| 102 |
JOIN locationdetermination USING (location_id) |
|
| 103 |
JOIN namedplace USING (namedplace_id) |
|
| 121 | 104 |
WHERE specimenreplicate.datasource_id = |
| 122 | 105 |
(SELECT party_id FROM party WHERE organizationname = :datasource) |
| 106 |
AND namedplace.rank = 'county' |
|
| 123 | 107 |
; |
Also available in: Unified diff
mappings/verify.specimens.sql: Updated for schema changes