Skip to content

Expeditions for non-MBARI ship

Revision History

  • Rev1 31-August-2020
  • Rev2 31-August-2020

Preliminaries

  • Let videolab (Lonnie) create MiniROV dives in his MINROV_Divelog database
  • Let the mbari minirov data load jobs succeed
  • Let the nightly jobs populate expd with dive info and nav, ctd etc.

Here are some useful queries to check on the above:

select * from [MINIROV_DiveLog].[dbo].Dive
SELECT * FROM [dbo].[MinirovLogfiles] where yr = 2020 order by startdtg
SELECT * FROM [dbo].[minirovrawctdload] Where Yr = 2020
SELECT * FROM [dbo].[minirovrawnavload] Where Yr >= 2020

Note

Quite often we are provided incorrect dive start/end times. It's useful to check those and correct them in both Lonnie’s Dive table and EXPD’s Dive table

Useful queries to compare dive times:

select * from [MINIROV_DiveLog].[dbo].Dive
select diveid, deviceid, rovname, divenumber, divestartdtg, diveenddtg from [Expd].[dbo].Dive where rovname = 'mini' and divenumber between 116 and 126 order by divenumber

Get the Platform ID

Check if the ship exists and get its shortname and its assigned ssds uniqueid.

select * from platformlookup

Warning

Only create new platform if it doesn’t already exist!!

If you need a new ID, create a new entry in SSDS and jot down it’s deviceID by using the ssds web page at:

http://new-ssds.mbari.org/ssds/newDevice.jsp

Decide on a unique 4-5 character ‘shortname’ and manually create a new entry in the platformlookup table using the ssds deviceid using query above in AquaData studio ‘edit mode’

Examine Dive Needing a Parent Expedition

Run query for your dives like (substitute your #’s):

select * from dive where rovname = 'mini' and divenumber between 116 and 126 order by divenumber

Note

Jot down and save for later, the start datetime of the earliest dive and end datetime of the last dive (these we will can use as the Expedition start and end datetimes as we usually aren’t provided that info)

Note how the ExpeditionID_FK is null or zero – we will fix this shortly

Warning

DO NOT PROCEED IF YOUR KEYS ARE **NOT** MISSING

Run query for your dive summaries like:

select * from divesummary where rovname = 'mini' and divenumber between 116 and 126 order by divenumber

Note how the expdid, shipid, shipname are null or zero – we will fix this shortly.

Warning

DO NOT PROCEED IF YOUR EXPEDITION KEYS ARE **NOT** MISSING

Create the Expedition

Expedition id’s or keys, are not auto-incremented, they are maintained manually in the uniqueid table. We need to increment that number – this will become your new ExpeditionID

select * from uniqueid

Use AquaDataStudio in edit mode to add one to the Expedition number field and be sure to save the result. (If AquaDataStudio asks what it should use as the primary key – tell it ‘Tablename’)

re-execute the query to make sure the change took. And make sure you have that new number “id” handy

Next, examine the Expedition table for your ship id:

select * from expedition where deviceid = 1869 order by shipseqnum

Observe that for a new ship, the result set will be empty. Otherwise, if there are prior expeditions for this ship there may be one or more entries. Jot down the highest ‘shipseqnum’.

So you should now have the following info close at hand:

  • The unique id to assign for the expedition
  • The ship deviceid
  • The ship shortname
  • The shipseqnum – your new will bewhat you discovered above + 1 (or = 1 if it’s the first..)
  • The Chief Scientist name
  • An expedition start and end datetime
  • Perhaps a Purpose
  • Perhaps an EquipmentDesc like “Minirov dives”
  • StatCode should be set to cmpl (complete)

Re-execute the query above in edit mode in AquadataStudio. Add a record and populate it with info from above. (Make sure you have incremented the shipseqnum!)

Save it, and then re-execute to make sure it took.

Next it’s on to linking up the dives to the expedition.

Linking Dives to the New Parent Expedition

Starting with the divesummary table…

Re-run query for your dive summaries like:

select * from divesummary where rovname = 'mini' and divenumber between 116 and 126 order by divenumber

In AquaDataStudio edit mode, fix expdid, shipdid and shipname for all dives shown. Also set the dirtybit0 flag=1 for all dives shown.

Next do the dive table in the same way. This time you only need to edit the ExpeditionID_FK field.

select * from dive where rovname = 'mini' and divenumber between 116 and 126 order by divenumber

Fix the EXPD Web Pages

Let Karen know she needs to make changes to the expd web active server pages to expose the new ship in dropdowns etc.

In the email archives for 10/19, Karen found…

Yes, I did add a few things to get bhrzn to work in the postCruise.

  1. Obviously, to the dropdown ShipSelect....
  2. How the Results EXPD section looks when choosing Bold Horizon from Dropdown
  3. I had to add it to the expd_function.inc file (muy importante)
  4. You also have to run the popEDwithDBqueries_perseus script on Draco to pull in the data once you and I make changes.

I can certainly add Kilo Moana to the postcruise. Let me know. Also, I do think we will probably need to re-run the script on Draco to create the links.