Re: Wrong count of 2km squares if a 10km record exists in the output.
I’ve written an XML report to count the number of occupied 10km squares and 2km squares per family/species for a status review of macro-moths of Great Britain excluding Northern Ireland and the Isle of Man between 2000 and 2010.
Example:
I select and insert into a temporary table the species name and 10km square, all records for a single species or RTVK. I then create another temporary table to select and insert the unique 10km records per species. I then create one final temporary table to count the number of unique 10km squares per species.
I perform the same as above but for 2km squares.
I now have 2 final temporary tables, one with a 10km count per species, one with a 2km count per species.
I then collate these counts for a species/RTVK into a final, single output table headed Taxon, Count of 10km, Count of 2km.
This report generally works very well and is a huge timesaver!
Regretfully, there appears to be a bug if a record is only recorded to 10km. The 10km count will be correct but the 2km count will add 1 to the count for every unique 10km square even though it clearly has no 2km reference.
Take the following example records:
Large Yellow Underwing SY7777
Large Yellow Underwing SY8888
Large Yellow Underwing SY9999
Large Yellow Underwing SY99
Large Yellow Underwing SZ00
What I should get is:
Taxon, Count of 10km, Count of 2km
Large Yellow Underwing, 4, 3
The report output gives me:
Taxon, Count of 10km, Count of 2km
Large Yellow Underwing, 4, 5
If all records were recorded at 2km resolution or greater there would not be a problem as all counts would be correct.
To further test the results I’ve split the report into two i.e. 10km report and 2km report. Running the 2km report produces the same result i.e. the wrong count of 5.
To resolve the 2km grid references I use the function LCRectifyGR().
The example code is for a family; however, the results will still remain the same for a single species i.e. if there is a record at 10km only it will add 1 to the 2km count for each unique 10km record.
I hope this all makes sense!
<?xml version="1.0" ?>
<!--
NMRS Macro-moth Status Review 2012
XML Report by Les Hill of Butterfly Conservation
http://www.butterfly-conservation.org/
-->
<CustomReport
title="NMRS Macro-moth Status Review 2012 - GB excl. Isle of Man - Cossidae"
menupath="Macro-moth Status Review"
description="Cossidae - Counts the number of occupied 10km and 2km squares in Great Britain (excl. Isle of Man) from 2000 to 2010."
>
<SQL>
<Where keytype="Default">
/*---------------------------------------------------------*\
Create a table in which all the 10km output will insert into.
\*---------------------------------------------------------*/
CREATE TABLE #Data_Dump_10km(
COMMON_TAXON varchar(64) COLLATE Database_Default
,SAMPLE_10KM varchar(4) COLLATE Database_Default
)
INSERT INTO #Data_Dump_10km(
COMMON_TAXON
,SAMPLE_10KM
)
/*------------------------------------------------------*\
Select the data and insert into the #Data_Dump_10km table.
\*------------------------------------------------------*/
SELECT DISTINCT
ITN.COMMON_NAME AS [Taxon],
dbo.LCRectifyGR(Sample.SPATIAL_REF, Sample.SPATIAL_REF_SYSTEM, 0) AS [Gridref10km]
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 Sample_Admin_Areas SAA ON SAA.Sample_Key = Sample.Sample_Key
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 Admin_Area ASam ON ASam.Admin_Area_Key = SAA.Admin_Area_Key
LEFT JOIN Location LSurv ON Survey_Event.Location_Key = LSurv.Location_Key
LEFT JOIN Survey ON Survey.Survey_Key = Survey_Event.Survey_Key
LEFT JOIN Location_Admin_Areas LAA ON LAA.Location_Key = LSurv.Location_Key
LEFT JOIN Index_Taxon_Name ITN2 ON ITN.Recommended_Taxon_List_Item_Key=ITN2.Recommended_Taxon_List_Item_Key
LEFT JOIN Admin_Area ALOC ON ALOC.Admin_Area_Key = LAA.Admin_Area_Key
LEFT JOIN Index_Taxon_Group ITG2 ON ITG2.Contained_List_Item_Key = ITN2.Taxon_List_Item_Key
LEFT JOIN Index_Taxon_Name ITN4 ON ITN4.Taxon_List_Item_Key=ITG2.Taxon_List_Item_Key
LEFT JOIN Index_Taxon_Name ITN5 ON ITN5.Recommended_Taxon_List_Item_Key = ITN4.Recommended_Taxon_List_Item_Key
WHERE Survey.Survey_Key NOT IN (SELECT Survey_Key FROM User_Survey_Restriction USR WHERE USR.Name_Key = 'LC00033400000004')
AND ((ALoc.Admin_Area_Key NOT IN ('NBNSYS0000000940')) OR (ASam.Admin_Area_Key NOT IN ('NBNSYS0000000940')))
AND ITN5.Taxon_List_Item_Key IN ('NHMSYS0000496564')
AND Sample.SPATIAL_REF_SYSTEM = 'OSGB'
AND Taxon_Determination.Preferred = 1
AND Taxon_Occurrence.Zero_Abundance = 0
AND Taxon_Occurrence.Verified <> 1
AND Sample.VAGUE_DATE_START > 36525
AND Sample.VAGUE_DATE_END < 40544
ORDER BY
ITN.COMMON_NAME,
dbo.LCRectifyGR(Sample.SPATIAL_REF, Sample.SPATIAL_REF_SYSTEM, 0)
/*-----------------------------------------------------*\
Create a new table in which to insert distinct 10km data.
\*-----------------------------------------------------*/
CREATE TABLE #New_Data_10km(
COMMON_TAXON varchar(64) COLLATE Database_Default
,SAMPLE_10KM varchar(4) COLLATE Database_Default
)
INSERT INTO #New_Data_10km(
COMMON_TAXON
,SAMPLE_10KM
)
/*---------------------------------------------------------*\
Select the new data and insert into the #New_Data_10km table.
\*---------------------------------------------------------*/
SELECT DISTINCT COMMON_TAXON AS [Taxon], SAMPLE_10KM AS [Gridref10km]
FROM #Data_Dump_10km
ORDER BY COMMON_TAXON, SAMPLE_10KM
/*--------------------------------------------------*\
Create a new table in which to insert 10km count data.
\*--------------------------------------------------*/
CREATE TABLE #New_Data2_10km(
COMMON_TAXON varchar(64) COLLATE Database_Default
,SAMPLE_10KM varchar(4) COLLATE Database_Default
)
INSERT INTO #New_Data2_10km(
COMMON_TAXON
,SAMPLE_10KM
)
/*----------------------------------------------------*\
Select and count new 10km data from the temporary table.
\*----------------------------------------------------*/
SELECT COMMON_TAXON AS [Taxon], Count(SAMPLE_10KM) AS [Gridref10km]
FROM #New_Data_10km
GROUP BY COMMON_TAXON
ORDER BY COMMON_TAXON
/*--------------------------------------------------------*\
Create a table in which all the 2km output will insert into.
\*--------------------------------------------------------*/
CREATE TABLE #Data_Dump_2km(
COMMON_TAXON varchar(64) COLLATE Database_Default
,SAMPLE_2KM varchar(5) COLLATE Database_Default
)
INSERT INTO #Data_Dump_2km(
COMMON_TAXON
,SAMPLE_2KM
)
/*------------------------------------------------------\
Select the data and insert into the #Data_Dump_2km table.
\*-----------------------------------------------------*/
SELECT DISTINCT
ITN.COMMON_NAME AS [Taxon],
dbo.LCRectifyGR(Sample.SPATIAL_REF, Sample.SPATIAL_REF_SYSTEM, 2) AS [Gridref2km]
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 Sample_Admin_Areas SAA ON SAA.Sample_Key = Sample.Sample_Key
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 Admin_Area ASam ON ASam.Admin_Area_Key = SAA.Admin_Area_Key
LEFT JOIN Location LSurv ON Survey_Event.Location_Key = LSurv.Location_Key
LEFT JOIN Survey ON Survey.Survey_Key = Survey_Event.Survey_Key
LEFT JOIN Location_Admin_Areas LAA ON LAA.Location_Key = LSurv.Location_Key
LEFT JOIN Index_Taxon_Name ITN2 ON ITN.Recommended_Taxon_List_Item_Key=ITN2.Recommended_Taxon_List_Item_Key
LEFT JOIN Admin_Area ALOC ON ALOC.Admin_Area_Key = LAA.Admin_Area_Key
LEFT JOIN Index_Taxon_Group ITG2 ON ITG2.Contained_List_Item_Key = ITN2.Taxon_List_Item_Key
LEFT JOIN Index_Taxon_Name ITN4 ON ITN4.Taxon_List_Item_Key=ITG2.Taxon_List_Item_Key
LEFT JOIN Index_Taxon_Name ITN5 ON ITN5.Recommended_Taxon_List_Item_Key = ITN4.Recommended_Taxon_List_Item_Key
WHERE Survey.Survey_Key NOT IN (SELECT Survey_Key FROM User_Survey_Restriction USR WHERE USR.Name_Key = 'LC00033400000004')
AND ((ALoc.Admin_Area_Key NOT IN ('NBNSYS0000000940')) OR (ASam.Admin_Area_Key NOT IN ('NBNSYS0000000940')))
AND ITN5.Taxon_List_Item_Key IN ('NHMSYS0000496564')
AND Sample.SPATIAL_REF_SYSTEM = 'OSGB'
AND Taxon_Determination.Preferred = 1
AND Taxon_Occurrence.Zero_Abundance = 0
AND Taxon_Occurrence.Verified <> 1
AND Sample.VAGUE_DATE_START > 36525
AND Sample.VAGUE_DATE_END < 40544
ORDER BY
ITN.COMMON_NAME,
dbo.LCRectifyGR(Sample.SPATIAL_REF, Sample.SPATIAL_REF_SYSTEM, 2)
/*------------------------------------------------*\
Create a new table in which to insert distinct data.
\*------------------------------------------------*/
CREATE TABLE #New_Data_2km(
COMMON_TAXON varchar(64) COLLATE Database_Default
,SAMPLE_2KM varchar(5) COLLATE Database_Default
)
INSERT INTO #New_Data_2km(
COMMON_TAXON
,SAMPLE_2KM
)
/*--------------------------------------------------------*\
Select the new data and insert into the #New_Data_2km table.
\*--------------------------------------------------------*/
SELECT DISTINCT COMMON_TAXON AS [Taxon], SAMPLE_2KM AS [Gridref2km]
FROM #Data_Dump_2km
ORDER BY COMMON_TAXON, SAMPLE_2KM
/*-------------------------------------------------*\
Create a new table in which to insert 2km count data.
\*-------------------------------------------------*/
CREATE TABLE #New_Data2_2km(
COMMON_TAXON varchar(64) COLLATE Database_Default
,SAMPLE_2KM varchar(5) COLLATE Database_Default
)
INSERT INTO #New_Data2_2km(
COMMON_TAXON
,SAMPLE_2KM
)
/*---------------------------------------------------*\
Select and count new 2km data from the temporary table.
\*---------------------------------------------------*/
SELECT COMMON_TAXON AS [Taxon], Count(SAMPLE_2KM) AS [Gridref2km]
FROM #New_Data_2km
GROUP BY COMMON_TAXON
ORDER BY COMMON_TAXON
/*--------------------------------------------------------*\
Combine the 10km and 2km outputs into a single output table.
\*--------------------------------------------------------*/
SELECT #New_Data2_10km.COMMON_TAXON AS [Taxon], #New_Data2_10km.SAMPLE_10KM AS [Gridref10km], #New_Data2_2km.SAMPLE_2KM AS [Gridref2km]
FROM #New_Data2_10km LEFT JOIN #New_Data2_2km ON #New_Data2_10km.COMMON_TAXON = #New_Data2_2km.COMMON_TAXON
/*------------------------*\
Delete the temporary tables.
\*------------------------*/
DROP TABLE #New_Data2_10km
DROP TABLE #New_Data_10km
DROP TABLE #Data_Dump_10km
DROP TABLE #New_Data2_2km
DROP TABLE #New_Data_2km
DROP TABLE #Data_Dump_2km
</Where>
</SQL>
<Columns>
<Column name="Taxon" position="1" caption="Taxon" width="150"/>
<Column name="Gridref10km" position="2" caption="No. of 10km Squares" width="100"/>
<Column name="Gridref2km" position="3" caption="No. of 2km Squares" width="100"/>
</Columns>
</CustomReport>Les Evans-Hill
Senior Data Officer, National Moth Recording Scheme
Senior Data Officer, National Moth Recording Scheme