How to install these user queries
Click on the link below for the query you wish to use. This will take you to a Google docs page showing the query’s title and the SQL text needed to set it up. 1. Highlight and then copy the query title 2. Go to MapMate and 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>. 3. You will be prompted for a name; paste in the name you copied in step 1. 4. You will now see MapMate’s SQL window with two lines of text - delete all the text that is there. 5. Now return to the Google docs page and highlight the SQL text (the block of text that has statements beginning SELECT, FROM, WHERE etc.), and copy this text (the right-click Copy function doesn't seem to work on Google docs, but you should be able to use Ctrl+C to do the copy). 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. 6. Return to MapMate and paste the SQL text into the blank window. 7. Make sure you save the new query - click on the yellow folder icon at the top of the window. Exporting MapMate records to Google Earth: click here for an example of the output from the query listed below. |
kitenet - home > MapMate resources >
Custom user queries
| query | notes | keyword | date uploaded |
|---|---|---|---|
| Summary of records from centres with info on deletes and archived records | For each CUK, shows the total number of records, the total number that haven't been deleted, and the total number that have been neither deleted nor archived | analysis | 12 December 2015 |
| Browse all records for <reference> | Shows all records (with determiner) for a particular reference | analysis | 10 April 2015 |
| Browse Taxa showing synonyms | Shows all names in current taxon filter, with an additional column giving the name that each synonym refers to | list species | 25 November 2014 |
| Browse records with extra detail - REVISED 2 | (revised at Apr 2014 to show correct 1km squares for 12-fig and for Irish grid refs) set of 6 queries (and 6 sub-queries) - extra details include: Determiner, Reference, taxon classification, national status etc. | browse records | 23 April 2014 |
| Browse Sites Not in Use | Shows all sites on your system that are NOT linked to records or recorders | other | 9 February 2014 |
| Best day for species | Shows the days with the highest species totals for your current filter settings | analysis | 20 November 2013 |
| Exporting MapMate records to Google Earth | Produces an output that can be used with the very useful Google Earth tools provided by Keith Balmer on the Beds Nat Hist Soc website | other | 20 November 2013 |
| Species per Year | Shows the number of species recorded each year for the current taxon and geographical area filter settings. | analysis | 3 October 2013 |
| Check for incorrect 10-figure grid refs | A bug in MapMate can cause sites with 10-figure grid refs to be synced inaccurately - this query allows you to see if you have sites affected by this | other | 13 November 2012 |
| Replace <species 1> with <species 2> | Allows you to change ALL your records from one species name to another, for updating scientific name changes | update | 14 October 2012 |
| Find Records with tabs or line breaks in the Comments | It's possible for tab or line break characters to end up in the Comments field of MapMate, which can cause problems when exporting data as text files. These queries allow you to find the records so they can be edited. | other | 9 October 2012 |
| Top 100 most abundant species | A simple adaptation of MapMate’s standard “Top 20 most abundant species”. | analysis | 6 August 2012 |
| Habitats Directive Species | Query and Filter to allow listing of records for Habitats Directive species, set up to facilitate JNCC questionnaire | list species | 7 December 2011 |
| Species list with details of first record | Lists species with the details of the first-ever record for each on your database | list species | 5 November 2011 |
| Earliest records showing full dates | Shows the record/s that are the earliest in the year that each species has ever been recorded | other | 21 May 2011 |
| Browse all records for GIS | Standard browse query with X and Y coords for exporting to GIS; also includes determiner and reference fields | browse records | 25 January 2011 |
| Update site for set of records on date | update query to do bulk edit of site for a set of records on a date (NB will change your records, use with caution, back-up first!) | update | 15 July 2010 |
| List Records introduced from <gen A> to <gen B> inclusive | Adapted from standard query "List Records introduced at <generation>" | browse records | 28 June 2010 |
| Species League Table (10km Records) 1980 onwards | Query adapted from the standard "Species League Table (10km Records)", filtered to exclude records from before 1980. | analysis | 6 April 2010 |
| Sites showing records summary | This query lists all sites that match the current sites filter, and shows how many records there are at each site, and what years the earliest and latest ones are. | other | 11 March 2010 |
| Species List full details for site | Requires the subqueries from "Browse records with extra detail" - lists species with classification and status | list species | 3 February 2010 |
| Browse all Records with determiner and reference | Standard browse query with determiner and reference added - also a version that allows you to choose records for a particular reference | browse records | 1 February 2010 |
| Analyse species abundance trends | queries to analyse trend of one species against overall numbers for all species | analysis | 11 January 2010 |
| Browse records with <any text> in the comment field | browse records | 11 January 2010 | |
| Customising queries to use 'Refers To' | shows how to set up queries that use "Refers To" to show synonyms, with example SQL | other | 11 January 2010 |
| Export records for NBN Gateway | for records centre/recording scheme use: exports data in format for sending to NBN Gateway | browse records | 11 January 2010 |
| record verification - set of queries | lengthy document containing 14 queries (and 9 sub-queries) to assist with validating/verifying records | validation | 11 January 2010 |
| Replace <date to change> with <corrected date> | update query to do bulk edit of dates (NB will change your records, use with caution, back-up first!) | update | 11 January 2010 |
| 1kSquareList | sub-query for listing records by 1k square - UPDATED at 14 March 2014 to allow for Irish grid references | sub-query | 9 January 2010 |
| Browse all Records for <1km-square> | requires sub-query "1kSquareList" | browse records | 9 January 2010 |
| 10km square list for a <vice-county> | provides list of 10km squares that overlap county | other | 6 January 2010 |
Showing 31 items