1

Topic: Invasive and Non-Native Species

Quick general query on how to best extract records relating to these species (invasive and non-native) from R6. I had been planning on using the GBINNS species list but it strikes me that i am not sure how current this list now is?

Is there a better (more current) designation on which i could base the report? I seem to recall there being a (non)native status field within R6 but i've not used it before.

LERC Officer
Merseyside BioBank - Local Environmental Records Centre for North Merseyside
www.MerseysideBioBank.org.uk

2

Re: Invasive and Non-Native Species

There is a non-native indicator ('Y' is non native) in the Organism table. What  I don't know is how reliable it is. It certainly wasn't reliable at one time, which is why it hasn't been used in R6, but it may be Ok now. I will ask Chris Raper.  There is also the 'GBNNSIP checklist of non-native species' which could be the basis for identifying non native species.

Mike Weideli
Littlefield Consultancy - IT Consultants

3

Re: Invasive and Non-Native Species

Thanks Mike, i think that's the indicator i was thinking of. I had been basing reports on the GBNNSIP checklist but i'm not sure how up-to-date that is either/is it still maintained?

LERC Officer
Merseyside BioBank - Local Environmental Records Centre for North Merseyside
www.MerseysideBioBank.org.uk

4

Re: Invasive and Non-Native Species

I have asked Chris about the indicator. If it is  Ok this would be the easist thing to use. The trouble with lists is that they don't get kept up to date, so I suspect it isn't that reliable.

Mike Weideli
Littlefield Consultancy - IT Consultants

5

Re: Invasive and Non-Native Species

Sorry Mike, does this relate to the report filter "Taxon UK Native".
Assumption being i add a filter value of No and it returns anything with a non-native indicator of Y?

LERC Officer
Merseyside BioBank - Local Environmental Records Centre for North Merseyside
www.MerseysideBioBank.org.uk

6

Re: Invasive and Non-Native Species

The Report Wizard uses UK_Native in Taxon_Version, which I don't think is accurate. Chris has confirmed that he does maintain the flag in the Organism table. It is based on the  GBNNSIP checklist plus any exotics added recently.

I can change the report wizard to use the ORGANISM table.

Mike Weideli
Littlefield Consultancy - IT Consultants

7

Re: Invasive and Non-Native Species

Had me worried for a second. UK_Native = No just pulled out nearly every record in the database! I'll not use that then.

It would be great if you could make the amendments to the wizard. Do you know when you might have the time to take a look at it?

LERC Officer
Merseyside BioBank - Local Environmental Records Centre for North Merseyside
www.MerseysideBioBank.org.uk

8

Re: Invasive and Non-Native Species

I have had a go at this. The attached script should  implement both reporting and filtering on the Uk native status. I have checked that it works, but only with limited data and simple reports. Not sure how long it will take to run on a  large database.

If you notice any problems let me know.

Post's attachments

000000AV.sql 2.22 kb, 2 downloads since 2016-11-14 

You don't have the permssions to download the attachments of this post.
Mike Weideli
Littlefield Consultancy - IT Consultants

9 (edited by BDeed 15-11-2016 17:51:37)

Re: Invasive and Non-Native Species

Thanks Mike, the filter appears to have worked but is pulling back species which appear to be native (at least i had considered to be)?
e.g. Labium album (white dead nettle), Conium maculatum (Hemlock), Salix fragilis (Crack Willow), Petalophyllum ralfsii (Petalwort, nationally rare!).

Is there a way to check if this is a result of the script or an intentional markup within the organism table? (NON_NATIVE_FLAG is set to Y in the table).

Petalophyllum ralfsii does not appear on the GBNNSIP checklist so must have been added subsequently or is an error.

LERC Officer
Merseyside BioBank - Local Environmental Records Centre for North Merseyside
www.MerseysideBioBank.org.uk

10

Re: Invasive and Non-Native Species

During the running of the script function 5 of 6 reported that there were no rows effected. I assumed this was because the key it's looking to delete wasn't found but there could be wider implications?

DELETE FROM USABLE_TABLE WHERE USABLE_TABLE_KEY = 'LCA0002500000010'

LERC Officer
Merseyside BioBank - Local Environmental Records Centre for North Merseyside
www.MerseysideBioBank.org.uk

11

Re: Invasive and Non-Native Species

Lamium album  is listed on the NNSS web site http://www.nonnativespecies.org/factshe … iesId=1892 and is marked in the Organsim table as non-native.  So is Conium maculatum and Salix fragilis.

Petalophyllum ralfsii isn't listed on the site, but in the Organism table it is marked both non- native and also redundant.  Being  'Redundant'  suggests perhaps there has been something going on regarding this species, but I don't know what.

Mike Weideli
Littlefield Consultancy - IT Consultants

12

Re: Invasive and Non-Native Species

Thanks Mike, In that case your script and filter work a treat.
I just need to figure out what's going on with the taxonomy.

LERC Officer
Merseyside BioBank - Local Environmental Records Centre for North Merseyside
www.MerseysideBioBank.org.uk

13

Re: Invasive and Non-Native Species

I have asked Chris about  Petalophyllum ralfsii. It is unusual to have a species marked redundant.

Mike Weideli
Littlefield Consultancy - IT Consultants