task #8792
Updated by Andreas Müller over 4 years ago
The functioning of the ERMS import (and PESI export) is explained at: [[Erms_mapping]] ## General information Old databases are available at BGBM-PESISQL\SQLExpress. Many issues have been handled in #1444. However as the ERMS-CDM is complex in often difficult there are many issues to take care for during upcoming imports. Comparison with SQL see #8792#note-30 below ## Data check before import * Check that the rule still is true that synonyms are directly linked to their accepted taxon and not via recursion (at least for field tu_accfinal (which has only less NULL values then tu_accfinal but never differs from tu_acctaxon in value) ~~~ sql SELECT * FROM [tu] INNER JOIN tu acc ON tu.tu_acctaxon = acc.id WHERE tu.tu_accfinal <> tu.id AND acc.id <> acc.tu_accfinal ~~~ * tu_acctaxon should not have relevant information ~~~ sql SELECT * FROM tu WHERE tu_acctaxon is NOT NULL AND tu_accfinal IS NULL OR tu_acctaxon <> tu_accfinal ~~~ * Ranks kingdom is identified by first number in tu_sp. Only the kingdoms 2-7 should are supported, others should not exist and the code may not work ~~~ sql SELECT * FROM tu WHERE tu.tu_sp not like '#2#%' AND tu.tu_sp not like '#3#%' AND tu.tu_sp not like '#4#%' AND tu.tu_sp not like '#5#%' AND tu.tu_sp not like '#6#%' AND tu.tu_sp not like '#7#%' ~~~ * Nomen nudum should not be accepted taxon of another taxon ~~~ sql SELECT acc.tu_status, acc.tu_displayname acc, syn.tu_accfinal, syn.id , syn.tu_displayname syn FROM tu acc INNER JOIN tu AS syn ON acc.id = syn.tu_accfinal WHERE (acc.tu_status = 3) AND syn.tu_accfinal <> syn.id ~~~ * accepted taxa should not have an unacceptreason ~~~ sql SELECT * FROM tu WHERE tu_status = 1 AND tu_unacceptreason IS NOT NULL ~~~ with some rare exceptions (in 2019 6 cases, not yet discussed if necessary) * tu_status There should be no synonyms with tu_status == 1 ~~~ sql SELECT * FROM [tu] INNER JOIN tu acc ON tu.tu_accfinal = acc.id WHERE tu.tu_accfinal <> tu.id AND tu.tu_status = 1 ~~~ Note: see below for reverse condition * Run ErmsImportActivator in CHECK_ONLY mode (and move above queries to validation) ## open issues * Misapplications Currently misapplications are identified by a UNION statement in ErmsTaxonImport.getAcceptedTaxaKeys(). It searches for misidentification patterns in tu.tu_unacceptreason . We also tried to evaluate the tu_authority field but searching for "sensu", "non", "auct". But realized that all these patterns are not used specific enough in ERMS. "Auct" is often simply used for misspellings, "sensu" is used for unassessed taxa, genus transfer, sensu in the meaning of sec and others or simply unclear. "Non" is generally to unspecific as it can be used for homonyms, too. So only the above query is possible but discussion with VLIZ is needed on data cleaning or standardization. Maybe even an own tu_status for misapplications is possible. Also the status of the following taxa should be checked again: ~~~ sql SELECT * from tu where tu.tu_unacceptreason like 'misidentification%' AND not (trim(tu.tu_unacceptreason) like 'misidentification' OR trim(tu.tu_unacceptreason) like 'misidentifications') ~~~ However, the PESI DW does not have a status "misapplied" but only synonym and therefore recognizing misapplications is not (yet) so important. But it might be useful for cleaning the authorship data and maybe for future DW versions. * Name status The name status is only guessed by the value in tu_unacceptreason. As this field is not standardized this is not exact. The current patterns are mostly taken from the SQL script + some obvious further mappings. A further discussion with VLIZ about the semantics of some values is necessary for further mapping (especially on ISZN related issues). Start with values with high counts! See also Y:\BDI\PESI\sh\DBs2PESI\vCDM\erms300_Match_Relation&Status.sql * Multiple open issues to be hanled by or discussed with VLIZ, mentioned in email to VLIZ 2019-12-23 * the status of taxa with status "alternate representation" should be discussed with VLIZ (and mayb ERS) * interim published taxa ~~~ sql SELECT * FROM tu WHERE tu_status = 9 ~~~ None, or only very few should exist. Open question: should they be exported at all? * type designations without type ~~~ sql SELECT * FROM tu WHERE tu_typedesignation IS NOT NULL AND tu_typetaxon IS NULL ORDER BY tu_rank, tu_typedesignation ~~~ These have a typedesignation (by original designation, by subsequent designation, ...) but no name. All are of rank subgenus or higher (and therfore should have a name type, not a specimen type). Current count = 3177. Possible explanations: name types are not European or missing data (not discussed with VLIZ yet) ## Protozoa and Chromista * which nomenclatural code to use? (currently both ICNafp, see ErmsTransformer.kingdomId2NomCode) * what is correct formatting for infrageneric taxa (only Chormista), ERMS currently formats like ICZN ~~~ sql SELECT id, tu_displayname, tu_sp FROM tu WHERE ( (tu_sp LIKE '#7#%') OR (tu_sp LIKE '#5#%')) AND (tu_rank > 180) AND (tu_rank < 220) ~~~ ## Distributions * take **occurrence_id** into account (currently not yet used, information about distribution status, needs mapping) * the mapping of areas to countries and the "European" is not clear (handled in above mail) * distributions attached to synonyms ~~~ sql SELECT v.*, t.tu_status, t.tu_displayname, t.id, t.tu_accfinal FROM dr v INNER JOIN tu t ON v.tu_id = t.id WHERE t.tu_accfinal <> t.id ORDER BY tu_status ~~~ very few distributions are attached to synonyms (n=23), this was 0 before. Check with VLIZ if this is dirty data. see also #1523 for further information * There are exact duplicates: (#1444#note-37, handled in mail) * Some distributions have a year, is this relevant? ~~~ sql SELECT tu_id FROM dr WHERE endyear IS NOT NULL ~~~ Does it mean the taxa in not observed there anymore is it really absent? Do we generally include extinct taxa in PESI or should we neglect them? ~~~ sql SELECT count(*) as n , tu_id, gu_id, source_id, unacceptsource_id, unacceptreason, tu_valid, valid_flag, certain_flag, map_flag, endemic_flag, exotic_flag, typelocality_flag, specimen_flag, vagrant_flag, occurrence_id, origin_id, invasiveness_id, lat, long, depthshallow, depthdeep, beginyear, beginmonth, beginday, endyear, endmonth, endday, min_abundance, max_abundance, qualitystatus_id, noteSortable FROM dr GROUP BY tu_id, gu_id, source_id, unacceptsource_id, unacceptreason, tu_valid, valid_flag, certain_flag, map_flag, endemic_flag, exotic_flag, typelocality_flag, specimen_flag, vagrant_flag, occurrence_id, origin_id, invasiveness_id, lat, long, depthshallow, depthdeep, beginyear, beginmonth, beginday, endyear, endmonth, endday, min_abundance, max_abundance, qualitystatus_id, noteSortable HAVING count(*) > 1 ORDER BY n DESC ~~~ a few more without using qualitystatus_id in group by/select (I added column noteSortable) ## Languages * very few (3?) really do not have a 639_3 code, others should be updated, handled in mail ~~~ sql SELECT LanID, LanName, Status, Partner_Agency, [639_3], [639_2], B_Code, bt_equiv, [639_1], Element_Scope, Language_Type, Documentation FROM languages WHERE (LanID IN (SELECT lan_id FROM vernaculars)) AND (LanID <> [639_3]) ORDER BY LanName ~~~ * in ERMS are still 2 records for Romanian (ron and run), formerly one was Moldovian (which does not exist or Moldavian which is the same as Romanian - according to SIL) ~~~ sql SELECT LanID, LanName, Status, Partner_Agency, [639_3], [639_2], B_Code, bt_equiv, [639_1], Element_Scope, Language_Type, Documentation FROM languages WHERE [639_1] = 'ro' OR [639_2] = 'ron' OR [639_2] = 'rum' OR LanID = 'ron' OR LanID = 'rum' ~~~ One of them should be removed by VLIZ, but are already mapped to the same PESI languages (id=33, id=29 was removed) ## Data cleaning: * tu_accfinal IS NULL ~~~ sql SELECT * FROM tu WHERE tu_accfinal IS NULL ~~~ should be checked. VLIZ should fix these (n=479). Currently they are handled as accepted taxa. * synonyms being the accepted taxon of there parent ~~~ sql SELECT * FROM tu syn LEFT JOIN tu AS accTaxon ON myTaxon.tu_accfinal = accTaxon.id WHERE syn.tu_accfinal IS NOT NULL AND myTaxon.id = accTaxon.tu_parent AND accTaxon.id <> myTaxon.id ~~~ These are often alternate representations (children) or autonyms (parents). They are explicitly handled in ErmsTaxonImport.getAcceptedTaxaKeys() Note: accTaxon.id <> myTaxon.id is to remove Biota from query * tu_status There should be no synonyms with tu_status == 1 ~~~ sql SELECT * FROM [tu] INNER JOIN tu acc ON tu.tu_accfinal = acc.id WHERE tu.tu_accfinal <> tu.id AND tu.tu_status = 1 ~~~ There should be no accepted taxa with status 2,3,5 (unaccepted, nomen nudem, alternate representation) ~~~ sql SELECT * FROM [tu] INNER JOIN tu acc ON tu.tu_accfinal = acc.id WHERE tu.tu_accfinal = tu.id AND tu.tu_status NOT IN (1, 7, 10, 8, 9, 6) ORDER BY tu.tu_status ~~~ ## other open issues * is there any meaning in the tu.tsn attribute? * trim on notes, links, and others (difficult as they are often ntext, but sortableNote can be used) * standardize sources.source_year field (currently many are imported as freetext) ## known incorrect name handling on ERMS portal (http://www.marbef.org/data/aphia.php, or WoRMS http://www.marinespecies.org/) * 4 parted names with incorrect rank marker, e.g (752445: Cyclonassa neritea var. kamieschensis lactescens <=> neritea kamieschensis subvar. lactescens), these should be logged during import or validation (but maybe commented) * check again special cases from #1444#note-38