Topic: Selecting Data by Admin Area

Does anybody know if you can get MapMate to select records by admin area.  We have tried to produce maps of a local authority district but these show all records visible on the map not just those within the admin area.

We are trying to export data to the NBN but want to do it by admin area rather than taxon group.  However, since the defaults for area appear to be based on the map area rather than the actual admin area given for each site, records outside of the particular admin area are selected. 

Am I missing something simple?

Suzanne Waymont
Greater Manchester Records Centre

Suzanne Waymont
Greater Manchester Ecology Unit/ Greater Manchester LRC

Re: Selecting Data by Admin Area

Hi, I'm no MapMate expert but I think if you right click over the district you want and choose query selected regions you will be on the right lines. Press F1 for the help information. You can add a selection polygon and use that to search all records for locations which worked for me. In fact this question has meant I can now do something I've been wondering about for a while.
Thanks

Tony Price
Data Manager, Somerset Environmental Records Centre (SERC)

Re: Selecting Data by Admin Area

Tony

Thanks for the reply but we have tried doing it this way and it doesn't do want we want it to do. 

We need to select all the records for a local authority area and then run a query on it (eg to put the data into a format that can be exported to the NBN).

Suzanne

Suzanne Waymont
Greater Manchester Ecology Unit/ Greater Manchester LRC

Re: Selecting Data by Admin Area

It depends on how the Admin Area has been used within your set of MapMate data. You can use the Sites filters to choose a modern county or unitary (rather than a vice-county), and run queries based on that, but i can't remember whether you can choose filters for local authority district level. But the results you get will depend on how consistently the data has been entered - MapMate allows various types of Admin Area (e.g. at county or district level) and doesn't enforce any rules as to how they are used (although the default option in more recent versions of MapMate has been for district level).

If the sites filters don't work at the level you require, then you'll probably need to set up a custom query/queries - happy to provide some info on this if you can say exactly what admin areas you need to use, but I'm away from a copy of MapMate at the moment so it won't be for a couple of weeks.

Martin

Martin Harvey

Re: Selecting Data by Admin Area

Martin

The site filters don't appear to let you select unitary authorities (which all of ours are), so any help with writing a query would be great.

Our districts are all in Greater Manchester: Wigan, Bolton, Bury, Manchester, Salford, Oldham, Rochdale, Stockport, Trafford, Tameside.

We are extracting data to go onto the NBN so have been using the query you and Graham French produced on this forum, but it would also be useful to have a general queries to select records by district.

Any help would be very welcome.

Suzanne

Suzanne Waymont
Greater Manchester Ecology Unit/ Greater Manchester LRC

Re: Selecting Data by Admin Area

Suzanne,

Sorry to have taken so long to get back on to this. Below is another version of the Export to NBN query, that allows you to select which admin area you want to use. Notes:

- When you run the query, you can type in all or part of an admin area name, but be careful of typing just part of the name. For example, if you type in "Bury" it will search for Aylesbury, Canterbury etc. as well as Bury, whereas if you type in the full "Greater Manchester: Bury" it will only use that definition.

- I've added an additional first column to the query that explicitly shows which admin area has been used - you don't need to export this column to NBN of course, but I thought it was useful to ensure that the queyr has picked out the right set of records.

- As discussed previously, this will only work for those records in MapMate that have been assigned to the correct district/unitary, e.g. you may find you have records assigned to admin area "Greater Manchester (Metropolitan County 1974-1985)" instead of the unitary.

Anyway, give it a try and see if it helps.

Martin


Export records for NBN Gateway for <admin area>

SELECT Records.[_guk], UKSiteParents.Name, 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,"T","F") 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 UKSiteParents ON [Sites\Default].[**Parent] = UKSiteParents.[_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]
WHERE UKSiteParents.Name Like "*"+[enter all or part of an Admin Area name]+"*";

Martin Harvey

Re: Selecting Data by Admin Area

Martin

Thanks very much for your help - that seems to have worked fine.

Suzanne

Suzanne Waymont
Greater Manchester Ecology Unit/ Greater Manchester LRC