1 (edited by mosteth 28-05-2009 12:00:37)

Topic: MapMate NBN Data Exchange Format Query

Has anyone developed an SQL query specifically for export to NBN Data Exchange format? I realise that it wouldn't be perfect and the Taxon Key would have to be imported from elsewhere, but it would be handy to have all the available info automatically generated.

I would (probably) be able to get there myself in time, but if there is already an SQL it would save me a few headaches...

Steff

Stefanie Buell
Biodiversity Projects Officer
Kent County Council

Re: MapMate NBN Data Exchange Format Query

Hi Steff,

I'm just about to start constructing a MapMate query for exporting our data at BMERC up to NBN, if you've already done the job for me that would be great! Otherwise I'll post what I do, hopefully in the next few weeks.

Martin

Martin Harvey

Re: MapMate NBN Data Exchange Format Query

Sorry Martin, I haven't made any in-roads there; this year we are just uploading a few key species datasets so it was easier for me to use the F9 custom query for each species. This is proving a bit of a pain where I was aiming to do 'marine mammals' and have had to break it down by species.

I look forward to trying out your query!

Steff

Stefanie Buell
Biodiversity Projects Officer
Kent County Council

Re: MapMate NBN Data Exchange Format Query

Steff, if you're using an F9 query, try this:

- open the data entry window and set your taxon filter to "Vertebrata: Marine Mammals"
- in the date field, put a range that will include all your records, e.g. "1500 to 2010" (and press enter)
- run the F9 query

That should pick up all the species in one go.

Martin

Martin Harvey

Re: MapMate NBN Data Exchange Format Query

Excellent!

Cheers Martin

Steff

Stefanie Buell
Biodiversity Projects Officer
Kent County Council

Re: MapMate NBN Data Exchange Format Query

Hello Steph

Martin's method is probably a lot easier than mine but I thought I would have a go at extracting records into the NBN Exchange Format using the customised F9 query as you suggested. To run the query on more than 1 species I copied the SQL (f9 query in the User table (making sure I didn't update the access database as I opened it)) into a new user defined query. I then did some minor modifications to get the query looking more like the exchange format. This should now query all the records in your database

SQL:
SELECT Records.[_guk] AS RecordKey, Format([Records].[Date],'dd/mm/yyyy') AS StartDate, Format([Records].[DateTo],'dd/mm/yyyy') AS EndDate, [Taxa\Default].[_guk] AS TaxonCode, [Taxa\Default].Taxon AS Taxon, [Sites\Default].OSGridRef AS Gridreference, [Sites\Default].[_precision] AS Precision, Recorders.Name AS Recorder, Recorders_1.Name AS Determiner, [Sites\Default].[_guk] AS SiteKey, [Sites\Default].Name AS SiteName
FROM (((Records INNER JOIN [Taxa\Default] ON Records.[*Taxon] = [Taxa\Default].[_guk]) INNER JOIN [Sites\Default] ON Records.[*Site] = [Sites\Default].[_guk]) LEFT JOIN Recorders ON Records.[*Recorder] = Recorders.[_guk]) LEFT JOIN Recorders AS Recorders_1 ON Records.[*Identifier] = Recorders_1.[_guk];

It creates most of the compulsory fields except the DateType and Projection fields but I can create these fields using VBA functions or send you the functions to do it yourself. Also you will need to match the species to the taxonversionkeys in the species dictionary. Again I can help with this or send you a list of NHM names and keys

In the above query I have not included any of the optional fields, for example the attribute fields eg comments, abundance etc but these could be added if wished by selecting them in the F9 query and then repeating the above steps to create the user defined query. Again I can help with this.

To limit the query to a number of species I added them to the end of the query in a where clause. To get the names and make sure there are no typos in them I ran another user defined query to list the taxon names recorded in the database, selecting(whilst holding down the control button) and copying the multiple species

