Topic: Additional analysis queries for MapMate

If you use MapMate you will know that it has the facility for SQL queries to be written and shared among users. The rather long message below describes some queries that I've compiled to add extra details to the standard "Browse All Records" query - these additions include fields for national status and BAP listing, and also the coordinates to facilitate export to GIS.

If you use MapMate you will probably also be a member of the MapMate-users egroup, in which case you'll have seen this message already!

Martin


Martin Harvey
Environmental Records Officer
Buckinghamshire and Milton Keynes Environmental Records Centre
email: mailto:[email protected]
web: www.bucksmkerc.org.uk


BROWSE RECORD QUERIES WITH ADDITIONAL DATA FIELDS

Queries and documentation provide by Martin Harvey, Buckinghamshire and Milton Keynes Environmental Records Centre (www.bucksmkerc.org.uk), based in part on queries provided by various members of the MapMate users egroup. Please email me if you find any errors or problems with these queries:
[email protected]


1. GENERAL DOCUMENTATION

Four main queries are given below:

- Browse all Records full details
- Browse all Records full details for <site>
- Browse all Records full details for <taxon>
- Browse all Records full details rare species

These main queries require seven additional sub-queries:

- Browse all Records full details subquery
- Browse all Records full details for <taxon> subquery
- Browse all Records full details rare species subquery
- Taxon Status Species subquery
- BAP Species subquery
- W+C Act Species subquery
- Taxon Status Edited for Rare Species

Sub-queries are not intended to be run as standalone queries, but perform functions within the main queries. When putting these subqueries into MapMate they must be given the exact names shown above, otherwise the main queries will not run correctly.

Each query uses the current default settings for sites and taxa.

The queries will not run if your version of MapMate does not include the tables for BAP-listed species and Wildlife and Countryside Act scheduled species - these tables were made available in a patch some time ago, but may be missing from older versions of MapMate.

The queries for "rare species" should pick out all species that are Red Data Book or Nationally Scarce (according to the statuses given in the MapMate taxon dictionary, which is not always fully accurate), plus all species that are Priority species in the UK BAP, plus all species that are listed in the Wildlife and Countryside Act.

In the queries for "rare species" the species included are:
- any species that MapMate classifies as Red Data Book or Nationally Scarce (*); plus
- any species listed as a Priority in the UK BAP (as of 2004) (**); plus
- any species receiving some degree of protection under the Wildlife and Countryside Act (***)

(*): MapMate's data on species statuses is a little erratic - for some taxonomic groups (e.g. Orthoptera) MapMate does not yet hold any status information (so they will not be included in this query); for others, MapMate's statuses are sometimes at variance with the 'official' published lists.
(**): This is based on the UK BAP species list as provided by MapMate - note that there are some taxonomic groups (e.g. some of the marine species) listed in the UK BAP which are not yet included in the MapMate species dictionary.
(***): This is based on the W&C Act list as provided by MapMate - note that this list does not distinguish between species that are fully protected and those that receive partial protection.

If you are recording in Hampshire, Essex or Buckinghamshire I can supply versions of the queries that allow local BAP species to be highlighted.


2. INSTALLATION

First of all, highlight the name of the query you want to set up from the QUERIES text below, and copy that name. Then, in MapMate use the normal method for creating customised queries: open the "Analysis" window, double-click on the "User Queries" sub-heading and choose <new user query>.

You will be prompted for a name; paste in the name you copied from the table below.

You will then see an SQL window with two lines of text - delete all the text that is there.

Now return to the QUERIES text below and highlight the SQL text (the block of text that has statements beginning SELECT, FROM, WHERE etc.), and copy this text. NB that some of these queries are rather long, make sure you highlight all the text from the beginning up to and including the semicolon at the end.

Return to MapMate and paste the SQL text into the blank window.

Make sure you save the new query - click on the yellow folder icon at the top of the window.


3. DETAILED NOTES

These queries are based on the standard "Browse all records" query, but add various details:

- MapMate ID: this is MapMate's [_guk] (unique ID) for the individual record.

- Classification1: three levels up the MapMate taxon hierarchy (NB that this will sometime produce a Phylum, sometimes an Order, and sometimes a Family, due to varying levels in the MapMate taxon hierarchy)

