1

Re: Taxon UK Native

Is the UK native status actually used/up to date?

I've tried pulling out all non-native taxa according to Recorder, using SQL and through the report wizard, but it looks like it don't work.

Using "Taxon UK Native is equal to yes" in the report wizard (for some reason the boolean is flipped... which I realised after the entire database was returned to me) it returns 31 records:

Collembola
Yellow-legged Gull
Harlequin Ladybird
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull
Yellow-legged Gull

I was of course, expecting a few more (correct ones) than that.

Has anyone produced a non-native rucksack they could share?

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

2

Re: Taxon UK Native

Running the following SQL  produces a fairly long list, some of which looks OK. I thought that the indicator was supposed to be correctly set, but NHM would need to confirm this. 

SELECT INDEX_TAXON_NAME.ACTUAL_NAME, INDEX_TAXON_NAME.COMMON_NAME
FROM TAXON_VERSION INNER JOIN TAXON_LIST_ITEM ON TAXON_VERSION.TAXON_VERSION_KEY = TAXON_LIST_ITEM.TAXON_VERSION_KEY INNER JOIN INDEX_TAXON_NAME ON TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY =INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY WHERE TAXON_VERSION.UK_NATIVE=0

The Report Wizard generates the following query where UK native is set to 'No' (false) which on the surface of it also looks ok

SELECT Distinct Taxon_Occurrence.Taxon_Occurrence_Key AS Occurrence_Key, 'T' AS Type, Convert(char(16), null) collate SQL_Latin1_General_CP1_CI_AS as SURVEY_KEY, Convert(char(16), null) collate SQL_Latin1_General_CP1_CI_AS as SURVEY_EVENT_KEY, Convert(char(16), null) collate SQL_Latin1_General_CP1_CI_AS as SAMPLE_KEY, Convert(char(16), null) collate SQL_Latin1_General_CP1_CI_AS as LIST_ITEM_KEY , Convert(char(1),Null) as [Sample Type] into "#REPORT_OUTPUT" From ((((((Taxon_Occurrence LEFT JOIN Sample ON Taxon_Occurrence.sample_Key = Sample.Sample_Key ) LEFT JOIN Taxon_Determination ON Taxon_Determination.Taxon_Occurrence_Key = Taxon_Occurrence.Taxon_Occurrence_Key and Taxon_Determination.Preferred = 1 ) LEFT JOIN Survey_Event ON Survey_Event.Survey_Event_Key = Sample.Survey_Event_Key ) LEFT JOIN Index_Taxon_Name ITN ON (ITN.Taxon_List_Item_Key = Taxon_Determination.Taxon_List_Item_Key and ITN.System_Supplied_Data=1) ) LEFT JOIN Survey ON Survey.Survey_Key = Survey_Event.Survey_Key ) LEFT JOIN Taxon_List_Item ON Taxon_List_Item.Taxon_List_Item_Key = ITN.Taxon_List_Item_Key ) LEFT JOIN Taxon_Version ON Taxon_Version.Taxon_Version_Key = Taxon_List_Item.Taxon_Version_Key WHERE Survey.Survey_Key NOT IN (SELECT Survey_Key FROM User_Survey_Restriction USR WHERE USR.Name_Key = 'LC00000100000001') AND ((TAXON_VERSION.UK_NATIVE = 0))
WHERE (((TAXON_VERSION.UK_NATIVE)=0));

Mike Weideli

3

Re: Taxon UK Native

MikeWeideli wrote:

SELECT INDEX_TAXON_NAME.ACTUAL_NAME, INDEX_TAXON_NAME.COMMON_NAME
FROM (TAXON_VERSION INNER JOIN TAXON_LIST_ITEM ON TAXON_VERSION.TAXON_VERSION_KEY = TAXON_LIST_ITEM.TAXON_VERSION_KEY) INNER JOIN INDEX_TAXON_NAME ON TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY
=INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY

Is this SQL missing something (or am I - not sure what this is meant to do)?

This is the SQL my report wizard produces when "uk taxon native is equal to no" - which yes, looks fine - but it's pulling out native species. So I guess its not the boolean thats been flipped, but rather the information in TAXON_VERSION.UK_NATIVE that is wrong.

SELECT  Distinct Taxon_Occurrence.Taxon_Occurrence_Key AS Occurrence_Key, 'T' AS Type 
FROM (((((((((Taxon_Occurrence LEFT JOIN Sample ON Taxon_Occurrence.sample_Key = Sample.Sample_Key ) LEFT JOIN Taxon_Determination ON Taxon_Determination.Taxon_Occurrence_Key = Taxon_Occurrence.Taxon_Occurrence_Key and Taxon_Determination.Preferred = 1 ) LEFT JOIN Survey_Event ON Survey_Event.Survey_Event_Key = Sample.Survey_Event_Key ) LEFT JOIN Index_Taxon_Name ITN ON (ITN.Taxon_List_Item_Key = Taxon_Determination.Taxon_List_Item_Key and ITN.System_Supplied_Data=1) ) LEFT JOIN Survey ON Survey.Survey_Key = Survey_Event.Survey_Key ) LEFT JOIN Taxon_List_Item ON Taxon_List_Item.Taxon_List_Item_Key = ITN.Taxon_List_Item_Key ) LEFT JOIN Taxon_Version ON Taxon_Version.Taxon_Version_Key = Taxon_List_Item.Taxon_Version_Key ) LEFT JOIN Index_Taxon_Name ITN2 ON ITN.Recommended_Taxon_List_Item_Key=ITN2.Recommended_Taxon_List_Item_Key ) LEFT JOIN Taxon_List_Version TLV ON TLV.Taxon_List_Version_Key = ITN2.Taxon_List_Version_Key ) LEFT JOIN Taxon_List TL ON TL.Taxon_List_Key = TLV.Taxon_List_Key 
WHERE Survey.Survey_Key NOT IN (SELECT Survey_Key FROM User_Survey_Restriction USR WHERE  USR.Name_Key = 'SR000351000001VG') AND TL.Taxon_List_Key = 'NBNSYS0000000079'  AND ((TAXON_VERSION.UK_NATIVE = 0))
Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

4

Re: Taxon UK Native

Sorry a bit of my SQl was missed in the paste. However on reflection there is definitely something wrong here as the majority of the taxa have a 0, which implies that they are not native which is onbiously wrong. Perhaps it is a simple as the indicator just being the wrong way round, but perhaps there is a bigger issue here than this. I have asked JNCC if they know what the situation is.

Mike Weideli

5

Re: Taxon UK Native

The response I have from JNCC is as follows :

"My understanding is that the field is not reliable and should not be used for extracting non-native species. I think that NHM are looking to improve/change it but expect this is not a priority at the moment in light of changes to the dictionary structure and verification of links. "

Mike Weideli

6

Re: Taxon UK Native

There are a couple of LRCs who manage non-native rucksacks I think... try asking on the Alerc forum to see if anyone is willing to share?

-----------------
Teresa Frost | Wetland Bird Survey National Organiser | BTO
Other hat  | National Forum for Biological Recording Council
(Old hats  | NBN Board, ALERC Board, CBDC, KMBRC)