SQL:
SELECT [Taxa\Default].Taxon
FROM Records INNER JOIN [Taxa\Default] ON Records.[*Taxon] = [Taxa\Default].[_guk]
GROUP BY [Taxa\Default].Taxon
ORDER BY [Taxa\Default].Taxon;

Then I pasted the species list into a where clause in the nbn exchange sql, making sure I added the quotes and commas where needed. For example this now queries records for 4 plant species

SQL:
SELECT Records.[_guk] AS RecordKey, Format([Records].[Date],'dd/mm/yyyy') AS StartDate, Format([Records].[DateTo],'dd/mm/yyyy') AS EndDate, [Taxa\Default].[_guk] AS TaxonCode, [Taxa\Default].Taxon AS Taxon, [Sites\Default].OSGridRef AS Gridreference, [Sites\Default].[_precision] AS Precision, Recorders.Name AS Recorder, Recorders_1.Name AS Determiner, [Sites\Default].[_guk] AS SiteKey, [Sites\Default].Name AS SiteName
FROM (((Records INNER JOIN [Taxa\Default] ON Records.[*Taxon] = [Taxa\Default].[_guk]) INNER JOIN [Sites\Default] ON Records.[*Site] = [Sites\Default].[_guk]) LEFT JOIN Recorders ON Records.[*Recorder] = Recorders.[_guk]) LEFT JOIN Recorders AS Recorders_1 ON Records.[*Identifier] = Recorders_1.[_guk]
WHERE [Taxa\Default].Taxon IN
(
'Aira praecox',
'Alchemilla glabra',
'Scrophularia nodosa',
'Sagina nodosa'
);

I then selected all the resulting records and exported it as a tab-delimited file

I this helps. I would be interested if anyone has a quicker and easier way especially around the production of the datetypes and projection.

Best wishes
Graham
NBN Technical Liaison Officer

Re: MapMate NBN Data Exchange Format Query

Thanks for that Graham, very useful. Could you give an example of what's needed in the DateType and Projection fields, would be nice to be able to generate that at the same time if possible. I'm sure these fields are documented but haven't had a chance to look it up yet, sorry.

Martin

Martin Harvey

Re: MapMate NBN Data Exchange Format Query

My understanding is that..

DateTypes relate to whether the record date is:
a precise day ('D')
a range of days ('DD')
a month ('M')
a range of months ('OO')
a year ('Y')
a range of years ('YY')
an end date to the nearest year ('-Y')
unknown or of no date ('U' or 'ND')

Maybe doing something with the 'duration' option that is available in the F9 custom query?

and as we are just using a grid reference rather than easting and northing, the projection would need to be 'OSGB' (British Isles), 'OSNI' (Northern Ireland) or 'OSI' (Ireland).

