1 Reply by Gordon Barker

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

Re: Help with XML reports

Hi

I am trying to work out how to write a couple of reports. I would like to know:

a) how many species (or other taxa) within each taxon group there are records for, across the whole dataset;
b) how many species ( "" ) within each taxon group there are records for, at a location;
c) what level these taxa have been recorded at?

E.g. I already know that at the location Hatfield Forest I have records for 3133 taxa, but I want to know that of these 500 are fungi, 200 beetles etc. Also that of the 3133, 2500 are at species level, 400 sub-species, 200 genus, 30 family...

Ideas I have had so far:

for c) it should be possible to amend the Database Statistics - Number of Observations per Species report to link in to the TAXON_RANK table the TAXON LIST KEY table links to it with the TAXON RANK KEY attribute but I am not sure how to write the join - I was thinking of using the Number of Observations per Species v2 report (on the uploads page, which I amended from the original to remove duplication across lists) but was wondering if there might be a problem if a sub-species on one list equated to a species on another;

for a) it might work again with amending Database Statistics - Number of Observations per Species, linking to TAXON_GROUP but I can't work out the link;

for b) it could involve integrating the solution for b) into the Site Reports - Location Summary Report V06-12.

However this has all made my head hurt. I would be very grateful if anyone could let me know if I am heading in the right direction, and supply any further pointers.

Thanks

Gordon

Gordon Barker
Biological Survey Data Manager
National Trust

2 Reply by johnvanbreda (edited by johnvanbreda 26-03-2010 09:24:27)

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

Re: Help with XML reports

Hi Gordon,
Here's a query that should help you on the way. It lists the orders in your database, with the number of species recorded for each. You can then add further joins to make it filter by location etc.

SELECT ITNOrd.Preferred_Name, COUNT(DISTINCT ITNRec.Recommended_Taxon_List_Item_Key)
FROM 
-- Start with the taxon list items for the Order
Taxon_List_Item TLIord
-- Use ITN to get the preferred name for each Order
INNER JOIN Index_Taxon_Name ITNOrd ON ITNOrd.Taxon_List_Item_Key=TLIord.Taxon_List_Item_Key
-- Use ITG to find all the children of each order (at any level)
INNER JOIN Index_Taxon_Group ITG ON ITG.Taxon_List_Item_Key=ITNOrd.Taxon_List_Item_Key
-- Join to Taxon Determination to find just the actual recorded names.
INNER JOIN Taxon_Determination TD 
    ON TD.Taxon_List_Item_Key = ITG.Contained_List_Item_Key
    AND TD.Preferred=1
-- Join to Index Taxon Name to get the recommended keys of the records.
INNER JOIN Index_Taxon_Name ITNRec ON ITNRec.Taxon_List_Item_Key=TD.Taxon_List_Item_Key
WHERE TLIord.Taxon_Rank_Key='NBNSYS0000000012' -- Order
GROUP BY ITNOrd.Preferred_Name

Hope that helps,

John van Breda
Biodiverse IT

johnvanbreda's Website