1 (edited by TonyP 15-05-2012 08:44:39)

Re: Batch Updates

This might sound strage but is there any descriptions of what the batch updates actually do? Yes I know they come with descriptions I just wondered if there any meaningful ones anywhere.

Det6 I'm told will allow me to change the validation type for a whole survey;

Det6 - Creates new Determination using original taxa
Creates a new determination to the original taxa for a selected survey where the system custodian is not
the custodian of the current determination. Use on imported Surveys to create new determinations for validation or to change the taxa at a later stage.
Select the Survey by entering the Survey name or by running the report from the observation hierarchy as a quick update.

Having not run a batch update and having a liking for my data I'm reluctant to run an update when it seems to say it does more than one thing i.e create a new determination [not what I want] or change the taxa [not what I want] and validation seems to be mentioned vaguely in conjunction with a new determination.

What it does not say is that it will ask me for anything to allow any control. I do not know if I can stop it if I do not like what it is about to do [should I be prompted in the first place].

Yes I could just try it but is that really the best way? The help file does not work, I did have it working once. The User guide does not mention batch updates.

Should write my own SQL; at least I would know what it is supposed to do.

Any help from someone who has tried to update the validation would be welcome. I'm just trying to change Unconfirmed or Observation to Correct for a survey.

Oh and just to clarify, the validity is correct since the data has come from the county recorder ti's just it comes from say MapMate and there was no option to set validation. To add a new determination would be a corruption of the data as it would not be true.

Data Manger
Somerset Environmental Records Centre

2

Re: Batch Updates

The 'Det' set  of Batch Updates haven't been properly documented.   They  were originally written to meet the  requirements of a specific user and were made generally available in case they might be of use. All are aimed at assisting with the management of imported data and went with a  document  on managing data exchange which never got published.  The way users manage their data varies considerably so probaby the main use of these updates is to provide example of what can be done and to demonstrate some of the techniques involved. 

Of the other Batch Updates many are associated with the management of Taxon Designations and the use of these is fully documented.  I think what most of the others do is self explanatory, but this might need looking into further.

As a first step anyone needing a Batch Update to carry out a specific task should put the request on the forum. In many cases there will already be something available which will meet the requirement or which can easily be adapted to do so.  I think most past requests have been met.

All Batch Updates need to be designed and used with care. A backup should always be taken before any changes are made and  the results checked carefully before further data is entered.
 
As far as your specific requirement is concerned, I don't think there is anything particularly wrong in creating a new determination for every record, which is what the batch update you mention would do, but this doesn't achieve anything useful in the situation you describe and increases the database size for no good reason.  To make the change you require  you need to change the detemination type on the taxon determination and to bring the  verified indicator on taxon occurrence into line  with the chosen determination type.  If you need a Batch Update or any help with the SQL to do this let me know.

Mike Weideli

3

Re: Batch Updates

Thanks Mike I had come to this conclusion once I had got over my normal surprise. I did take the trouble of opening it in SSMS last night and realised that there are prompts there and that I would need to write some T-SQL to accomplish the required result. The upshot of this is that the data required by JNCC will not be available as the NBN export will not export most of the data due to its determination type.

Still that's what we have to work with. Internally I avoided looking at the determination type as I know they are all ok for us to use.

Most of my T-SQL is a work in progress at present but I would like to publish at least some of it as it reaches maturity. Oh and without most of your UDF's it would have been much harder, I thank you once again. I've made a couple myself and will no doubt be doing more as we go.

We've managed to generate a flat file version of recorder that takes about 20 mins for 1.3 million records and this then gets sent to MapInfo [through text files at present but should be through MS SQL later] and Arc through ARC Server via MS SQL.

Data Manger
Somerset Environmental Records Centre

4

Re: Batch Updates

I think this may be of some help - I do a similar thing with our county datasets (that is set the determination to "correct" after import from MapMate). It updates all determinations within a survey.

(use at own risk, etc. etc.)

UPDATE Taxon_Determination
SET Determination_Type_Key = 'NBNSYS0000000007'
WHERE Taxon_Determination_Key IN (SELECT TD.Taxon_Determination_Key
                                  FROM Survey AS SV
                                  INNER JOIN Survey_Event SE ON SE.Survey_Key = SV.Survey_Key
                                  INNER JOIN Sample S ON S.Survey_Event_Key = SE.Survey_Event_Key
                                  INNER JOIN Taxon_Occurrence TOCC ON TOCC.Sample_key = S.Sample_Key
                                  INNER JOIN Taxon_Determination TD ON TD.Taxon_Occurrence_Key = TOCC.Taxon_Occurrence_Key
                                  WHERE SV.Survey_Key = 'SR0003510000002P')
Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

5

Re: Batch Updates

How very kind of you Charlie. I note your caveats and I will also add in some more clauses as some helpful person has been and modifed the determation for some, quite appropriatly as it happens, since I loaded the data.

Tony

Data Manger
Somerset Environmental Records Centre

6

Re: Batch Updates

To get the verified indicators in line with your determination types you will also need to run

Update Taxon_Occurrence set verified = Determination_Type.Verified
FROM Taxon_Determination TDET
INNER JOIN Taxon_Occurrence TOCC ON TOCC.Taxon_Occurrence_key = TDET.taxon_Occurrence_key
INNER JOIN Determination_Type
ON Determination_Type.Determination_Type_Key = TDET.Determination_Type_key

David

7

Re: Batch Updates

David

The query is correct for data just imported,  but as would apply to  all records in the database  you need to take into account only  the determination type of  the preffered determination so

Update Taxon_Occurrence set verified = DT.Verified
FROM Taxon_Determination TDET
INNER JOIN Taxon_Occurrence TOCC ON TOCC.Taxon_Occurrence_key = TDET.taxon_Occurrence_key and TDET.Preferred = 1
INNER JOIN Determination_Type DT
ON DT.Determination_Type_Key = TDET.Determination_Type_key

Mike Weideli

8

Re: Batch Updates

Thanks to you all I took from your comments and ran:

/* Sets the Preferred Determination for Unconfirmed occurrances imported from MapMate to Confirmed */
/* Tony Price 28th May 2012 */

Update     Taxon_Determination
SET            DETERMINATION_TYPE_KEY = 'NBNSYS0000000012' --Confirmed
FROM         dbo.TAXON_DETERMINATION
WHERE     (CHARINDEX('MMEEE', TAXON_DETERMINATION_KEY) = 1) AND (DETERMINATION_TYPE_KEY = 'NBNSYS0000000011') AND (Preferred = 1) -- MapMate Key with Unconfirmed and Prefferred

Then I ran Mikes code above which pulls the verrifcation status from the determation to the occurrance.
In summary; all records imported from MapMate have their prefferred determination changed from 'Unconfirmed' to 'Confirmed' and the when looked at in Recorder they will have 'Passed verification'.

This has made my colleague very happy and I can now use the NBN Export on the data.

Data Manger
Somerset Environmental Records Centre