1 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Missing designations (6.7.2.77)

Has anyone else lost certain designations? We didn't notice this until quite recently, so I'm not 100% sure that this problem was introduced with the 6.7.2.77 upgrade. What I'm seeing are species that are clearly missing important designations. For instance, the bats seem to be missing the W&C Act designations. You can try this out this quick test in your own databases by using the following SQL:

USE NBNData
SELECT      ITN.PREFERRED_NAME,
            ITN.COMMON_NAME,
            TDT.KIND,
            TDT.SHORT_NAME
FROM        INDEX_TAXON_NAME ITN
INNER JOIN  TAXON_DESIGNATION TD ON
            ITN.TAXON_LIST_ITEM_KEY = TD.TAXON_LIST_ITEM_KEY
INNER JOIN  TAXON_DESIGNATION_TYPE TDT ON
            TD.TAXON_DESIGNATION_TYPE_KEY = TDT.TAXON_DESIGNATION_TYPE_KEY
WHERE       ITN.COMMON_NAME LIKE '%bat'
OR          ITN.COMMON_NAME LIKE '%bats'
ORDER BY    ITN.COMMON_NAME

The results I get are as follows (apologies for the dodgy formatting):

PREFERRED_NAME    COMMON_NAME    KIND    SHORT_NAME
Barbastella barbastellus    Barbastelle bat    BAP    Priority Species
Barbastella barbastellus    Barbastelle bat    IUCN1994    IUCN (1994) - V
Barbastella barbastellus    Barbastelle bat    SxRSI    RSI
Barbastella barbastellus    Barbastelle bat    SxPSR    PSR
Barbastella barbastellus    Barbastelle bat    SxBAPSI    BAPSI
Chiroptera    Bats    SxRSI    RSI
Chiroptera    Bats    SxRSI    RSI
Chiroptera    Bats    SxPSR    PSR
Chiroptera    Bats    SxPSR    PSR
Vespertilionidae    Bats    Bonn    Bonn Convention Appendix 2
Chiroptera    Bats    WONI    Wildlife (N Ireland) Order Sch 6
Chiroptera    Bats    WONI    Wildlife (N Ireland) Order Sch 5
Chiroptera    Bats    WONI    Wildlife (N Ireland) Order Sch 7
Myotis bechsteinii    Bechstein's Bat    BAP    Priority Species
Myotis bechsteini    Bechstein's Bat    IUCN1994    IUCN (1994) - V
Myotis bechsteini    Bechstein's Bat    HSDir    Habitats and species directive Annex 2np
Myotis bechsteini    Bechstein's Bat    SxRSI    RSI
Myotis bechsteini    Bechstein's Bat    SxPSR    PSR
Myotis bechsteini    Bechstein's Bat    SxBAPSI    BAPSI
Myotis brandti    Brandt's Bat    SxRSI    RSI
Myotis brandti    Brandt's Bat    SxPSR    PSR
Plecotus auritus    Brown Long-eared Bat    SxPSR    PSR
Plecotus auritus    Brown Long-eared Bat    IUCN1994    RDB - Internationally Important
Myotis daubentoni    Daubenton's Bat    SxRSI    RSI
Myotis daubentoni    Daubenton's Bat    SxPSR    PSR
Myotis daubentoni    Daubenton's Bat    IUCN1994    RDB - Internationally Important
Rhinolophus ferrumequinum    Greater Horseshoe Bat    HSDir    Habitats and species directive Annex 2np
Rhinolophus ferrumequinum    Greater Horseshoe Bat    BAP    Priority Species
Rhinolophus ferrumequinum    Greater Horseshoe Bat    IUCN1994    IUCN (1994) - LR (cd)
Rhinolophus ferrumequinum    Greater Horseshoe Bat    SxRSI    RSI
Rhinolophus ferrumequinum    Greater Horseshoe Bat    SxPSR    PSR
Rhinolophus ferrumequinum    Greater Horseshoe Bat    SxBAPSI    BAPSI
Myotis myotis    Greater mouse-eared bat    SxRSI    RSI
Myotis myotis    Greater mouse-eared bat    SxPSR    PSR
Myotis myotis    Greater mouse-eared bat    BAP    Priority Species
Myotis myotis    Greater mouse-eared bat    IUCN1994    IUCN (1994) - LR (lc)
Myotis myotis    Greater mouse-eared bat    HSDir    Habitats and species directive Annex 2np
Plecotus austriacus    Grey Long-eared Bat    SxRSI    RSI
Rhinolophidae    Horseshoe Bats    ConReg    Conservation Regulations (1994) Sch2
Rhinolophidae    Horseshoe Bats    Bonn    Bonn Convention Appendix 2
Rhinolophidae    Horseshoe Bats    WCAct    Wildlife & Countryside Act Sch5 Sec 9.5b
Rhinolophidae    Horseshoe Bats    WCAct    Wildlife & Countryside Act Sch5 Sec 9.4b
Rhinolophidae    Horseshoe Bats    WCAct    Wildlife & Countryside Act Sch5 Sec 9.5a
Rhinolophidae    Horseshoe Bats    WCAct    Wildlife & Countryside Act Sch5 Sec 9.4a
Rhinolophidae    Horseshoe Bats    WCAct    Wildlife & Countryside Act Sch 5 Sec 9.2
Rhinolophidae    Horseshoe Bats    WCAct    W&CA Sch 5 Sec 9.1 (taking)
Rhinolophidae    Horseshoe Bats    WCAct    W&CA Sch 5 Sec 9.1 (killing/injuring)
Rhinolophidae    Horseshoe Bats    WCAct    Wildlife & Countryside Act Sch 6
Nyctalus leisleri    Leisler's Bat    IUCN1994    RDB - Internationally Important
Rhinolophus hipposideros    Lesser Horseshoe Bat    IUCN1994    RDB - Internationally Important
Rhinolophus hipposideros    Lesser Horseshoe Bat    HSDir    Habitats and species directive Annex 2np
Rhinolophus hipposideros    Lesser Horseshoe Bat    IUCN1994    IUCN (1994) - V
Rhinolophus hipposideros    Lesser Horseshoe Bat    BAP    Priority Species
Myotis nattereri    Natterer's Bat    IUCNold    RDB - Indeterm
Myotis nattereri    Natterer's Bat    SxRSI    RSI
Myotis nattereri    Natterer's Bat    SxPSR    PSR
Myotis emarginatus    Notch-eared Bat    HSDir    Habitats and species directive Annex 2np
Pipistrellus pipistrellus    Pipistrelle bat    Berne    Berne Convention Appendix 3
Myotis dasycneme    Pond Bat    HSDir    Habitats and species directive Annex 2np
Myotis mystacinus    Whiskered Bat    IUCNold    RDB - Indeterm
Myotis mystacinus    Whiskered Bat    SxRSI    RSI
Myotis mystacinus    Whiskered Bat    SxPSR    PSR