(correct me if I'm wrong!)

Steff

Stefanie Buell
Biodiversity Projects Officer
Kent County Council

Re: MapMate NBN Data Exchange Format Query

Thanks Steph, the DateType and Projection are as you describe, except the month is "O" not "M"

Information is documented in the data provider pack which can be accessed from the NBN website - http://www.nbn.org.uk/. Click on "How to start sharing data with the NBN" and then on the Sharing data page you can download the data provider pack from a link half way down the page. The NBN Exchange Format is documented - 2- NBN Exchange Format guidelines v2.3.1.doc

Best wishes
Graham

Re: MapMate NBN Data Exchange Format Query

I've had a go at producing a query that will run within MapMate and generate all the Gateway required fields, plus a number of optional fields. I hope I've managed to get the DateType field working correctly, it seems to do so for the data I've tested it with but there may be quirks in other datasets that I've not allowed for. Any feedback welcome.

One thing this doesn't do is provide the TaxonVersionKey for each species. I'm aware that several people have had to tackle the translation of MapMate into Recorder, has anyone managed to compile a txon translation table to give the NBN TaxonVersionKey to go with each MapMate taxon?

Martin


MapMate user query for exporting data in NBN Gateway format
Version 1.0 at 8 July 2009

The query below gives the SQL needed to set up a new user query in MapMate, to export MapMate data in a format matching the NBN Gateway requirements, insofar as this is possible. I have tested it on several datasets and it seems to work correctly, but it is always possible that other datasets will hit problems that I have not foreseen, so if you use it please check the results carefully and let me know if you find any problems.

This query generates the required fields for the Gateway, plus a number of optional fields - you may need to customise the latter for your own requirements. A list of the fields used is given below the query itself.

A text file containing this query can also be downloaded from my website:
http://sites.google.com/site/kitenetter/Home/mapmate
Please send any feedback to Martin Harvey: [email protected]


Export records for NBN Gateway


SELECT Records.[_guk] AS RecordKey, Records.[*Reference] AS SurveyKey, "" AS TaxonVersionKey, Taxa_1.[_guk] AS MapMateTaxonKey, Taxa_1.Taxon AS MapMateTaxon, Taxa_1.Vernacular AS MapMateVernacular, IIf([Records].[Quantity]=-7,True,False) AS ZeroAbundance, False AS Sensitive, Format([Date],"dd/mm/yyyy") AS StartDate, Format([DateTo],"dd/mm/yyyy") AS EndDate, IIf((([Records].[DateTo]-[Records].[Date]>366) And (Day([Records].[Date])=1) And (Month([Records].[Date])=1) And (Day([Records].[DateTo])=31) And (Month([Records].[DateTo])=12)),"YY",IIf(((Year([Records].[Date])=Year([Records].[DateTo])) And (Day([Records].[Date])=1) And (Month([Records].[Date])=1) And (Day([Records].[DateTo])=31) And (Month([Records].[DateTo])=12)),"Y",IIf(((Day([Records].[Date])=1) And (Day([Records].[DateTo])>27) And (Month([Records].[Date])<>Month([Records].[DateTo]))),"OO",IIf(((Month([Records].[Date])=Month([Records].[DateTo])) And (Day([Records].[Date])=1) And (Day([Records].[DateTo])>27)),"O",IIf(([Records].[Date]=[Records].[DateTo]),"D","DD"))))) AS DateType, Records.[*Site] AS SiteKey, Sites.Name AS SiteName, Sites.OSGridRef AS GridReference, "OSGB" AS Projection, Sites.[_precision] AS Precision, Recorders.Name AS Recorder, Recorders_1.Name AS Determiner, IIf([Records].[Quantity]>0,[Records].[Quantity],Null) AS Count, IIf([Records].[Quantity]=0,True,False) AS NoAbundanceRecorded, 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]=-21,'Locally Dominant',IIf([Records].[Quantity]=-22,'Locally Abundant',IIf([Records].[Quantity]=-23,'Locally Frequent',""))))))))) AS DAFORabundance, TaxonSex.Sex AS TaxonSex, TaxonStage.Stage AS TaxonStage, Methods.Method AS RecordMethod, RecordStatus.Status AS RecordStatus, Records.Comment AS RecordComment, Sites.ViceCounty, [References]![Author] & " (" & [References]![Year] & ") " & [References]![Title] AS SurveyReference
FROM (((((((((Records INNER JOIN [Sites\Default] ON Records.[*Site] = [Sites\Default].[_guk]) INNER JOIN [Taxa\Default] ON Records.[*Taxon] = [Taxa\Default].[_guk]) INNER JOIN Taxa AS Taxa_1 ON [Taxa\Default].[**RefersTo] = Taxa_1.[_guk]) INNER JOIN TaxonSex ON Records.[*Sex] = TaxonSex.[_guk]) INNER JOIN TaxonStage ON Records.[*Stage] = TaxonStage.[_guk]) INNER JOIN RecordStatus ON Records.[*Status] = RecordStatus.[_guk]) INNER JOIN Recorders ON Records.[*Recorder] = Recorders.[_guk]) INNER JOIN Recorders AS Recorders_1 ON Records.[*Identifier] = Recorders_1.[_guk]) INNER JOIN [References] ON Records.[*Reference] = References.[_guk]) INNER JOIN Methods ON Records.[*Method] = Methods.[_guk];



