Animals for Continents

-- REPLACE serves to eliminate new-line characters causing import errors
-- join to catalogue_number serves to eliminate leading unidentified whitespace from the raw data, enforcing use of processed version
-- set explicit ESCAPE for control characters; ENCLOSE fields in "


SET NAMES 'utf8';
SELECT
o.data_provider_id AS 'data_provider_id',
o.data_resource_id AS 'data_resource_id', 
dp.name AS 'Publisher', 
dr.name AS 'Dataset', 
REPLACE(IFNULL(r.collector_name, ""),  "\n", ", ") AS 'Collector_name', 
'' AS 'Collector_number', 
'' AS 'Field_number', 
'' AS 'GUID', 
o.occurrence_date AS 'Date_collected', 
r.institution_code AS 'Institution_code',
r.collection_code AS 'Collection_code',
cat.code AS 'Catalogue_number', 
lub.br_value AS 'Basis_of_record', 
ir.url as 'Image_url', 
o.modified AS 'last_indexed',  
REPLACE(IFNULL(r.identifier_name, ""),  "\n", " ")  AS 'Identifier',  
r.identification_date AS 'Identification_date',  
r.scientific_name AS 'Scientific_name_original', 
r.author AS 'Author',  
nn.canonical AS 'Scientific_name', 
r.kingdom AS 'Kingdom_original', 
kn.canonical AS 'Kingdom',  
r.phylum AS 'Phylum_original', 
pn.canonical AS 'Phylum',  
r.class AS 'Class_original', 
cn.canonical AS 'Class',  
r.order_rank AS 'Order_rank_original',  
orn.canonical AS 'Order_rank',  
r.family AS 'Family_original', 
fn.canonical AS 'Family',  
r.genus AS 'Genus_original', 
gn.canonical AS 'Genus',  
r.country AS 'Country_original',  
o.iso_country_code AS 'Country', 
REPLACE(IFNULL(r.locality, ""),  "\n", " ")  AS 'Locality', 
REPLACE(IFNULL(r.county, ""),  "\n", " ")  AS 'County',  
REPLACE(IFNULL(r.continent_ocean, ""),  "\n", " ")  AS 'Continent_or_Ocean',  
REPLACE(IFNULL(r.state_province, ""),  "\n", " ")  AS 'State_or_Province',  
c.region AS 'Region', 
dp.iso_country_code AS 'Provider_country',  
r.latitude AS 'Latitude', 
o.latitude AS 'Latitude_interpreted',
r.longitude AS 'Longitude', 
o.longitude AS 'Longitude_interpreted',
r.lat_long_precision AS 'Coordinate_precision', 
o.geospatial_issue AS 'geospatial_issue', 
o.cell_id AS 'cell_id',  
o.centi_cell_id AS 'centi_cell_id',  
r.min_depth AS 'Min_depth',  
r.max_depth AS 'Max_depth',  
r.min_altitude AS 'Min_altitude',  
r.max_altitude AS 'Max_altitude',  
r.altitude_precision AS 'Altitude_precision',  
CONCAT('http://data.gbif.org/occurrences/',o.id) AS 'gbif_portal_url',
CONCAT('http://data.gbif.org/ws/rest/occurrence/get/',o.id) AS 'gbif_webservice_url',   
o.id AS 'occurrence_id',
o.nub_concept_id AS 'nub_concept_id'

INTO OUTFILE '/var/local/large/db/mysql/Americas_Mammalia_20120218.txt'
FIELDS TERMINATED BY '\t' ESCAPED BY '\\' ENCLOSED BY '"'
LINES TERMINATED BY '\n'

FROM
occurrence_record o
JOIN data_resource dr ON dr.id = o.data_resource_id 
JOIN data_provider dp ON o.data_provider_id = dp.id 
LEFT JOIN country c ON o.iso_country_code = c.iso_country_code 
LEFT JOIN lookup_basis_of_record lub ON o.basis_of_record=lub.br_key
LEFT JOIN catalogue_number cat ON o.catalogue_number_id=cat.id
  LEFT JOIN image_record ir ON o.id = ir.occurrence_id  
INNER JOIN raw_occurrence_record r ON o.id=r.id 
INNER JOIN taxon_concept nc ON o.nub_concept_id=nc.id 
INNER JOIN taxon_name nn ON nc.taxon_name_id=nn.id 
LEFT JOIN taxon_concept kc ON nc.kingdom_concept_id=kc.id 
LEFT JOIN taxon_name kn ON kc.taxon_name_id=kn.id 
LEFT JOIN taxon_concept pc ON nc.phylum_concept_id=pc.id 
LEFT JOIN taxon_name pn ON pc.taxon_name_id=pn.id 
LEFT JOIN taxon_concept cc ON nc.class_concept_id=cc.id 
LEFT JOIN taxon_name cn ON cc.taxon_name_id=cn.id 
LEFT JOIN taxon_concept oc ON nc.order_concept_id=oc.id 
LEFT JOIN taxon_name orn ON oc.taxon_name_id=orn.id 
LEFT JOIN taxon_concept fc ON nc.family_concept_id=fc.id 
LEFT JOIN taxon_name fn ON fc.taxon_name_id=fn.id 
LEFT JOIN taxon_concept gc ON nc.genus_concept_id=gc.id 
LEFT JOIN taxon_name gn ON gc.taxon_name_id=gn.id 
 
WHERE

o.class_concept_id=359 -- Mammalia 
 
/*
o.class_concept_id=212 -- Aves
o.class_concept_id=358 -- Reptilia
o.class_concept_id=131 -- Amphibia 
o.class_concept_id=359 -- Mammalia 
*/
AND
c.continent_code in ('SA','NA')
/*
c.continent_code = 'AF'
c.continent_code in ('AS','EU') 
c.continent_code = 'OC'
c.continent_code in ('SA','NA')
*/
-- LIMIT 200
;


-- Africa_Reptilia:
-- Query OK, 182,863 rows affected (13 min 17.32 sec)

-- Africa_Aves:
-- 7,528,912

-- Africa_Amphibia:
-- Query OK, 134,703 rows affected (2 min 45.11 sec)

-- Africa_Mammalia
-- Query OK, 418,859 rows affected (6 min 36.69 sec)

-- Oceania_Mammalia
-- Query OK, 927,343 rows affected (16 min 38.06 sec)

-- Oceania_Aves
-- Query OK, 19,707,485 rows affected (2 hours 16 min 55.98 sec)

-- Oceania_Reptilia
-- Query OK, 708,771 rows affected (3 min 31.78 sec)

-- Oceania_Amphibia
-- Query OK, 325,344 rows affected (1 min 37.27 sec)

-- Eurasia_Amphibia
-- Query OK, 502,882 rows affected (37 min 20.35 sec)

-- Eurasia_Reptilia
-- Query OK, 444,345 rows affected (10 min 19.84 sec)

-- Eurasia_Mammalia
-- Query OK, 2,276,478 rows affected (25 min 37.85 sec)

-- Eurasia_Aves
-- 63,134,032

-- Americas_Aves
-- 100,565,307

-- Americas_Mammalia
-- 3,214,241

-- Americas_Reptilia
-- 1,898,500

-- Americas_Amphibia
-- 1,946,565