{"sql":"WITH kdes AS ( SELECT count(distinct species_code) num_species, count(distinct prod_id) FILTER (WHERE lower(type) = 'node') num_nodes, count(distinct prod_id) FILTER (WHERE lower(type) = 'corridor') num_corridors FROM kdes_activity ), metasites AS ( SELECT count(DISTINCT species_code) AS num_species, count(DISTINCT prod_id) AS num_metasites FROM network_points WHERE release_stage >= 2 AND TYPE = 'METASITE' ), kdes_per_species AS ( SELECT json_agg(kdes ORDER BY taxon_group, common_name) species_kdes FROM ( SELECT taxon_group, common_name, json_build_object( 'taxon_group', taxon_group, 'species_code', species_code, 'commonName', common_name, 'scientificName', scientific, 'kdes', kdes, 'metasites', metasites, 'metalines', metalines) AS kdes FROM ( SELECT taxon_group, species_code, common_name, scientific, json_build_array(count(distinct prod_id) FILTER (WHERE lower(type) = 'node'), count(distinct prod_id) FILTER (WHERE lower(type) = 'corridor')) as kdes FROM kdes_activity GROUP BY taxon_group, species_code, common_name, scientific ) kdes FULL JOIN ( SELECT taxon_group, species_code, common_name, scientific_name AS scientific, count(distinct prod_id) AS metasites FROM network_points JOIN species_profiles USING (species_code) WHERE TYPE = 'METASITE' AND release_stage >= 2 GROUP BY species_code ) metasites USING (taxon_group, species_code, common_name, scientific) FULL JOIN ( SELECT taxon_group, species_code, common_name, scientific_name AS scientific, count(distinct source) AS metalines FROM network_lines JOIN species_profiles USING (species_code) WHERE TYPE ~ 'META' AND release_stage >= 2 GROUP BY species_code ) metalines USING (taxon_group, species_code, common_name, scientific) ORDER BY taxon_group, common_name ) foo ), papers AS ( SELECT count(distinct zotero_id) num_papers, count(distinct species_code) as num_species_reviewed FROM lit_review_paperinfo WHERE keep_paper and active ), points AS ( SELECT count(*) as num_points, count(distinct concat_ws('-', dataset_id, species_code, animal_id)) as num_animals FROM kdes_activity_data_merged ), contributors AS ( SELECT count(distinct provider_id) as num_contributors, count(distinct dataset_id) as num_datasets FROM kdes_dataset_providers ) SELECT kdes.num_species as num_species_kdes, metasites.num_species AS num_species_metasites, metasites.num_metasites, papers.num_papers, papers.num_species_reviewed, points.num_points, points.num_animals, num_contributors, num_datasets, num_nodes, num_corridors, kdes_per_species.species_kdes FROM kdes, metasites, papers, points, contributors, kdes_per_species","num_records":1,"records":[{"num_species_kdes":8,"num_species_metasites":56,"num_metasites":1213,"num_papers":1169,"num_species_reviewed":110,"num_points":101131,"num_animals":415,"num_contributors":37,"num_datasets":63,"num_nodes":27,"num_corridors":17,"species_kdes":"[{\"taxon_group\" : \"fish\", \"species_code\" : \"THAL\", \"commonName\" : \"Albacore Tuna\", \"scientificName\" : \"Thunnus alalunga\", \"kdes\" : null, \"metasites\" : 22, \"metalines\" : 9}, {\"taxon_group\" : \"fish\", \"species_code\" : \"THTH\", \"commonName\" : \"Atlantic Bluefin Tuna\", \"scientificName\" : \"Thunnus thynnus\", \"kdes\" : null, \"metasites\" : 28, \"metalines\" : 17}, {\"taxon_group\" : \"fish\", \"species_code\" : \"CEMA\", \"commonName\" : \"Basking Shark\", \"scientificName\" : \"Cetorhinus maximus\", \"kdes\" : null, \"metasites\" : 8, \"metalines\" : 3}, {\"taxon_group\" : \"fish\", \"species_code\" : \"ALSU\", \"commonName\" : \"Bigeye Thresher Shark\", \"scientificName\" : \"Alopias superciliosus\", \"kdes\" : null, \"metasites\" : 4, \"metalines\" : null}, {\"taxon_group\" : \"fish\", \"species_code\" : \"THOB\", \"commonName\" : \"Bigeye Tuna\", \"scientificName\" : \"Thunnus obesus\", \"kdes\" : null, \"metasites\" : 38, \"metalines\" : 8}, {\"taxon_group\" : \"fish\", \"species_code\" : \"THAT\", \"commonName\" : \"Blackfin Tuna\", \"scientificName\" : \"Thunnus atlanticus\", \"kdes\" : null, \"metasites\" : 1, \"metalines\" : null}, {\"taxon_group\" : \"fish\", \"species_code\" : \"ISIN\", \"commonName\" : \"Black Marlin\", \"scientificName\" : \"Istiompax indica\", \"kdes\" : null, \"metasites\" : 8, \"metalines\" : 6}, {\"taxon_group\" : \"fish\", \"species_code\" : \"MANI\", \"commonName\" : \"Blue Marlin\", \"scientificName\" : \"Makaira nigricans\", \"kdes\" : null, \"metasites\" : 33, \"metalines\" : 15}, {\"taxon_group\" : \"fish\", \"species_code\" : \"MOTA\", \"commonName\" : \"Chilean Devil Ray\", \"scientificName\" : \"Mobula tarapacana\", \"kdes\" : null, \"metasites\" : 2, \"metalines\" : 1}, {\"taxon_group\" : \"fish\", \"species_code\" : \"COHI\", \"commonName\" : \"Common Dolphinfish\", \"scientificName\" : \"Coryphaena hippurus\", \"kdes\" : null, \"metasites\" : 11, \"metalines\" : null}, {\"taxon_group\" : \"fish\", \"species_code\" : \"ALVU\", \"commonName\" : \"Common Thresher Shark\", \"scientificName\" : \"Alopias vulpinus\", \"kdes\" : null, \"metasites\" : 3, \"metalines\" : null}, {\"taxon_group\" : \"fish\", \"species_code\" : \"MOBI\", \"commonName\" : \"Giant Manta Ray\", \"scientificName\" : \"Mobula birostris\", \"kdes\" : null, \"metasites\" : 2, \"metalines\" : null}, {\"taxon_group\" : \"fish\", \"species_code\" : \"SPMO\", \"commonName\" : \"Great Hammerhead\", \"scientificName\" : \"Sphyrna mokarran\", \"kdes\" : null, \"metasites\" : 4, \"metalines\" : 1}, {\"taxon_group\" : \"fish\", \"species_code\" : \"CACA\", \"commonName\" : \"Great White Shark\", \"scientificName\" : \"Carcharodon carcharias\", \"kdes\" : null, \"metasites\" : 19, \"metalines\" : 11}, {\"taxon_group\" : \"fish\", \"species_code\" : \"TEPF\", \"commonName\" : \"Longbill Spearfish\", \"scientificName\" : \"Tetrapturus pfluegeri\", \"kdes\" : null, \"metasites\" : 1, \"metalines\" : null}, {\"taxon_group\" : \"fish\", \"species_code\" : \"THOR\", \"commonName\" : \"Pacific Bluefin Tuna\", \"scientificName\" : \"Thunnus orientalis\", \"kdes\" : null, \"metasites\" : 12, \"metalines\" : 7}, {\"taxon_group\" : \"fish\", \"species_code\" : \"LANA\", \"commonName\" : \"Porbeagle\", \"scientificName\" : \"Lamna nasus\", \"kdes\" : null, \"metasites\" : 5, \"metalines\" : 1}, {\"taxon_group\" : \"fish\", \"species_code\" : \"ISPL\", \"commonName\" : \"Sailfish\", \"scientificName\" : \"Istiophorus platypterus\", \"kdes\" : null, \"metasites\" : 31, \"metalines\" : 13}, {\"taxon_group\" : \"fish\", \"species_code\" : \"SPLE\", \"commonName\" : \"Scalloped Hammerhead\", \"scientificName\" : \"Sphyrna lewini\", \"kdes\" : null, \"metasites\" : 9, \"metalines\" : null}, {\"taxon_group\" : \"fish\", \"species_code\" : \"ISOX\", \"commonName\" : \"Shortfin Mako\", \"scientificName\" : \"Isurus oxyrinchus\", \"kdes\" : null, \"metasites\" : 29, \"metalines\" : null}, {\"taxon_group\" : \"fish\", \"species_code\" : \"CAFA\", \"commonName\" : \"Silky Shark\", \"scientificName\" : \"Carcharhinus falciformis\", \"kdes\" : null, \"metasites\" : 6, \"metalines\" : null}, {\"taxon_group\" : \"fish\", \"species_code\" : \"KAPE\", \"commonName\" : \"Skipjack Tuna\", \"scientificName\" : \"Katsuwonus pelamis\", \"kdes\" : null, \"metasites\" : 9, \"metalines\" : 1}, {\"taxon_group\" : \"fish\", \"species_code\" : \"THMAC\", \"commonName\" : \"Southern Bluefin Tuna\", \"scientificName\" : \"Thunnus maccoyii\", \"kdes\" : null, \"metasites\" : 12, \"metalines\" : 4}, {\"taxon_group\" : \"fish\", \"species_code\" : \"KAAU\", \"commonName\" : \"Striped Marlin\", \"scientificName\" : \"Kajikia audax\", \"kdes\" : null, \"metasites\" : 23, \"metalines\" : null}, {\"taxon_group\" : \"fish\", \"species_code\" : \"ACSO\", \"commonName\" : \"Wahoo\", \"scientificName\" : \"Acanthocybium solandri\", \"kdes\" : null, \"metasites\" : 7, \"metalines\" : 1}, {\"taxon_group\" : \"fish\", \"species_code\" : \"RHTY\", \"commonName\" : \"Whale Shark\", \"scientificName\" : \"Rhincodon typus\", \"kdes\" : null, \"metasites\" : 39, \"metalines\" : 15}, {\"taxon_group\" : \"fish\", \"species_code\" : \"KAAL\", \"commonName\" : \"White Marlin\", \"scientificName\" : \"Kajikia albida\", \"kdes\" : null, \"metasites\" : 13, \"metalines\" : null}, {\"taxon_group\" : \"fish\", \"species_code\" : \"THAB\", \"commonName\" : \"Yellowfin Tuna\", \"scientificName\" : \"Thunnus albacares\", \"kdes\" : null, \"metasites\" : 27, \"metalines\" : 5}, {\"taxon_group\" : \"mammals\", \"species_code\" : \"LAAC\", \"commonName\" : \"Atlantic White-sided Dolphin\", \"scientificName\" : \"Lagenorhynchus acutus\", \"kdes\" : null, \"metasites\" : 1, \"metalines\" : null}, {\"taxon_group\" : \"mammals\", \"species_code\" : \"BAMU\", \"commonName\" : \"Blue Whale\", \"scientificName\" : \"Balaenoptera musculus\", \"kdes\" : null, \"metasites\" : 33, \"metalines\" : 17}, {\"taxon_group\" : \"mammals\", \"species_code\" : \"BAED\", \"commonName\" : \"Bryde's Whale\", \"scientificName\" : \"Balaenoptera edeni\", \"kdes\" : null, \"metasites\" : 4, \"metalines\" : 2}, {\"taxon_group\" : \"mammals\", \"species_code\" : \"DUDU\", \"commonName\" : \"Dugong\", \"scientificName\" : \"Dugong dugon\", \"kdes\" : null, \"metasites\" : 9, \"metalines\" : 3}, {\"taxon_group\" : \"mammals\", \"species_code\" : \"BAPH\", \"commonName\" : \"Fin Whale\", \"scientificName\" : \"Balaenoptera physalus\", \"kdes\" : null, \"metasites\" : 29, \"metalines\" : null}, {\"taxon_group\" : \"mammals\", \"species_code\" : \"MENO\", \"commonName\" : \"Humpback Whale\", \"scientificName\" : \"Megaptera novaeangliae\", \"kdes\" : [2, 1], \"metasites\" : 115, \"metalines\" : null}, {\"taxon_group\" : \"mammals\", \"species_code\" : \"OROR\", \"commonName\" : \"Killer Whale\", \"scientificName\" : \"Orcinus orca\", \"kdes\" : null, \"metasites\" : 16, \"metalines\" : 9}, {\"taxon_group\" : \"mammals\", \"species_code\" : \"HYAM\", \"commonName\" : \"North Atlantic Bottlenose Whale\", \"scientificName\" : \"Hyperoodon ampullatus\", \"kdes\" : null, \"metasites\" : 1, \"metalines\" : null}, {\"taxon_group\" : \"mammals\", \"species_code\" : \"EUGL\", \"commonName\" : \"North Atlantic Right Whale\", \"scientificName\" : \"Eubalaena glacialis\", \"kdes\" : null, \"metasites\" : 4, \"metalines\" : 3}, {\"taxon_group\" : \"mammals\", \"species_code\" : \"EUJA\", \"commonName\" : \"North Pacific Right Whale\", \"scientificName\" : \"Eubalaena japonica\", \"kdes\" : null, \"metasites\" : 1, \"metalines\" : null}, {\"taxon_group\" : \"mammals\", \"species_code\" : \"STAT\", \"commonName\" : \"Pantropical Spotted Dolphin\", \"scientificName\" : \"Stenella attenuata\", \"kdes\" : null, \"metasites\" : 3, \"metalines\" : null}, {\"taxon_group\" : \"mammals\", \"species_code\" : \"GRGR\", \"commonName\" : \"Risso's Dolphin\", \"scientificName\" : \"Grampus griseus\", \"kdes\" : null, \"metasites\" : 2, \"metalines\" : 1}, {\"taxon_group\" : \"mammals\", \"species_code\" : \"BABR\", \"commonName\" : \"Sei Whale\", \"scientificName\" : \"Balaenoptera borealis\", \"kdes\" : null, \"metasites\" : 6, \"metalines\" : 4}, {\"taxon_group\" : \"mammals\", \"species_code\" : \"DEDE\", \"commonName\" : \"Short-beaked Common Dolphin\", \"scientificName\" : \"Delphinus delphis\", \"kdes\" : null, \"metasites\" : 1, \"metalines\" : null}, {\"taxon_group\" : \"mammals\", \"species_code\" : \"ARAU\", \"commonName\" : \"South American Fur Seal\", \"scientificName\" : \"Arctocephalus australis\", \"kdes\" : null, \"metasites\" : 4, \"metalines\" : null}, {\"taxon_group\" : \"mammals\", \"species_code\" : \"TRIN\", \"commonName\" : \"South American Manatee\", \"scientificName\" : \"Trichechus inunguis\", \"kdes\" : null, \"metasites\" : 2, \"metalines\" : null}, {\"taxon_group\" : \"mammals\", \"species_code\" : \"EUAU\", \"commonName\" : \"Southern Right Whale\", \"scientificName\" : \"Eubalaena australis\", \"kdes\" : null, \"metasites\" : 2, \"metalines\" : 1}, {\"taxon_group\" : \"mammals\", \"species_code\" : \"PHMA\", \"commonName\" : \"Sperm Whale\", \"scientificName\" : \"Physeter macrocephalus\", \"kdes\" : null, \"metasites\" : 24, \"metalines\" : 12}, {\"taxon_group\" : \"seabirds\", \"species_code\" : \"SYAN\", \"commonName\" : \"Ancient Murrelet\", \"scientificName\" : \"Synthliboramphus antiquus\", \"kdes\" : [4, 4], \"metasites\" : null, \"metalines\" : null}, {\"taxon_group\" : \"seabirds\", \"species_code\" : \"DIAN\", \"commonName\" : \"Antipodean Albatross\", \"scientificName\" : \"Diomedea antipodensis\", \"kdes\" : null, \"metasites\" : 11, \"metalines\" : 2}, {\"taxon_group\" : \"seabirds\", \"species_code\" : \"STPA\", \"commonName\" : \"Arctic Tern\", \"scientificName\" : \"Sterna paradisaea\", \"kdes\" : null, \"metasites\" : 29, \"metalines\" : null}, {\"taxon_group\" : \"seabirds\", \"species_code\" : \"THER\", \"commonName\" : \"Chatham Albatross\", \"scientificName\" : \"Thalassarche eremita\", \"kdes\" : [4, 0], \"metasites\" : null, \"metalines\" : null}, {\"taxon_group\" : \"seabirds\", \"species_code\" : \"PTAX\", \"commonName\" : \"Chatham Petrel\", \"scientificName\" : \"Pterodroma axillaris\", \"kdes\" : [3, 2], \"metasites\" : null, \"metalines\" : null}, {\"taxon_group\" : \"seabirds\", \"species_code\" : \"CABO\", \"commonName\" : \"Cory's Shearwater\", \"scientificName\" : \"Calonectris diomedea\", \"kdes\" : [5, 6], \"metasites\" : null, \"metalines\" : null}, {\"taxon_group\" : \"seabirds\", \"species_code\" : \"PHAL\", \"commonName\" : \"Short-tailed Albatross\", \"scientificName\" : \"Phoebastria albatrus\", \"kdes\" : null, \"metasites\" : 8, \"metalines\" : 4}, {\"taxon_group\" : \"seabirds\", \"species_code\" : \"MAGI\", \"commonName\" : \"Southern Giant Petrel\", \"scientificName\" : \"Macronectes giganteus\", \"kdes\" : null, \"metasites\" : 21, \"metalines\" : 1}, {\"taxon_group\" : \"seabirds\", \"species_code\" : \"PHIR\", \"commonName\" : \"Waved Albatross\", \"scientificName\" : \"Phoebastria irrorata\", \"kdes\" : null, \"metasites\" : 3, \"metalines\" : 3}, {\"taxon_group\" : \"seaturtles\", \"species_code\" : \"CHMY\", \"commonName\" : \"Green\", \"scientificName\" : \"Chelonia mydas\", \"kdes\" : [0, 1], \"metasites\" : 162, \"metalines\" : null}, {\"taxon_group\" : \"seaturtles\", \"species_code\" : \"ERIM\", \"commonName\" : \"Hawksbill\", \"scientificName\" : \"Eretmochelys imbricata\", \"kdes\" : null, \"metasites\" : 104, \"metalines\" : null}, {\"taxon_group\" : \"seaturtles\", \"species_code\" : \"LEKE\", \"commonName\" : \"Kemp's Ridley\", \"scientificName\" : \"Lepidochelys kempii\", \"kdes\" : null, \"metasites\" : 9, \"metalines\" : 7}, {\"taxon_group\" : \"seaturtles\", \"species_code\" : \"DECO\", \"commonName\" : \"Leatherback\", \"scientificName\" : \"Dermochelys coriacea\", \"kdes\" : [3, 2], \"metasites\" : 104, \"metalines\" : null}, {\"taxon_group\" : \"seaturtles\", \"species_code\" : \"CACAR\", \"commonName\" : \"Loggerhead\", \"scientificName\" : \"Caretta caretta\", \"kdes\" : [6, 1], \"metasites\" : 99, \"metalines\" : null}]"}]}