Note, only Rhinolophidae Horseshoe Bats (the family, not the species) appear to have any sort of W&C Act designation. Also, I found one of the Horseshoe Bats (again, the family not the species) with an AUTHORITY of 'DELETE'. This is TAXON_LIST_ITEM_KEY 'NBNSYS0000127159'.

Whatever is going on, something is certainly not right.

Charles

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

2 Reply by wilkin_s

  • wilkin_s
  • Administrator
  • Offline
  • Registered: 31-03-2006
  • Posts: 87

Re: Missing designations (6.7.2.77)

Charles
I have had a quick look at this. I dont think the problem is to do with the recent upgrade. Often species are designated at a higher taxonomic level (eg. all dolphins) and there needs to be a layer of interpretation to decide exactly what this means within the UK context. The problem will be addressed to some extent by JNCCs list of designations (one of the things I am looking at today). This actually explodes these sorts of designations to the species level which would make a more useful reporting product. We have the data for that and I am just working on the mechanism for publishing to the species dictionary and internal process for maintaining it. I also need to check whether this will actually help the situation with Recorder. eg. when doing this sort of designation reporting, if there was one definitive list with all the designations associated with each species, is it possible just to rely on that list?
In the meantime though, unless I have misunderstood the inner workings of Recorder I cant see how you could report down to the species level. (Unless - does index taxon Group help at all?)
Steve

3 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Missing designations (6.7.2.77)

Its a bit more complex than that, because the Rhinolophidae in question is on a list where it doesn't have any children, so you need to either use Index_Taxon_Synonym as well, or use nameserver translation as in the following example:

USE NBNData
SELECT DISTINCT
            ITN.Taxon_List_Item_Key, ITN.PREFERRED_NAME,
            ITN.COMMON_NAME,
            TDT.KIND,
            TDT.SHORT_NAME
FROM        INDEX_TAXON_NAME ITN
INNER JOIN  INDEX_TAXON_GROUP ITG ON 
            ITN.TAXON_LIST_ITEM_KEY=ITG.CONTAINED_LIST_ITEM_KEY