- Classification2: two levels up the MapMate taxon hierarchy

- Classification3: one level up the MapMate taxon hierarchy

- Code

- Taxon

- Vernacular

- Conservation Status: the Red Data Book, Nationally Scarce or other status according to the MapMate taxon dictionary (but with some modification so that the terms used for Nationally Scarce are more consistent)

- UKBAP Status: status according to MapMate in 2004

- W+C Act Schedule: listing according to MapMate in 2004

- Site

- Original_GridRef

- x_coord/y_coord: grid ref translated into coordinates to facilitate data export to GIS packages; the coordinates will plot at the centre of the appropriate sized square referenced by the grid ref

- 1k Square: the 1km square extracted from the grid ref (blank if the grid ref is not sufficiently precise)

- tetrad: the 2km square extracted from the grid ref (blank if the grid ref is not sufficiently precise)

- Quantity: numerical quantities are shown as numbers; where MapMate uses zero and minus numbers to stand in for text quantities then these are shown as text (e.g. "0" is shown as "Present", "-1" as "Dominant", "-7" as "Not present", etc.)

- Date: usually shows the first of MapMate's two date fields, but for records assigned to a year the year only is shown here.

- Date Range: where a record is assigned to a date range a second date (MapMate's "DateTo" field) is shown here.

- Recorder

- Determiner

- Stage

- Method

- Comment

- Source of data: this come from the author, year and title of the Reference attached to the record.


4. QUERIES

Browse all Records full details subquery

SELECT Records.[_guk] AS [MapMate ID], Taxa_3.Taxon AS Classification1, Taxa_2.Taxon AS Classification2, Taxa_1.Taxon AS Classification3, [Taxa\Default].Code AS Code, [Taxa\Default].Taxon, [Taxa\Default].Vernacular, [C\Taxon Status Species subquery].[Conservation Status], [C\BAP Species subquery].[BAP Listed] AS [UKBAP Status], [C\W+C Act Species subquery].[W+C Act Schedule], [Sites\Default].Name AS Site, [Sites\Default].OSGridRef AS Original_GridRef, [Sites\Default].Xpos AS x_coord, [Sites\Default].Ypos AS y_coord, IIf(Len([OSGridref])=4,Null,IIf(Len([OSGridref])=5,Null,IIf(Len([OSGridRef])=6,[OSGridRef],IIf(Len([OSGridRef])=8,Left([OSGridRef],4) & Mid([OSGridRef],6,2),Left([OSGridRef],4) & Mid([OSGridRef],7,2))))) AS [1k Square], [Sites\Default].[2kSquare] AS tetrad, IIf(Records.Quantity=-1, 'Dominant',IIf(Records.Quantity=-2, 'Abundant',IIf(Records.Quantity=-3, 'Frequent',IIf(Records.Quantity=-4, 'Occasional',IIf(Records.Quantity=-5, 'Rare',IIf(Records.Quantity=-6, 'Very rare',IIf(Records.Quantity=-7, 'Not present',IIf(Records.Quantity=0, 'Present',Records.Quantity)))))))) AS Quantity, IIf([Records].DateTo-[Records].Date<27,Format([Records].Date,"dd mmm yyyy"),IIf([Records].DateTo-[Records].Date>32,Format([Records].Date,"yyyy"),Format([Records].Date,"mmm yyyy"))) AS [Date], IIf([Records].DateTo=[Records].Date,'',IIf([Records].DateTo-[Records].Date<27,Format([Records].DateTo,"dd mmm yyyy"),IIf([Records].DateTo-[Records].Date>32,Format([Records].DateTo,"yyyy"),Format([Records].DateTo,"mmm yyyy")))) AS [End Date], Recorders.Name AS Recorder, Recorders_1.Name AS Determiner, TaxonStage.Stage, Methods.Method, Records.Comment, ([References].[Author]&" "&[References].[Year]&". "&[References].[Title]) AS [Source of data]
FROM ((((((((((((Records INNER JOIN [Taxa\Default] ON Records.[*Taxon] = [Taxa\Default].[_guk]) INNER JOIN Taxa AS Taxa_1 ON [Taxa\Default].[**Parent] = Taxa_1.[_guk]) INNER JOIN Taxa AS Taxa_2 ON Taxa_1.[**Parent] = Taxa_2.[_guk]) INNER JOIN Taxa AS Taxa_3 ON Taxa_2.[**Parent] = Taxa_3.[_guk]) INNER JOIN [C\Taxon Status Species subquery] ON Records.[*Taxon] = [C\Taxon Status Species subquery].[guk]) INNER JOIN [C\BAP Species subquery] ON Records.[*Taxon] = [C\BAP Species subquery].[guk]) INNER JOIN [C\W+C Act Species subquery] ON Records.[*Taxon] = [C\W+C Act Species subquery].[guk]) INNER JOIN [Sites\Default] ON Records.[*Site] = [Sites\Default].[_guk]) INNER JOIN Recorders ON Records.[*Recorder] = Recorders.[_guk]) INNER JOIN TaxonStage ON Records.[*Stage] = TaxonStage.[_guk]) INNER JOIN Recorders AS Recorders_1 ON Records.[*Identifier] = Recorders_1.[_guk]) INNER JOIN Methods ON Records.[*Method] = Methods.[_guk]) INNER JOIN [References] ON Records.[*Reference] = References.[_guk]
ORDER BY Taxa_3.Taxon, Taxa_2.Taxon, Taxa_1.Taxon, [Taxa\Default].Code;


Browse all Records full details for <taxon> subquery

PARAMETERS [#taxon] Text;
SELECT Records.[_guk] AS MapMateID, Taxa_3.Taxon AS Classification1, Taxa_2.Taxon AS Classification2, Taxa_1.Taxon AS Classification3, [Taxa\Default].Code AS Code, [Taxa\Default].Taxon, [Taxa\Default].Vernacular, [C\Taxon Status Species subquery].[Conservation Status], [C\BAP Species subquery].[BAP Listed] AS [UKBAP Status], [C\W+C Act Species subquery].[W+C Act Schedule], [Sites\Default].Name AS Site, [Sites\Default].OSGridRef AS Original_GridRef, [Sites\Default].Xpos AS x_coord, [Sites\Default].Ypos AS y_coord, IIf(Len([OSGridref])=4,Null,IIf(Len([OSGridref])=5,Null,IIf(Len([OSGridRef])=6,[OSGridRef],IIf(Len([OSGridRef])=8,Left([OSGridRef],4) & Mid([OSGridRef],6,2),Left([OSGridRef],4) & Mid([OSGridRef],7,2))))) AS [1k Square], IIf(Records.Quantity=-1, 'Dominant',IIf(Records.Quantity=-2, 'Abundant',IIf(Records.Quantity=-3, 'Frequent',IIf(Records.Quantity=-4, 'Occasional',IIf(Records.Quantity=-5, 'Rare',IIf(Records.Quantity=-6, 'Very rare',IIf(Records.Quantity=-7, 'Not present',IIf(Records.Quantity=0, 'Present',Records.Quantity)))))))) AS Quantity, IIf([Records].DateTo-[Records].Date<27,Format([Records].Date,"dd mmm yyyy"),IIf([Records].DateTo-[Records].Date>32,Format([Records].Date,"yyyy"),Format([Records].Date,"mmm yyyy"))) AS [Date], IIf([Records].DateTo=[Records].Date,'',IIf([Records].DateTo-[Records].Date<27,Format([Records].DateTo,"dd mmm yyyy"),IIf([Records].DateTo-[Records].Date>32,Format([Records].DateTo,"yyyy"),Format([Records].DateTo,"mmm yyyy")))) AS [End Date], Recorders.Name AS Recorder, Recorders_1.Name AS Determiner, TaxonStage.Stage, Methods.Method, Records.Comment, ([References].[Author]&" "&[References].[Year]&". "&[References].[Title]) AS [Source of data]
FROM ((((((((((((Records INNER JOIN [Taxa\Default] ON Records.[*Taxon] = [Taxa\Default].[_guk]) INNER JOIN Taxa AS Taxa_1 ON [Taxa\Default].[**Parent] = Taxa_1.[_guk]) INNER JOIN Taxa AS Taxa_2 ON Taxa_1.[**Parent] = Taxa_2.[_guk]) INNER JOIN Taxa AS Taxa_3 ON Taxa_2.[**Parent] = Taxa_3.[_guk]) INNER JOIN [C\Taxon Status Species subquery] ON Records.[*Taxon] = [C\Taxon Status Species subquery].[guk]) INNER JOIN [C\BAP Species subquery] ON Records.[*Taxon] = [C\BAP Species subquery].[guk]) INNER JOIN [C\W+C Act Species subquery] ON Records.[*Taxon] = [C\W+C Act Species subquery].[guk]) INNER JOIN [Sites\Default] ON Records.[*Site] = [Sites\Default].[_guk]) INNER JOIN Recorders ON Records.[*Recorder] = Recorders.[_guk]) INNER JOIN TaxonStage ON Records.[*Stage] = TaxonStage.[_guk]) INNER JOIN Recorders AS Recorders_1 ON Records.[*Identifier] = Recorders_1.[_guk]) INNER JOIN Methods ON Records.[*Method] = Methods.[_guk]) INNER JOIN [References] ON Records.[*Reference] = References.[_guk]
WHERE Records.[*Taxon] = [#taxon]
ORDER BY [Sites\Default].OSGridRef, [Sites\Default].Name, [Records].Date;


Browse all Records full details rare species subquery

SELECT Records.[_guk] AS [MapMate ID], Taxa_3.Taxon AS Classification1, Taxa_2.Taxon AS Classification2, Taxa_1.Taxon AS Classification3, [Taxa\Default].Code AS Code, [Taxa\Default].Taxon, [Taxa\Default].Vernacular, [C\Taxon Status Edited for Rare Species].[Edited Conservation Status] AS [Conservation Status], [C\BAP Species subquery].[BAP Listed] AS [UKBAP Status], [C\W+C Act Species subquery].[W+C Act Schedule], [Sites\Default].Name AS Site, [Sites\Default].OSGridRef AS Original_GridRef, [Sites\Default].Xpos AS x_coord, [Sites\Default].Ypos AS y_coord, IIf(Len([OSGridref])=4,Null,IIf(Len([OSGridref])=5,Null,IIf(Len([OSGridRef])=6,[OSGridRef],IIf(Len([OSGridRef])=8,Left([OSGridRef],4) & Mid([OSGridRef],6,2),Left([OSGridRef],4) & Mid([OSGridRef],7,2))))) AS [1k Square], IIf(Records.Quantity=-1, 'Dominant',IIf(Records.Quantity=-2, 'Abundant',IIf(Records.Quantity=-3, 'Frequent',IIf(Records.Quantity=-4, 'Occasional',IIf(Records.Quantity=-5, 'Rare',IIf(Records.Quantity=-6, 'Very rare',IIf(Records.Quantity=-7, 'Not present',IIf(Records.Quantity=0, 'Present',Records.Quantity)))))))) AS Quantity, IIf([Records].DateTo-[Records].Date<27,Format([Records].Date,"dd mmm yyyy"),IIf([Records].DateTo-[Records].Date>32,Format([Records].Date,"yyyy"),Format([Records].Date,"mmm yyyy"))) AS [Date], IIf([Records].DateTo=[Records].Date,'',IIf([Records].DateTo-[Records].Date<27,Format([Records].DateTo,"dd mmm yyyy"),IIf([Records].DateTo-[Records].Date>32,Format([Records].DateTo,"yyyy"),Format([Records].DateTo,"mmm yyyy")))) AS [End Date], Recorders.Name AS Recorder, Recorders_1.Name AS Determiner, TaxonStage.Stage, Methods.Method, Records.Comment, ([References].[Author]&" "&[References].[Year]&". "&[References].[Title]) AS [Source of data]
FROM ((((((((((((Records INNER JOIN [Taxa\Default] ON Records.[*Taxon] = [Taxa\Default].[_guk]) INNER JOIN Taxa AS Taxa_1 ON [Taxa\Default].[**Parent] = Taxa_1.[_guk]) INNER JOIN Taxa AS Taxa_2 ON Taxa_1.[**Parent] = Taxa_2.[_guk]) INNER JOIN Taxa AS Taxa_3 ON Taxa_2.[**Parent] = Taxa_3.[_guk]) INNER JOIN [C\Taxon Status Edited for Rare Species] ON [Taxa\Default].[*Status] = [C\Taxon Status Edited for Rare Species].[GUK]) INNER JOIN [C\BAP Species subquery] ON Records.[*Taxon] = [C\BAP Species subquery].[guk]) INNER JOIN [C\W+C Act Species subquery] ON Records.[*Taxon] = [C\W+C Act Species subquery].[guk]) INNER JOIN [Sites\Default] ON Records.[*Site] = [Sites\Default].[_guk]) INNER JOIN Recorders ON Records.[*Recorder] = Recorders.[_guk]) INNER JOIN TaxonStage ON Records.[*Stage] = TaxonStage.[_guk]) INNER JOIN Recorders AS Recorders_1 ON Records.[*Identifier] = Recorders_1.[_guk]) INNER JOIN Methods ON Records.[*Method] = Methods.[_guk]) INNER JOIN [References] ON Records.[*Reference] = References.[_guk]
WHERE [C\Taxon Status Edited for Rare Species].[Edited Conservation Status] <> 'none' OR [C\BAP Species subquery].[BAP Listed] = Not Null OR [C\W+C Act Species subquery].[W+C Act Schedule] = Not Null
ORDER BY Taxa_3.Taxon, Taxa_2.Taxon, Taxa_1.Taxon, [Taxa\Default].Code;


Taxon Status Species subquery

SELECT [Taxa\Default].[_guk] AS guk, IIf(TaxonStatus.Status='Scarce','Nationally Scarce',IIf(TaxonStatus.Status='Notable','Nationally Scarce',IIf(TaxonStatus.Status='Nb','Nationally Scarce/Nb',IIf(TaxonStatus.Status='Na','Nationally Scarce/Na',IIf(TaxonStatus.Status='Nb+Endemic','Nationally Scarce/Nb and Endemic',IIf(TaxonStatus.Status='Na+Endemic','Nationally Scarce/Na and Endemic',IIf(TaxonStatus.Status='Notable+Endemic','Nationally Scarce and Endemic',TaxonStatus.Status))))))) AS [Conservation Status]
FROM TaxonStatus RIGHT JOIN [Taxa\Default] ON TaxonStatus.[_guk] = [Taxa\Default].[*Status];


BAP Species subquery

SELECT [Taxa\Default].[_guk] AS guk, BAPP.Schedule AS [BAP Listed]
FROM BAPP RIGHT JOIN [Taxa\Default] ON BAPP.[_guk] = [Taxa\Default].[_guk];


W+C Act Species subquery

SELECT [Taxa\Default].[_guk] AS guk, WCA.Schedule AS [W+C Act Schedule]
FROM WCA RIGHT JOIN [Taxa\Default] ON WCA.[_guk] = [Taxa\Default].[_guk];


Taxon Status Edited for Rare Species

SELECT TaxonStatus.[_guk] AS GUK, TaxonStatus.Status AS [Conservation Status], IIf(TaxonStatus.[_guk] = 'cdt1d1z1', "Nationally Scarce/Nb", IIf(TaxonStatus.[_guk] = 'eoir21z1', "Nationally Scarce", IIf(TaxonStatus.[_guk] = 'k4bf31z1', "Nationally Scarce and Endemic", IIf(TaxonStatus.[_guk] = 'l4uw31z1', "Nationally Scarce/Na", IIf(TaxonStatus.[_guk] = 'p9sq31z1', "Nationally Scarce", IIf(TaxonStatus.[_guk] = 'ej7fq1z1', "Nationally Scarce/Na and Endemic", IIf(TaxonStatus.[_guk] = 'poq341z1', "Nationally Scarce/Nb and Endemic", IIf(TaxonStatus.[_guk] <> '0gdrs1z1' AND TaxonStatus.[_guk] <> '1buqu1z1' AND TaxonStatus.[_guk] <> '4w2qi1zj' AND TaxonStatus.[_guk] <> '5frds1zj' AND TaxonStatus.[_guk] <> '5mwip1z1' AND TaxonStatus.[_guk] <> '7o2sp1my' AND TaxonStatus.[_guk] <> '8qj1w1z1' AND TaxonStatus.[_guk] <> '9ug4g1z1' AND TaxonStatus.[_guk] <> 'bu1t41z1' AND TaxonStatus.[_guk] <> 'cdt1d1z1' AND TaxonStatus.[_guk] <> 'drl9l1z1' AND TaxonStatus.[_guk] <> 'e3s2a1zj' AND TaxonStatus.[_guk] <> 'eoir21z1' AND TaxonStatus.[_guk] <> 'flq851z1' AND TaxonStatus.[_guk] <> 'gn2of1z1' AND TaxonStatus.[_guk] <> 'h5kzq1z1' AND TaxonStatus.[_guk] <> 'imcpi1z1' AND TaxonStatus.[_guk] <> 'k4bf31z1' AND TaxonStatus.[_guk] <> 'kf3cd1z1' AND TaxonStatus.[_guk] <> 'l4uw31z1' AND TaxonStatus.[_guk] <> 'o2gi41z1' AND TaxonStatus.[_guk] <> 'p9sq31z1' AND TaxonStatus.[_guk] <> 'pl56i1z1' AND TaxonStatus.[_guk] <> 'poq341z1' AND TaxonStatus.[_guk] <> 'q626m1zj' AND TaxonStatus.[_guk] <> 'u2d3p1z1' AND TaxonStatus.[_guk] <> 'u2w0b1z1' AND TaxonStatus.[_guk] <> 'ubhr41z1' AND TaxonStatus.[_guk] <> 'w43ed1z1' AND TaxonStatus.[_guk] <> 'wq2z31zj' AND TaxonStatus.[_guk] <> 'zg6v01z1' AND TaxonStatus.[_guk] <> 'zumuc1z1' AND TaxonStatus.[_guk] <> 'yoy2g1z1' AND TaxonStatus.[_guk] <> 'ej7fq1z1', "none", TaxonStatus.Status)))))))) AS [Edited Conservation Status]
FROM TaxonStatus
ORDER BY TaxonStatus.Status;


Browse all Records full details

SELECT [MapMate ID], Classification1, Classification2, Classification3, Code, Taxon, Vernacular, [Conservation Status], [UKBAP Status], [W+C Act Schedule], Site, Original_GridRef, x_coord, y_coord, [1k Square], tetrad, Quantity, [Date], IIf(([Date]=[End Date]),'',[End Date]) AS [Date Range], Recorder, Determiner, Stage, Method, Comment, [Source of data]
FROM [C\Browse all Records full details subquery]
ORDER BY Classification1, Classification2, Code, Taxon;


Browse all Records full details for <site>

PARAMETERS [$site] Text;
SELECT Classification1, Classification2, Classification3, Code, Taxon, Vernacular, [Conservation Status], [UKBAP Status], [W+C Act Schedule], Site, Original_GridRef, Quantity, [Date], IIf(([Date]=[End Date]),'',[End Date]) AS [Date Range], Recorder, Determiner, Stage, Method, Comment, [Source of data]
FROM [C\Browse all Records full details subquery]
WHERE (Site) Like [$site];


Browse all Records full details for <taxon>

SELECT MapMateID, Classification1, Classification2, Classification3, Code, Taxon, Vernacular, [Conservation Status], [UKBAP Status], [W+C Act Schedule], Site, Original_GridRef, x_coord, y_coord, [1k Square], Quantity, [Date], IIf(([Date]=[End Date]),'',[End Date]) AS [Date Range], Recorder, Determiner, Stage, Method, Comment, [Source of data]
FROM [C\Browse all Records full details for <taxon> subquery];


Browse all Records full details rare species

SELECT [MapMate ID], Classification1, Classification2, Classification3, Code, Taxon, Vernacular, [Conservation Status], [UKBAP Status], [W+C Act Schedule], Site, Original_GridRef, x_coord, y_coord, [1k Square], Quantity, [Date], IIf(([Date]=[End Date]),'',[End Date]) AS [Date Range], Recorder, Determiner, Stage, Method, Comment, [Source of data]
FROM [C\Browse all Records full details rare species subquery];

Martin Harvey

Re: Additional analysis queries for MapMate

Thanks Martin, much appreciated.

Re: Additional analysis queries for MapMate

Someone recently contacted me with some requests to change the above queries, so I've updated them and slightly extended them. The revised versions can be downloaded from my website:
http://sites.google.com/site/kitenetter/Home/mapmate

(see the fourth bullet point "SQL text (.txt file) for custom User Queries to browse records with additional details")

Martin Harvey