Project

General

Profile

Full-database summary statistics

Note that different statistics were calculated on different versions of the database, and some are out of date

Input rows

27,930,518

calculation: inputs/import.stats.xls 2013-1-16 import has 29,805,931 total - 1,707,970 geoscrub - 3536 herbaria - 163,907 NCBI

Table row counts

Table Row count
analytical_stem ~27,204,860
analytical_aggregate ~15,143,832
SET search_path TO "public.2012-12-11-11-46-55.r6795";
EXPLAIN SELECT * FROM analytical_stem;
EXPLAIN SELECT * FROM analytical_aggregate;

Joins

location to locationevent

11,217,698

SET search_path TO "public.2012-11-06-16-50-06.r6038";
SELECT count(*)
FROM location
LEFT JOIN locationevent USING (location_id)

Filters

places with no geoscrub place

datasource no_geoscrub_place total
ACAD 8200 8247
ARIZ 831 36165
CTFS 156 157
CVS 6206 6206
FIA 270690 270691
GBIF 188289 188613
Madidi 285 285
MO 4402 205541
NCU-NCSC 1668 2741
NY 1 57839
QMOR 280 444
REMIB 13734 16275
SALVIAS 6746 7065
SpeciesLink 136494 187724
TEAM 2337 2337
U 2563 3177
VegBank 59413 66284
UNCC 1831 1858
SET search_path TO "public.2012-11-13-19-16-20.r6152";
SELECT
reference.shortname AS datasource
, no_geoscrub_place
, count(*) AS total
FROM (
    SELECT datasource_place.reference_id, count(*) AS no_geoscrub_place
    FROM place datasource_place
    JOIN place matched_place ON matched_place.place_id = datasource_place.matched_place_id
    WHERE matched_place.matched_place_id IS NULL -- no match
    AND datasource_place.reference_id > 3
    GROUP BY datasource_place.reference_id
) no_geoscrub_place
JOIN reference USING (reference_id)
JOIN place USING (reference_id)
GROUP BY reference_id, reference.shortname, no_geoscrub_place
ORDER BY reference_id

locationevents without location

373

SET search_path TO "public.2012-12-06-23-19-52.r6672";
SELECT count(*)
FROM locationevent
WHERE location_id IS NULL

taxonoccurrences without locationevent

254,588

SET search_path TO "public.2012-12-06-23-19-52.r6672";
SELECT count(*)
FROM taxonoccurrence
WHERE locationevent_id IS NULL

extra taxondeterminations

Extra taxondeterminations have isoriginal = true

3,904,804 of 29,779,347 = 13%

SET search_path TO "public.2012-11-06-16-50-06.r6038";
SELECT count(*)
FROM taxondetermination
WHERE isoriginal

non-extra taxondeterminations

25,874,543 of 29,779,347

Source: 29,779,347 - 3,904,804 = 25,874,543

Analytical DB

Note that total row counts are out of date. They were likely fixed in changes between the r6360 (2012-11-21) and r6446 (2012-11-24) imports, due to the decrease in the backup file size indicating fewer analytical_stem rows. r6111 (2012-11-9) also fixed a join to use geoscrub_cultivated rather than geoscrub, which contained many duplicate records.

speciesBinomials

207,066

(takes 11 min)

SET search_path TO "public.2012-11-16-20-51-10.r6262";
SELECT count(DISTINCT "speciesBinomial") FROM analytical_aggregate;

non-NULL georeferenceValid

3,985,866 of 54,344,800

SET search_path TO "public.2012-11-13-19-16-20.r6152";
SELECT count(*)
FROM analytical_stem
WHERE "georeferenceValid" IS NOT NULL

higherPlantGroup

higherPlantGroup count
<NULL> 1,486,319
bryophytes 376,128
gymnosperms (conifers) 4,262,022
ferns and allies 564,406
flowering plants 47,517,123
gymnosperms (non-conifer) 24,313
SET search_path TO "public.2012-11-06-16-50-06.r6038";
SELECT "higherPlantGroup", count(*)
FROM analytical_stem
GROUP BY "higherPlantGroup" 

isNewWorld

16,151,354 of 54,344,800

SET search_path TO "public.2012-11-06-16-50-06.r6038";
SELECT count(*)
FROM analytical_stem
WHERE "isNewWorld" 

threatened

189,855 of 54,344,800

SET search_path TO "public.2012-11-06-16-50-06.r6038";
SELECT count(*)
FROM analytical_stem
WHERE threatened