1 Topic by cpanter

  • cpanter
  • Member
  • Offline
  • Registered: 15-03-2011
  • Posts: 8

Topic: Extracting family & order from NBNData

Hi,
I am trying to extract the family and order for all species listed within Recorder - not from my records stored within recorder, but from the Recorder library in the NBNData file.
I am able to extract other information I need using Microsoft SQL server management studio.
However using this I cannot seem to find the table with the NBNData database which has the information relating to the family and order of species.
I seem to remember I had been able to do this a few years ago, but have forgotten how I did this.
Does anyone know what table this information is stored in?
Thanks!

2 Reply by MikeWeideli

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

Re: Extracting family & order from NBNData

Family and Order are not stored specifically in Recorder, but have to be derived from the Taxonomic hierarchy which is represented in table Taxon_List_Item key.   Each entry has a parent, so if you know the start point you can get the parents and the hierarchy. To make this a bit easier table Index_Taxon_Group identifies all the children for each TLI key so that the relationships are easier to get to. Even so getting to family etc. isn't that easy to do and is complicated by the fact that not all Taxon Lists  (even the recommended ones) have a complete hierearchy.  The following extract from the 'XML reports help' system illustrates some techniques for getting to the information. The next upgrade to Recorder (v6.18) will include a new table (Organism) and some user defined functions which will make it much easier to get to various levels in the hierarchy.


Reporting on Family etc.
Obtaining the family or other level from the taxonomic hierarchy is possible within the limitations of the Dictionaries used for the data. Some Dictionaries do not have any taxanomic hierarchy. The following illustrates SQL which will extract the Family if there is one and if not return the highest taxonomic level available. The Taxon_rank key can be changed to get whatever rank is required. You would need to link the temporary table #Fam to  Taxon_Determination and the other table needed to extract the data you require.

CREATE TABLE #Taxa (TLI_Key char(16) COLLATE SQL_Latin1_General_CP1_CI_AS, RecTLI_Key char(16) COLLATE SQL_Latin1_General_CP1_CI_AS)INSERT INTO #Taxa (TLI_KEY,RecTLI_Key)
SELECT DISTINCT TLI.Taxon_List_Item_Key,
ITN.Recommended_Taxon_List_Item_Key
FROM Taxon_List_Item TLI
INNER JOIN Index_Taxon_Name ITN ON TLI.Taxon_List_Item_Key = ITN.Taxon_List_Item_Key
INNER JOIN Taxon_Determination TDET ON TDET.Taxon_List_Item_Key=TLI.TAXON_LIST_ITEM_KEY
WHERE
ITN.Actual_Name like('%')

CREATE TABLE #Fam (REcTLi_Key char(16) COLLATE SQL_Latin1_General_CP1_CI_AS,Fam_Key char(16) COLLATE SQL_Latin1_General_CP1_CI_AS)

Insert  INTO #Fam (RecTli_key)
SELECT DISTINCT RECTLi_Key
FROM #Taxa
INNER JOIN Taxon_determination TDET
ON TDET.Taxon_List_Item_key = #Taxa.TLi_key

UPDATE #Fam set Fam_Key = ITG.Taxon_List_Item_Key
FROM Index_taxon_Group ITG
INNER JOIN #Fam ON #Fam.RecTli_key = ITG.Contained_List_Item_key
INNER JOIN Taxon_List_Item TLi
ON TLI.Taxon_list_item_key = ITG.Taxon_List_Item_Key AND TLI.Taxon_Rank_Key = 'NBNSYS0000000018'

