Recording all plants growing wild in Somerset, not just the rarities!

Somerset Record and Taxon Count per 1Km Square

Spring 2017 Update

These spreadsheets were created from MapMate records made since 2000 and should include the bulk of records from 2016. The flashing animation shows the progress since April 2016.

Click to animate


The colours have been changed to give a better gradation of colours (in my opinion!), the underlying data is the same. The key is below, it is also in the spreadsheets and is the same for both records and taxa. Note that the zero-count squares and the 1-19 squares are boxed for emphasis.


  Displaying the Data

Three Excel spreadsheets have been created:

  1. The number of records in each 1km square
  2. The number of different taxa recorded in each 1km square
  3. Each 1km square showing the percentage within Somerset i.e. 100% for most and varying percentages for border squares.

These counts cover VC5 and VC6, which for the sake of simplicity is referred to as “Somerset”, but note that the combined administrative counties of Somerset, North Somerset and Bath and North East Somerset (BANES) do not have exactly the same borders as the vice-counties.

These have been restricted to the years 2000-2019 inclusive, but any desired period can be extracted on request.

The spreadsheets are best viewed by using the scale control (bottom left of Excel screen) to adjust the scale. At 30% most of the county is visible (although this will vary from machine to machine), but a larger scale will be needed to read the numbers.

While every effort has been made to ensure that these reflect the records in MapMate there is always the potential for errors. Any feedback on the data, the method or suggestions for improvements would be welcomed.


The method was kindly provided by Brian Pitkin of Surrey Botanical Society. The Surrey analysis is available in the Records section of the society’s website, Any errors in the data or presentation are of course my own.

The method relies on a list of 1km squares originally sourced from the Biological Records Centre VC Grid Squares request page. This data was also provided by Brian Pitkin as a map-like spreadsheet, showing the percentage of each square contained within VC 5 and VC 6. Note that the extent of each vice-county extends to low water mark, which accounts for the large coastal expanse of red (zero-count) squares, particularly around the mouth of the Parrett. In addition, the Herbaria United OS Grid Reference to Vice-County converter was found to be very useful in checking grid references against the VC boundaries.

The original method relied on the use of Access97 (the underlying database software used by MapMate). As this is no longer widely available, and the Access97 files are not accessible from newer versions of Access, the decision was taken to export all the record, site and taxon data from MapMate and then process it in Access 2016. This involved creating MapMate User Queries to display the records, sites and taxa and then saving them as text files. These were used as source data to create tables in Access2016.

From that point, the SQL queries provided by Brian Pitkin were used successfully to generate the required data. The counts were copied into the spreadsheet, also provided by Brian.

The record and taxon counts were exported from Access and copied into an Excel spreadsheet. Conditional Formatting has been used to colour each square according to its value. Again, this was designed by Brian Pitkin although we have added an additional colour to represent squares with more than 300 records or taxa.

Known Issues

  1. This process identified some data problems, which have been notified to Helena Crouch:
  2. Some records have been assigned to the wrong vice-county. e.g. SWT’s Ringdown reserve which is in VC3 but is inside the administrative county of Somerset. These errors do not directly affect the analysis. An additional check was added to exclude results falling outside VC5 and VC6.
  3. Some records, originally recorded with a 10-figure (1m precision) grid reference, have been truncated to an 8-figure reference (by removing the last 2 digits) and thus creating an invalid grid reference. The Xpos, Ypos fields in the Sites table do not appear to be affected by this truncation. Therefore, Xpos, Ypos have been used to obtain the grid square numbers rather than OSGridRef.
  4. There are two sites with an OSGridRef = “Unknown”. These have been excluded by using a more detailed check of the format of OSGridRef.
  5. The data represents the contents of MapMate as synchronised with Helena Crouch. It is understood that some records from 2015 have not yet been synchronised to Helena’s database.

Val Graham

Previous Posts