Surrey’s Vascular Plants and Charophytes: Analysis of the Number of Records and Taxa per 1km Square (monad) by Date Class (Year range)

Skip to Records Analysis Spread Sheet

Skip to Taxa Analysis Spread Sheet

Surrey covers 2,102 monads, including 1,833 complete monads, 137 with 50% to 99% coverage and 132 with 1% to 49% coverage.

The number of records and taxa of Vascular Plants and Charophytes accumulated by the Surrey Botanical Society and stored in MapMate as at 1st September 2011 stood at 285,037

An analysis of all these records revealed monads without records. A list of monads and the percentage of each 1km square in Surrey was copied from the Biological Records Centre and pasted into a MS Access table. This table was then merged with the list of Surrey 1km Squares from MapMate.



The 1km Squares straddling the VC Boundary were checked against an OS Map with a UK OS grid and a Surrey VC boundary overlay developed by Robin Day from code originally provided by Bill Chadwick, to produce a 'map'.

The majority of records (c.96%) are at a sufficient precision to enable analysis at the monad (1km Square) level (see Appendix 1)



The analyses for each Date Class and all years were carried out in MS Access using queries developed for the purpose, with assistance from David Williams (see Appendix 2 for records & Appendix 3 for taxa).

Date Class	Year Range	No. of Records	No. of Taxa	Cumulative No. of Taxa
DC 0	Pre 1929	673	323	323
DC 1	1930-1969	7,267	1,024	1,097
DC 2	1970-1986	13,407	1,412	1,548
DC 3	1987-1999	16,997	2,058	2,150
DC 4	2000-2009	147,375	1,984	2,421
DC 5	2010-2019	46,305	1,426	2,473

The resulting Crosstab tables were first sorted in descending order and then copied and pasted into an Excel spread sheet prior to reformatting the data to produce a 'map' for each Date Class, for all records click HERE and taxa click HERE.



Acknowledgements
I am extremely grateful to David Williams, Robin Day and Peter Wakeham for invaluable assistance and Bill Chadwick who provided the code for SBS use which Robin adapted.
Brian Pitkin, 21 September 2011

Skip to Records Analysis Spread Sheet

Skip to Taxa Analysis Spread Sheet

Appendix 1

Length of OSGridRef	OSGridRef Type	Number of OSGridRefs	Subtotals & Totals	Percentage of Squares
0	Blank	23		0.008
4	Hectads - 10km Square	1959		0.687
5	Tetrads - 2km Square	8389		2.943
Subtotal			10371	3.638
6	Monads - 1km Square	34308		12.0363
8	100m Square	233877		82.051
10	10m Square	6388		2.241
12	1m Square	93		0.032
Subtotal			274666	96.361
Totals			285037	100

Appendix 2 - Queries to generate a table of the number of records per 1km Square.

Note: To run these queries you will first need to create a table of 1kSquareList by downloading the County list from the Biological Records Centre. Queries must be run in the order listed below. Text in bold is the name of a query and must match exactly character for character. Copy and paste the text into a separate MS Access query.

Count records by 1kSquares for all years

SELECT DISTINCT [1kSquareList].[1kSquare], [1kSquareList].X, [1kSquareList].Y, Count(Records.[_guk]) AS Totals, Year([Date]) AS Year

FROM ((Records INNER JOIN Sites ON Records.[*Site] = Sites.[_guk]) INNER JOIN 1kSquareList ON Sites.[_guk] = [1kSquareList].[_guk]) INNER JOIN Taxa ON Records.[*Taxon] = Taxa.[_guk]

GROUP BY [1kSquareList].[1kSquare], [1kSquareList].X, [1kSquareList].Y, Records.Date;

Count records per 1kSquare for year range

SELECT DISTINCT [Count records by 1kSquares for all years].[1kSquare], [Count records by 1kSquares for all years].X, [Count records by 1kSquares for all years].Y, Sum([Count records by 1kSquares for all years].Totals) AS Totals INTO [Count records per 1kSquare for years selected - Table]

