4.2 How is gbif IMPORT done by @admin

1. visit the link of the GBIF filter + download as a taxonList and as Simple

2. using the taxonList export (it has Tab as separator), add new taxa into the convertor or update their info

   • filter out names with less than 20 occurrences to reduce it a little bit..
   • filter out FAMILY rank
   • replace all ’ with ’’
   • fill NULL in V column empty cells
   • use the Excel formula, that is prepared to be in the first, newly pasted column

   ="INSERT INTO gbif.taxa (taxon_key, scientific_name, accepted_taxon_key, accepted_scientific_name, taxon_rank, species, species_key)
   VALUES
   ("&B2&",'"&C2&"',"&D2&",'"&E2&"','"&G2&"','"&U2&"',"&V2&")
   ON CONFLICT (taxon_key)
   DO UPDATE SET
    accepted_taxon_key = EXCLUDED.accepted_taxon_key,
    accepted_scientific_name = EXCLUDED.accepted_scientific_name,
    taxon_rank = EXCLUDED.taxon_rank,
    species = EXCLUDED.species,
    species_key = EXCLUDED.species_key
    ;"

3. cleanup & truncate table: sql ALTER TABLE IF EXISTS gbif.records DROP CONSTRAINT IF EXISTS gbif_records_taxon_fkey; DROP INDEX IF EXISTS gbif.gbif_records_coords_idx; DROP INDEX IF EXISTS gbif.gbif_records_taxon_key_idx; DROP INDEX IF EXISTS gbif.gbif_records_year_idx; DROP INDEX IF EXISTS gbif.records_institution_code_idx; TRUNCATE TABLE gbif.records RESTART IDENTITY;

4. upload occurrences from Simple on megastroj, import records.csv via admin.pladias.cz Console docker cp ./data/records.csv adminpladiascz-app-1:/srv/temp/data/records.csv && nohup docker exec adminpladiascz-app-1 bin/console gbif:importRecords > output.log 2>&1 &

5. due foreignKey remove records those are not mapped sql DELETE FROM gbif.records r WHERE NOT EXISTS (SELECT 1 FROM gbif.taxa t WHERE t.taxon_key = r.taxon_key);

6. restore indexes ```sql ALTER TABLE IF EXISTS gbif.records ADD CONSTRAINT gbif_records_taxon_fkey FOREIGN KEY (taxon_key) REFERENCES gbif.taxa (taxon_key) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE;

   CREATE INDEX IF NOT EXISTS gbif_records_coords_idx ON gbif.records USING gist (coords) TABLESPACE pg_default;

   CREATE INDEX IF NOT EXISTS gbif_records_taxon_key_idx ON gbif.records USING btree (taxon_key ASC NULLS LAST) TABLESPACE pg_default;

   CREATE INDEX IF NOT EXISTS gbif_records_year_idx ON gbif.records USING btree (year ASC NULLS LAST) TABLESPACE pg_default;

   CREATE INDEX IF NOT EXISTS records_institution_code_idx ON gbif.records USING btree (institution_code COLLATE pg_catalog.”default” ASC NULLS LAST) TABLESPACE pg_default; ```