UPDATE #Fam set Fam_Key = ITG.Taxon_List_Item_Key
FROM  Index_taxon_Group ITG
INNER JOIN #Fam ON #Fam.RecTli_key = ITG.Contained_List_Item_key
INNER JOIN Taxon_List_Item TLi
ON TLI.Taxon_list_item_key = ITG.Taxon_List_Item_Key AND ITG.Item_level =
(Select max(item_level) FROM Index_taxon_Group ITG3
WHERE  ITG3.Contained_list_item_Key =#Fam.Rectli_key)
AND #Fam.Fam_key Is null

Select ITn.Actual_Name, ITN2.Actual_Name from #Fam
INNER JOIN Index_Taxon_Name ITN
ON ITN.taxon_List_Item_key = #Fam.rectli_key
left JOin INDEX_Taxon_Name ITN2
ON ITn2.Taxon_List_Item_key = #Fam.fam_Key
ORDER BY ITN.Actual_name

DROP TABLE #Taxa

DROP Table #Fam

Mike Weideli

MikeWeideli's Website

3 Reply by TonyP

  • TonyP
  • TonyP
  • Veteran
  • Offline
  • From: Somerset
  • Registered: 15-05-2006
  • Posts: 306

Re: Extracting family & order from NBNData

Hi below is the SQL I use in SSMS to generate a table of orders and families. I can't remember if this just does if for recorded species so you'll need to bear that is mind. As you can see I do this in another database to avoid contaminating the NBNData;

-- =============================================
-- Author:        Tony Price
-- Create date: 07/09/2011
-- Description:   
-- Modified: 10/09/2012
-- Version 1.00
-- =============================================
Use NBNExtension
GO
DROP TABLE SysOrderFamily
GO

SELECT DISTINCT
ITN.Preferred_List,ITN2.Preferred_List  as Preferred_Avail, ITN.taxon_List_Item_key as Used_Key,ITN.Preferred_Name as Pref_Name,ITN2.Taxon_List_Item_Key as Rec_Key,
ITN.Actual_Name as Used_Name ,TL.Item_Name AS Used_Dict,ITN2.Actual_Name as REC_Name ,TL2.Item_Name as Rec_Dict,TV.Attribute as UsedAttrib,TLI.Preferred_name as pref_Key,
TV2.Attribute as RecAttrib,
'Preferred' =
CASE
          WHEN ITN.taxon_List_Item_key =  ITN2.Taxon_List_Item_Key THEN 'Yes'
          ELSE 'No'

END,
CAST('' AS VARCHAR(100)) AS [Order],
CAST('' AS VARCHAR(100)) AS Family
INTO SysOrderFamily
FROM NBNData.dbo.Taxon_Determination TDET
INNER JOIN NBNData.dbo.Index_Taxon_Name  ITN
    ON TDET.Taxon_List_item_Key = ITN.Taxon_List_item_Key
INNER JOIN NBNData.dbo.Taxon_List_Item TLI
    ON TLI.Taxon_List_Item_Key = TDET.taxon_List_Item_Key
INNER JOIN NBNData.dbo.TAXON_VERSION TV
    ON TV.Taxon_Version_Key = TLI.Taxon_version_key
INNER JOIN NBNData.dbo.Taxon_List_Version TLV
    ON TLV.Taxon_List_Version_Key = TLI.Taxon_List_Version_Key
INNER JOIN NBNData.dbo.Taxon_List TL
    ON TL.Taxon_List_Key = TLV.Taxon_List_Key
INNER JOIN NBNData.dbo.Index_Taxon_Name ITN2
    ON ITN.Recommended_taxon_List_Item_key = ITN2.taxon_List_Item_Key
INNER JOIN NBNData.dbo.Taxon_List_Item TLI2
    ON TLI2.Taxon_List_Item_Key = ITN2.taxon_List_Item_Key
INNER JOIN NBNData.dbo.Taxon_Version TV2
    ON TV2.taxon_Version_key =  TLI2.taxon_version_Key
INNER JOIN NBNData.dbo.Taxon_List_Version TLV2
    ON TLV2.Taxon_List_Version_Key = TLI2.Taxon_List_Version_Key
INNER JOIN NBNData.dbo.Taxon_List TL2
    ON TL2.Taxon_List_Key = TLV2.Taxon_List_Key

WHERE ITN.System_Supplied_Data = 1
AND ITN2.System_Supplied_Data = 1

UPDATE    OrdFam
    SET    [Order] = ITN.Actual_Name
    FROM    SysOrderFamily    OrdFam
    JOIN    NBNData.dbo.Index_Taxon_Name ITN1 ON ITN1.Taxon_List_Item_Key = OrdFam.Rec_Key
    JOIN    NBNData.dbo.Index_Taxon_Name ITN2 ON ITN2.Recommended_Taxon_List_Item_key=ITN1.Recommended_Taxon_List_Item_Key
    JOIN    NBNData.dbo.Index_Taxon_Group ITG ON ITN2.Taxon_List_Item_Key = ITG.Contained_List_Item_Key
    JOIN    NBNData.dbo.Taxon_List_Item TLI
        ON    ITG.Taxon_List_Item_Key = TLI.Taxon_List_Item_Key
        AND TLI.Taxon_Rank_Key = 'NBNSYS0000000012' -- Order
    JOIN    NBNData.dbo.Index_Taxon_Name ITN
        ON    ITN.Taxon_List_Item_Key = TLI.Taxon_List_Item_Key

UPDATE    OrdFam
    SET    Family = ITN.Actual_Name
    FROM    SysOrderFamily    OrdFam
    JOIN    NBNData.dbo.Index_Taxon_Name ITN1 ON ITN1.Taxon_List_Item_Key = OrdFam.Rec_Key
    JOIN    NBNData.dbo.Index_Taxon_Name ITN2 ON ITN2.Recommended_Taxon_List_Item_key=ITN1.Recommended_Taxon_List_Item_Key
    JOIN    NBNData.dbo.Index_Taxon_Group ITG ON ITN2.Taxon_List_Item_Key = ITG.Contained_List_Item_Key
    JOIN    NBNData.dbo.Taxon_List_Item TLI
        ON    ITG.Taxon_List_Item_Key = TLI.Taxon_List_Item_Key
        AND TLI.Taxon_Rank_Key = 'NBNSYS0000000018' -- Family
    JOIN    NBNData.dbo.Index_Taxon_Name ITN
        ON    ITN.Taxon_List_Item_Key = TLI.Taxon_List_Item_Key

Data Manger
Somerset Environmental Records Centre

TonyP's Website