1

Topic: Batch update of Grid Reference

How do I update the grid reference for all records for a given Location and a given character string in the Obs Comment?

In this case, the Location is a UKBMS Transect Site, and the Obs Comment contains the Section number, and each section has a different Grid Reference. Unfortunately some of the records were entered with the wrong grid reference.

Harry Clarke
Surrey County Butterfly Recorder

2

Re: Batch update of Grid Reference

It would probably  be best if this was done in two stages. Firstly, an XML report to get the Taxon_Occurrence_Key and spatial ref from the comments into a csv  file. This could then be checked and put back in through a batch update to make the changes.  To help I would need to know specifically the rules for determining  the spatial ref from the comments.

Mike Weideli

3

Re: Batch update of Grid Reference

In simplistic terms, the update will be something like
UPDATE tables
SET grid_ref = 'TQ123456'
WHERE Location = 'Transect name' AND (Obs Comment = 'n' OR Obs Comment = 'Sn')

I will need to edit the batch file, changing location, obs comment, and grid-reference as appropriate, as several set of changes are required. I've checked via a report that the above condition will uniquely select the appropriate records.
Each section's set of records is stored under an Event for a particular date. the "sample grid reference" is most important to update.
Creating a csv file with Taxon_Occurrence_Key, etc. fields of relevant records is straight forward, if that is what is required.

Harry Clarke
Surrey County Butterfly Recorder

4

Re: Batch update of Grid Reference

Hi

Things to note are that ob comment may contain rtf formatting, so this will need to be removed or may cause problems. Updating the grid ref will also require updating the lat/long so these will need to be calculated. To be safe the spatial refernce system should also be updated. Probably best to keep the event in line with the sample.  I will think it through and get back to you. To confirm Location is the Location name from the sample not the Location from the hierarchy ?

Mike Weideli

5

Re: Batch update of Grid Reference

Attached is Batch Update which from what I understand will do what you require, It it doesn't do what you want let me know. Make sure you have a backup and take care with it as it has no validation. If obs comment has only one criteria then enter the same parameter in both.

Post's attachments

LC_Special11.xml 2.07 kb, 1 downloads since 2018-02-05 

You don't have the permssions to download the attachments of this post.
Mike Weideli

6

Re: Batch update of Grid Reference

Thanks I will give this a try tomorrow or Wednesday.
Location is as in "Event Location" and "Location" in the Sample

Harry Clarke
Surrey County Butterfly Recorder

7

Re: Batch update of Grid Reference

Hi

Looks like I used the wrong Location first time so a version which uses the correct one is attached.

Post's attachments

LC_Special12.xml 2.1 kb, 2 downloads since 2018-02-06 

You don't have the permssions to download the attachments of this post.
Mike Weideli

8

Re: Batch update of Grid Reference

I have made a mistake, it is the "Sample Comment" that contains the section number. What changes do I need to make to the batch file?

Harry Clarke
Surrey County Butterfly Recorder

9

Re: Batch update of Grid Reference

Hi

Attached is a revised version. I haven't tested this one.

Post's attachments

LC_Special14.txt 1.98 kb, 7 downloads since 2018-02-07 

You don't have the permssions to download the attachments of this post.
Mike Weideli

10

Re: Batch update of Grid Reference

Many thanks Mike, that seems to have produced the desired result, with the right number of records correctly modified.

Harry Clarke
Surrey County Butterfly Recorder