The fields produced by the above query are:

RecordKey: MapMate's Records.[_guk]

SurveyKey: MapMate's Records.[*Reference] - ideally you should also provide a list of the references in a separate table (see also the SurveyReference field, below)

TaxonVersionKey: not available within MapMate, so this field is left unpopulated, and will need to be populated by other means before sending to Gateway

MapMateTaxonKey: MapMate's Taxa.[_guk] (NB this is linked via Records.[*Taxon] - Taxa.[_guk], and then Taxa.[*RefersTo] - Taxa_1.[_guk], in order to use the latest versions of any synonyms within MapMate - a side-effect of this is that any trinomial subspecies names used for Lepidoptera will be replaced by the relevant binomial species names)

MapMateTaxon: MapMate's taxon name

MapMateVernacular: MapMate' verncaular name

ZeroAbundance: set to True if MapMate's "Quantity" field shows "Not Present" (i.e. -7 in the underlying database field), otherwise set to False

Sensitive: this is set to False for all records, requires manual adjustment if any of the records being exported need to be flagged as sensitive

StartDate: MapMate's Records.Date

EndDate: MapMate's Records.DateTo

DateType: a rather complex calculation to display which of the Gateway's date range types is applicable to the date ranges used in MapMate

SiteKey: MapMate's Records.[*Site]

SiteName: MapMate's Sites.Name

GridReference: MapMate's Sites.OSGridRef

Projection: set to "OSGB" - this will need changing manually if dealing with records from Ireland or Northern Ireland

Precision: MapMate's Sites.[_precision]

Recorder

Determiner

Count: MapMate's Records.Quantity (excluding those quantities of less than 1, where MapMate uses zero to indicate "Present, quantity not recorded" and minus numbers for DAFOR terms etc.)

NoAbundanceRecorded: set to True where MapMate uses zero to indicate "Present, quantity not recorded", otherwise False

DAFORabundance: shows MapMate's DAFOR terms, if used

TaxonSex

TaxonStage

RecordMethod

RecordStatus

RecordComment

ViceCounty

SurveyReference: concatenates Author, Date and Title from the References table

Martin Harvey

11 (edited by gcfrench 09-07-2009 09:19:57)

Re: MapMate NBN Data Exchange Format Query

Hello Martin

Thanks for producing this query and tackling all those IIf statements to produce the datetype. Possible tweaks to the SQL are
1. ZeroAbundance: IIf([Records].[Quantity]=-7,True,False) AS ZeroAbundance. Replace True and False with "T" and "F" respectively
2. Sensitive: Assuming Mapmate doesn't flag confidential records then I would probably remove the sensitive field (False AS Sensitive). This field is optional and if no field is supplied "False" is assumed anyway
3. Projection: A quick and dirty way of getting projection could be to test if the second character in the gridreference is a number or not ie IIf(IsNumeric(Mid([Sites.OSGridRef],2,1)),"OSNI","OSGB") AS Projection.