FROM [Count records by 1kSquares for all years]

WHERE ((([Count records by 1kSquares for all years].Year)>=[Start year] And ([Count records by 1kSquares for all years].Year)<=[End year]) AND (([Count records by 1kSquares for all years].Y)>129 And ([Count records by 1kSquares for all years].Y)<182) AND (([Count records by 1kSquares for all years].X)>479 And ([Count records by 1kSquares for all years].X)<545))

GROUP BY [Count records by 1kSquares for all years].[1kSquare], [Count records by 1kSquares for all years].X, [Count records by 1kSquares for all years].Y;

Note The >479 and <545 (where the leading 4=SU and the leading 5=TQ in the 'maps') and >129 and <182 (where the leading 1 is ignored in the 'maps') are the absolute limits for Surrey and would need to be modified for other VC's to ensure that no erroneous MapMate records are included

Comparison of records

SELECT DISTINCT [All 1kSquares including those with no records].[1kSquare], [All 1kSquares including those with no records].X, [All 1kSquares including those with no records].Y, [All 1kSquares including those with no records].Totals AS Totals INTO [Add these Surrey 1kSquares]

FROM [Count records per 1kSquare for years selected - Table] RIGHT JOIN [All 1kSquares including those with no records] ON [Count records per 1kSquare for years selected - Table].[1kSquare] = [All 1kSquares including those with no records].[1kSquare]

WHERE ((([Count records per 1kSquare for years selected - Table].[1kSquare]) Is Null) AND (([Count records per 1kSquare for years selected - Table].X) Is Null) AND (([Count records per 1kSquare for years selected - Table].Y) Is Null) AND (([Count records per 1kSquare for years selected - Table].Totals) Is Null));

Update - Add these Surrey 1kSquares

UPDATE [Add these Surrey 1kSquares] SET [Add these Surrey 1kSquares].Totals = 0;

Append missing 1k Squares to Record Count

INSERT INTO [Count records per 1kSquare for years selected - Table] ( 1kSquare, X, Y, Totals )

SELECT [Add these Surrey 1kSquares_1].[1kSquare], [Add these Surrey 1kSquares_1].X, [Add these Surrey 1kSquares_1].Y, [Add these Surrey 1kSquares_1].Totals

FROM [Add these Surrey 1kSquares] AS [Add these Surrey 1kSquares_1];

Count records per 1kSquare for years selected - Crosstab

TRANSFORM Sum([Count records per 1kSquare for years selected - Table].Totals) AS [The Value]

SELECT [Count records per 1kSquare for years selected - Table].Y

FROM [Count records per 1kSquare for years selected - Table]

GROUP BY [Count records per 1kSquare for years selected - Table].Y

PIVOT [Count records per 1kSquare for years selected - Table].X;



Appendix 3 - Queries to generate a table of the number of taxa per 1km Square.

Note: To run these queries you will first need to create a table of 1kSquareList by downloading the County list from the Biological Records Centre. Queries must be run in the order listed below. Text in bold is the name of a query and must match exactly character for character. Copy and paste the text into a separate MS Access query.

Count taxa by 1kSquares for all years

SELECT DISTINCT [1kSquareList].[1kSquare], [1kSquareList].X, [1kSquareList].Y, Taxa.Taxon, Year([Date]) AS Year

FROM ((Sites INNER JOIN 1kSquareList ON Sites.[_guk] = [1kSquareList].[_guk]) INNER JOIN Records ON Sites.[_guk] = Records.[*Site]) INNER JOIN Taxa ON Records.[*Taxon] = Taxa.[_guk]

GROUP BY [1kSquareList].[1kSquare], [1kSquareList].X, [1kSquareList].Y, Taxa.Taxon, Records.Date;

Count taxa per 1kSquare for range selected

