1 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Adding attributes to Report Wizard (and a dictionary problem too)

I know nothing about micro-moths, or their taxonomy, but I think that I have found an error in the dictionary. Of course I could be wrong because the dictionary is so complicated that I don't know what to do to check it.

I have just added a new attribute to the report wizard to return Bradley & Fletcher codes for micro moths. These codes are held in TAXON_LIST_ITEM.LST_ITM_CODE so should be straightforward to extract. Before anyone points it out, I am aware that this field also contains various other types of codes for other taxa, but at present I am not interestedin them).

My JOIN SQL looks like this

FROM #REPORT_OUTPUT INNER JOIN INDEX_TAXON_NAME
ON #REPORT_OUTPUT.LIST_ITEM_KEY = INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY
INNER JOIN TAXON_LIST_ITEM
ON INDEX_TAXON_NAME.RECOMMENDED_TAXON_LIST_ITEM_KEY = TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY

And the ATTRIBUTE_SQL is

#REPORT_OUTPUT.[Bradley and Fletcher codes] = TAXON_LIST_ITEM.LST_ITM_CODE

I realise that it is probably desirable to also filter for system supplied data on both tables and also to check that #REPORT_OUTPUT.TYPE = 'T', but I am not quite sure about the syntax for this so I will keep checking, any advice on this will be gratefully received.

I ran the wizard & selected all taxa from the LEPIDOPTERA (micro) list and got nearly 17000 records back, then repeated the process with the new attribute included and got the same number, so far so good.

However on closer inspection 56 records returned no value for my new attribute. All of those were either higher taxa (mostly family level), or records of a single species for which the preferred name was returned as Blastobasis adustella which is in the list of additional names.

Closer inspection of the offending records in the observations hierarchy revals that they are reported there as Blastobasis lignea. The LEPIDOPTERA list gives these as synonyms with lignea preferred.

What puzzles me is this. If I inspect a single occurrence I find the TLIK used is NHMSYS0000495791. If I put this into the INDEX_TAXON_NAME table it gives both the actual and preferred names as B. lignea and the Recommended TLIK as NHMSYS0020327125. However if I put this RTLIK backinto ITN as the TLIK it returns both actual and preferred as B. adustella and the RTLIK as NHMSYS0020327125 again.

Surely if they are synonyms then the preferred name and the recommended TLIK should be the same in both cases no?

Also it would appear that the original determination was as B. lignea (as reported by the observation heirarchy), the preferred name according to the preferred checklist. As far as I can tell all 30 or so records of this taxon have been recorded against this TLIK.

I have chosen to use the ITN table for the attribute because without it the report returns about 1500 records without a B&F code instead of 56.

So ultimately there are two questions 1. Can someone suggest any ways I might improve the join sql above? and 2. am I correct that this is a dictionary error (in which case I will post it over there instead?

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

2 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,627

Re: Adding attributes to Report Wizard (and a dictionary problem too)

There is a difference between the Preferred Name and the Recommended name. The Preferred name comes from the dictionary list  of the Taxon_List_Item_Key. So in this case NHMSYS0000495791 is from the Lepidoptera list and in that list the preferred name and the actual name are the same  so the Preferred_Name (key) is the same as the Taxon_List_Item_Key. There may also be within that list  taxa which are within that list considered as synonyms. These will have a differeent TLI key, but the with the Preferred_name (key) of NHMSYS0000495791.

The Recommended_Name is derived, not just from the same list, but from any list via the link provided through NameServer.  So in this case NameServer is pointing  TLI Key NHMSYS0000495791 to TLI Key NHMSYS0020327125 (Blastobasis lignea) which is on the List of Additional Name.

Rightly or wrongly what this is saying is that Blastobasis lignea, while the preferred name in the Lepidoptera list, is no longer the name to be used and that it  has been replaced by Blastobasis adustella.

I think the reason why your SQL is failing to give the results you expect is because the LST_ITM_CODE is only on the Lepodoptera list. As your join is using Recommended_Taxon_List_Item_Key from Index_Taxon_Name you are getting the LST_ITM_CODE from the List of  Additional Names where I suspect it isn't populated.

I am not totally sure about this, but possibly in using ITN you are avoiding the situation where list other than the Lepidoptera list has ben used. In most cases the  Recommended_taxon_List_Item key is pointing to the Lepidoptera List (the preferred list) where the LST_ITM_CODE is used, but in a few as in the example it is pointing to a list which doesn't have the LST_ITM_CODE.

This needs some playing around with, but  to get the query right I think you need to use ITN twice with a join  on both through Recommended_Taxon_List_Item_Key, then filter the result just to include the Lepidoptera list. Something on the lines of

FROM #REPORT_OUTPUT INNER JOIN INDEX_TAXON_NAME
ON #REPORT_OUTPUT.LIST_ITEM_KEY = INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY
INNER JOIN INDEX_TAXON_NAME ITN2
ON ITN2.RECOMMNEDED_TAXON_LIST_ITEM_KEY = INDEX_TAXON_NAME.RECOMMENDED_
TAXON_LIST_ITEM_KEY
INNER JOIN
TAXON_LIST_ITEM
ON ITN2..TAXON_LIST_ITEM_KEY  = TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY
INNER JOIN TAXON_LIST_VERSION ON TAXON_LIST_VERSION_TAXON_LIST_VERSION_KEY
= TAXON_LIST_ITEM.TAXON_LIST_VERSION_KEY AND TAXON_LIST_VERSION.TAXON_LIST_KEY =  'NHMSYS0000495044'  AND TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY = TAXON_LIST_ITEM.PREFERRED_NAME

This works by finding all possible links  (based on NameServer)  for the Taxon_List_Item_Key of your data, then filtering out just those from the Lepidoptera list, but ignoring any synonyms.

Mike Weideli

MikeWeideli's Website

3 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Adding attributes to Report Wizard (and a dictionary problem too)

Thanks Mike

That was interesting, after I had sorted out the various typos (I'm impressed that you constructed that SQL freehand, I tend to use management studio because I find it much easier with a visual tool), I tested it and it returned about 150 null values for the LST_ITM_CODE out of 17000. These included several species this time, so it looked like a step backwards from the 50 odd of one species I got yesterday. But then I replaced the TAXON_LIST_KEY with that of the lepidoptera (Micro) list and got only 9 nulls, all of which were at family level.

I'm still not quite clear why there are two preferred lepidoptera lists as there is certainly overlap between the two. How does recorder manage this?

I now have two versions of my new attribute, one which filters on the micro list as described above and one which uses the main lep list. The latter I tested on a selection of all records from the main lep list (151000 records) and about 30000 came back empty. I need to do more research about these codes it seems.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website