Making these changes the SQL is
SELECT Records.[_guk] AS RecordKey, Records.[*Reference] AS SurveyKey, "" AS TaxonVersionKey, Taxa_1.[_guk] AS MapMateTaxonKey, Taxa_1.Taxon AS MapMateTaxon, Taxa_1.Vernacular AS MapMateVernacular, IIf([Records].[Quantity]=-7,"T","F") AS ZeroAbundance, Format([Date],"dd/mm/yyyy") AS StartDate, Format([DateTo],"dd/mm/yyyy") AS EndDate, IIf((([Records].[DateTo]-[Records].[Date]>366) And (Day([Records].[Date])=1) And (Month([Records].[Date])=1) And (Day([Records].[DateTo])=31) And (Month([Records].[DateTo])=12)),"YY",IIf(((Year([Records].[Date])=Year([Records].[DateTo])) And (Day([Records].[Date])=1) And (Month([Records].[Date])=1) And (Day([Records].[DateTo])=31) And (Month([Records].[DateTo])=12)),"Y",IIf(((Day([Records].[Date])=1) And (Day([Records].[DateTo])>27) And (Month([Records].[Date])<>Month([Records].[DateTo]))),"OO",IIf(((Month([Records].[Date])=Month([Records].[DateTo])) And (Day([Records].[Date])=1) And (Day([Records].[DateTo])>27)),"O",IIf(([Records].[Date]=[Records].[DateTo]),"D","DD"))))) AS DateType, Records.[*Site] AS SiteKey, Sites.Name AS SiteName, Sites.OSGridRef AS GridReference, IIf(IsNumeric(Mid(Sites.OSGridRef,2,1)),"OSNI","OSGB") AS Projection, Sites.[_precision] AS Precision, Recorders.Name AS Recorder, Recorders_1.Name AS Determiner, IIf([Records].[Quantity]>0,[Records].[Quantity],Null) AS Count, IIf([Records].[Quantity]=0,True,False) AS NoAbundanceRecorded, 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]=-21,'Locally Dominant',IIf([Records].[Quantity]=-22,'Locally Abundant',IIf([Records].[Quantity]=-23,'Locally Frequent',""))))))))) AS DAFORabundance, TaxonSex.Sex AS TaxonSex, TaxonStage.Stage AS TaxonStage, Methods.Method AS RecordMethod, RecordStatus.Status AS RecordStatus, Records.Comment AS RecordComment, Sites.ViceCounty, [References]![Author] & " (" & [References]![Year] & ") " & [References]![Title] AS SurveyReference FROM (((((((((Records INNER JOIN [Sites\Default] ON Records.[*Site] = [Sites\Default].[_guk]) INNER JOIN [Taxa\Default] ON Records.[*Taxon] = [Taxa\Default].[_guk]) INNER JOIN Taxa AS Taxa_1 ON [Taxa\Default].[**RefersTo] = Taxa_1.[_guk]) INNER JOIN TaxonSex ON Records.[*Sex] = TaxonSex.[_guk]) INNER JOIN TaxonStage ON Records.[*Stage] = TaxonStage.[_guk]) INNER JOIN RecordStatus ON Records.[*Status] = RecordStatus.[_guk]) INNER JOIN Recorders ON Records.[*Recorder] = Recorders.[_guk]) INNER JOIN Recorders AS Recorders_1 ON Records.[*Identifier] = Recorders_1.[_guk]) INNER JOIN [References] ON Records.[*Reference] = References.[_guk]) INNER JOIN Methods ON Records.[*Method] = Methods.[_guk];

The sticking point is matching the MapMate TaxonKey to the NHM Taxonversionkey. Unfortunately I haven't got a list but would be happy to compile one if people send me their matches

Best wishes
Graham
NBN Technical Liaison Officer

Re: MapMate NBN Data Exchange Format Query

Thanks for those improvements Graham. At BMERC we're currently working on our botanical dataset with a view to supplying an upload to the NBN later this summer.

Any offers for lists to match MapMate taxa to NHM taxa? I'm aware that various records centres have done imports from MapMate to Recorder, so presumably there must some translation tables somewhere? I have a (partial) translation table from Recorder to MapMate that I can contribute, will send it to Graham. But we'll need to do some checking/editing of any translation table to ensure we are not introducing taxonomic errors into the data.

Martin

Martin Harvey

Re: MapMate NBN Data Exchange Format Query

Hello Martin

No offers as yet. Once you have finished your checking/editing of the translation table if you could send it to me that would be great to get the ball rolling

Best wishes

Graham