SELECT DISTINCT [Count taxa by 1kSquares for all years].[1kSquare], [Count taxa by 1kSquares for all years].X, [Count taxa by 1kSquares for all years].Y, [Count taxa by 1kSquares for all years].Taxon INTO [Count taxa per 1kSquare for years selected - Table]

FROM [Count taxa by 1kSquares for all years]

WHERE ((([Count taxa by 1kSquares for all years].Year)>=[Start year] And ([Count taxa by 1kSquares for all years].Year)<=[End year]) AND (([Count taxa by 1kSquares for all years].Y)>129 And ([Count taxa by 1kSquares for all years].Y)<182) AND (([Count taxa by 1kSquares for all years].X)>479 And ([Count taxa by 1kSquares for all years].X)<545))

GROUP BY [Count taxa by 1kSquares for all years].[1kSquare], [Count taxa by 1kSquares for all years].X, [Count taxa by 1kSquares for all years].Y, [Count taxa by 1kSquares for all years].Taxon

HAVING ((([Count taxa by 1kSquares for all years].Taxon) Is Not Null));

Count taxa per 1kSquare for range selected 2

SELECT [Count taxa per 1kSquare for years selected - Table].[1kSquare], [Count taxa per 1kSquare for years selected - Table].X, [Count taxa per 1kSquare for years selected - Table].Y, Count([Count taxa per 1kSquare for years selected - Table].Taxon) AS CountOfTaxon INTO [Count taxa per 1kSquare for years selected - Table 2]

FROM [Count taxa per 1kSquare for years selected - Table]

GROUP BY [Count taxa per 1kSquare for years selected - Table].[1kSquare], [Count taxa per 1kSquare for years selected - Table].X, [Count taxa per 1kSquare for years selected - Table].Y;

Comparison of taxa

SELECT DISTINCT [All 1kSquares including those with no records].[1kSquare], [All 1kSquares including those with no records].X, [All 1kSquares including those with no records].Y, [All 1kSquares including those with no records].Totals AS Totals INTO [Add these Surrey 1kSquares]

FROM [Count taxa per 1kSquare for years selected - Table] RIGHT JOIN [All 1kSquares including those with no records] ON [Count taxa per 1kSquare for years selected - Table].[1kSquare] = [All 1kSquares including those with no records].[1kSquare]

WHERE ((([Count taxa per 1kSquare for years selected - Table].[1kSquare]) Is Null) AND (([Count taxa per 1kSquare for years selected - Table].X) Is Null) AND (([Count taxa per 1kSquare for years selected - Table].Y) Is Null));

Update - Add these Surrey 1kSquares

UPDATE [Add these Surrey 1kSquares] SET [Add these Surrey 1kSquares].Totals = 0;

Append missing 1k Squares to Taxa Count

INSERT INTO [Count taxa per 1kSquare for years selected - Table 2] ( 1kSquare, X, Y, CountOfTaxon )

SELECT [Add these Surrey 1kSquares].[1kSquare], [Add these Surrey 1kSquares].X, [Add these Surrey 1kSquares].Y, [Add these Surrey 1kSquares].Totals

FROM [Add these Surrey 1kSquares]

GROUP BY [Add these Surrey 1kSquares].[1kSquare], [Add these Surrey 1kSquares].X, [Add these Surrey 1kSquares].Y, [Add these Surrey 1kSquares].Totals;

Count taxa per 1kSquare for years selected - Table_Crosstab

TRANSFORM Sum([Count taxa per 1kSquare for years selected - Table 2].CountOfTaxon) AS [The Value]

SELECT [Count taxa per 1kSquare for years selected - Table 2].Y

FROM [Count taxa per 1kSquare for years selected - Table 2]

GROUP BY [Count taxa per 1kSquare for years selected - Table 2].Y

PIVOT [Count taxa per 1kSquare for years selected - Table 2].X;

© Surrey Botanical Society 2007 - 2012