1 Topic by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Topic: Native plants query

Hi, I am trying to pull out a list of native plant species and seem to have got something wrong. I thought I could use the non-native flag in the Organism table but seem to have got species like Californian Fuchsia and Caucasian bladdernut included. Any suggestions?

SELECT     ORGANISM.TAXON_VERSION_KEY, ORGANISM.NON_NATIVE_FLAG, TAXON_RANK.SHORT_NAME, INDEX_TAXON_NAME.ACTUAL_NAME, 
                      INDEX_TAXON_NAME.COMMON_NAME, TAXON_GROUP.TAXON_GROUP_NAME
FROM         TAXON_RANK INNER JOIN
                      TAXON_LIST_ITEM INNER JOIN
                      ORGANISM ON TAXON_LIST_ITEM.TAXON_VERSION_KEY = ORGANISM.TAXON_VERSION_KEY ON 
                      TAXON_RANK.TAXON_RANK_KEY = TAXON_LIST_ITEM.TAXON_RANK_KEY INNER JOIN
                      INDEX_TAXON_NAME ON TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY = INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY INNER JOIN
                      TAXON_VERSION ON TAXON_LIST_ITEM.TAXON_VERSION_KEY = TAXON_VERSION.TAXON_VERSION_KEY INNER JOIN
                      TAXON_GROUP ON TAXON_VERSION.OUTPUT_GROUP_KEY = TAXON_GROUP.TAXON_GROUP_KEY
WHERE     (ORGANISM.NON_NATIVE_FLAG IS NULL) AND (TAXON_RANK.SHORT_NAME = 'Spp')and (TAXON_GROUP.TAXON_GROUP_NAME like 'flo%')

Alternatively if there is a nice list somewhere anyone knows about that doesn't need a query, I would be very happy to have that. The BSBI database won't let me do a wildcard query.

Thanks

Gordon Barker
Biological Survey Data Manager
National Trust

2 Reply by sallyrankin

  • sallyrankin
  • Administrator
  • Offline
  • Registered: 07-04-2006
  • Posts: 371

Re: Native plants query

The following query shows that the NON_NATIVE_FLAG has only been populated for 3,320 taxa in dictionary version 0000001W, none of which are from the VASCULAR PLANTS AND STONEWORTS BSBI List. Are you using the latest version of the dictionary? If so, it may be worth checking whether it has been populated it that but if it hasn’t, using this flag isn’t going to work for your purposes. If anyone has a rucksack of native species you could use that for your report.

SELECT ORGANISM.ORGANISM_KEY, ORGANISM.PARENT_KEY, ORGANISM.TAXON_VERSION_KEY, ORGANISM.NON_NATIVE_FLAG, INDEX_TAXON_NAME.PREFERRED_NAME, INDEX_TAXON_NAME.COMMON_NAME, TAXON_LIST.ITEM_NAME
FROM ORGANISM
INNER JOIN TAXON_LIST_ITEM ON ORGANISM.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.RECOMMENDED_TAXON_LIST_ITEM_KEY
INNER JOIN TAXON_LIST_VERSION ON INDEX_TAXON_NAME.TAXON_LIST_VERSION_KEY = TAXON_LIST_VERSION.TAXON_LIST_VERSION_KEY
INNER JOIN TAXON_LIST ON TAXON_LIST_VERSION.TAXON_LIST_KEY = TAXON_LIST.TAXON_LIST_KEY
WHERE (ORGANISM.NON_NATIVE_FLAG IS NOT NULL)
ORDER BY TAXON_LIST.ITEM_NAME

Sally Rankin, JNCC Recorder Approved Expert
E-mail: s.rankin@btinternet.com
Telephone: 01491 578633
Mobile: 07941 207687