INNER JOIN  INDEX_TAXON_NAME ITNS1 
    ON ITG.TAXON_LIST_ITEM_KEY=ITNS1.TAXON_LIST_ITEM_KEY
INNER JOIN  INDEX_TAXON_NAME ITNS2 
    ON ITNS1.RECOMMENDED_TAXON_LIST_ITEM_KEY=ITNS2.RECOMMENDED_TAXON_LIST_ITEM_KEY
INNER JOIN  TAXON_DESIGNATION TD ON 
            ITNS2.TAXON_LIST_ITEM_KEY=TD.TAXON_LIST_ITEM_KEY
INNER JOIN  TAXON_DESIGNATION_TYPE TDT ON
            TD.TAXON_DESIGNATION_TYPE_KEY = TDT.TAXON_DESIGNATION_TYPE_KEY
WHERE       (ITN.COMMON_NAME LIKE '%bat'
OR          ITN.COMMON_NAME LIKE '%bats')
ORDER BY    ITN.COMMON_NAME

That returns quite a lot more data - I think its right!

John van Breda
Biodiverse IT

johnvanbreda's Website

4 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Missing designations (6.7.2.77)

John, that's excellent, it seems to work. It'll take me a while to wrap my head around exactly how it works (I'm going to fiddle around with it and work it out - I never quite feel comfortable with query unless I know exactly what it's doing), but knowing that it looks like nothing is missing is a great relief. Once I've worked out what's going on here I'll probably write it up on the wiki.

One question still remains, though: why the AUTHORITY entry of 'DELETE'?

Charles

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

5 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Missing designations (6.7.2.77)

Perhaps a solution to difficulties with designations would be the creation of a designation index that would pull all designations from wherever they may be together into one linking table? This would help improve performance and simplify the querying process and be a reliable way of getting at designations.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

6 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Missing designations (6.7.2.77)

An alternative, which would simplify queries but would not improve performance, would be to add a view to the database which joins taxon list items to their designations.  This way there would be no maintenance problems (no need to update the index etc).  It would also be incredibly simple to implement, just requiring a query in a CREATE VIEW statement and no code changes in Recorder.

One idea might be for someone (doesn't have to be Dorset Software) to compile a list of useful views and create the SQL for them.  They could then be deployed in a future upgrade and will make life simpler for those writing custom reports.

John van Breda
Biodiverse IT

johnvanbreda's Website

7 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Missing designations (6.7.2.77)

Views - great idea! I'll do some performance testing to see how it goes.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

8 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Missing designations (6.7.2.77)

There are 2 types of view, standard and schema bound.  If you use a standard view then the performance (in my experience) is likely to be exactly the same as if you wrote the query without the view.  Its really just a 'shortcut' to help tidy SQL.

If you use schema bound views then you can index the view - this slows down updates but could enhance performance.  The problem is with schema bound views is that they impose restrictions on the SET options (such as ARITHABORT) on the stored procedures that write to the underlying tables.  In simple terms, adding schema bound views can break existing code.

But, just from the point of view of making the data model much more usable, a collection of views that simplify the more complex parts of the model would be great.  You could also add a script that dumps the contents of the views out into a data warehouse (snapshot) for reporting or web servers very easily.

John van Breda
Biodiverse IT

johnvanbreda's Website

9 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Missing designations (6.7.2.77)

Are schema bound views the same as indexed views? If that's the case, doesn't the index only get used in the Enterprise Edition of SQL Server? So you'd get the UPDATE performance hit, but none of the performance benefit of the index.

I do actually have a seperate NBNReporting database setup that I select into. I connect to this using our GIS - works really well.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

10 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Missing designations (6.7.2.77)

Being schema bound is a pre-requisite of being indexed.  Schema binding means that the view becomes part of the main database schema, so that you can't do anything to the underlying tables that will break the view, such as dropping a table or removing columns.  Once a view is schema bound you are able to add indexes to it within certain limits.  The indexes can be used on any SQL Server version including MSDE, but ONLY if the correct SET options are enabled.  In fact the indexes generated can be used to optimise other queries, even those that don't reference the view.

But, as I said before, indexed views aren't a good idea unless you start off with them in the initial design otherwise they break a lot of code.  There are views for searching the Thesaurus that are indexed but they were designed in from the start.

John van Breda
Biodiverse IT

johnvanbreda's Website