Home About News R&D Data Internal Page Search
navigation bar
Home Projects Information Roadmap

BOG DATA SYSTEM: DATA ACCESS

Table of Contents

bulletDOCUMENTATION *
bulletSYSTEM CONFIGURATION  *
bulletSetting Up ODBC *
bulletSetting Up DSN (Data Source Name) *
bulletAPPLICATIONS *
bullet DATABASE *
bulletData Tables*
bulletKey Fields *
bulletOther Useful Fields*
bulletSQL Examples *
bulletBOG Cruise List

INTRODUCTION

BOG shipboard data is loaded into a database called BOG.  The MS-SQL Server, SOLSTICE, is the interface between the database and the users.  There are 3 main data access methods: MSQuery, MSExcel, and MSAccess.  Your PC computer must configured to be able to connect to the database.  The sections following will tell you where you can find information on the database, the configurations, and some of the applications.  Quick Reference Page 

DOCUMENTATION

There is an online BOG Database User's Manual. This manual covers the shipboard data only. Included are data loading for profile and bottle data, data access from MSQuery, MSExcel, and MSAccess, and Data table definitions.

The is also an online web page covering the ODBC connection and the DSN setup. Included are detailed instructions for connecting your PC to the MS SQL Server SOLSTICE and setting up your data sources.

SYSTEM CONFIGURATION

BOG 

 

Data files are accessible through the MBARI network using ODBC (Open DataBase Connectivity) connection to the Microsoft SQL Server. To access the data you might need to install some programs, MSQuery, MSExcel, MSAccess. Refer to the TSD computer support team for more information on these programs. In most cases you will already have Excel and Access. MSQuery is at typhoon\apps\office.97\office\Msqry32.exe

Setting Up ODBC

You will need to tell your computer where the Microsoft SQL (Structured Query Language) Server resides. Instructions for this procedure are online at the following URL : http://www.mbari.org/bog/roadmap/documents/odbc_mods.htm

The NT configuration has a few more windows but is basically the same. This can be set from the control panel, 'ODBC Data Source'. If you do not have this option contact TSD. Under the 'System DSN' tab add a system data source. Your data source driver is 'SQL Server'. Name your data source 'BOG', be sure to use capital letters. You may describe the data source as 'BOG Database'; the 'SQL Server' is SOLSTICE. The SQL Server authenticity login should be set to 'With SQL Server authentication using login ID and password entered by the user'. Check the 'Connect to SQL Server to obtain default settings for the additional configuration options. If you have a SQL Server account use your login ID and password, otherwise you may use the 'Login ID' of 'everyone'. Please see Francisco Chavez or Reiko Michisaki for the password. Change the default database to 'BOG'. Accept the other defaults. The system should test your connection. If your connection does not work contact TSD.

Setting Up Data Sources

For data connections to MSQuery and MSExcel you will need to tell your computer your data source.  The setup is very similar to the ODBC connections.  You can find instructions at the following URL: http://www.mbari.org/bog/roadmap/documents/dsn_mods.htm

APPLICATIONS

In addition to using MSQuery, MSExcel, and MSAccess,  there are two main applications for data access both of which are MSAccess front-ends to the database.  To use these applications you must have your computer's ODBC and DSN configured. BOGRPTS uses MSAccess to query the database, the extracted data can then be loaded into an EXCEL spreadsheet. This application is best suited to data extraction. This application is located at: \\typhoon\diatom\BOG\BOGRpts.mdb

The other application is BOG_USER. This is a simple MSAccess database with the data tables linked. Available are a handful of VisualBasic functions which can be used in data queries, such as converting the date and time fields to seasons, or converting the decimal latitude and longitude to distance (km). This application gives one more flexibility in both accessing and manipulating the data. This requires more knowledge of MSAccess, please refer to TSD This application is located at:

\\typhoon\diatom\BOG\BOGRpts.mdb

DATABASE

The Biological Oceanography Group (BOG) maintains a database containing shipboard and some mooring data. Shipboard data includes the Point Lobos time series, Shift-up, Pegasus, CoOP, CoFE, and SECRET cruises. The NOAA shipboard data includes the 1992-1996 Long Line and the 1996-1999 Equatorial Pacific cruises. The mooring data is from M1 (1989-1999), and M2 (1992-1999). Mooring CTD data is from 1992-1999.  This section briefly describes the database.  For more detail refer to the User's Guide.

SERVER: SOLSTICE

DATABASE: BOG

Data Tables

Table

Description

EXPEDITION

Expedition information: Project, Platform, Dates, Stations, etc.

BCTD

Bottle Data: Chl, Phaeo, Temp., Sal., Sigma T, NO3, NO2, SIO, PO4, NH4, Conductivity, Transmissivity, PAR, PARCOS, Fluor, Chla.

PCTD

Profiling CTD Data: Temp., Sal., Conduct., Transmiss, Oxygen, IR443, IR490, IR555

CARBON

C14 Bottle Data

KONSTANTS

Constants used to calculate productivity

IV

Depth integrated chl, phaeo, carbon, pb. Derived values: Pbopt, MLD, Upwelling indices, PAR24, Biomass Growth Rate.

TAXA

Taxanomic Biomass expressed as protist carbon (mg m-3)

TAXA_AL

Raw Taxanomic values

FCM

Flow cytometry: pros, syn, p_euks, h_bact

UNDERWAY_EXPD

Expedition information: Platform, Project, Cruise, Site, Date and Time, etc.

UNDERWAY_DATA

Data: Tmp, Sal, Conduct, Fluor, Chla, Transmiss, NO3, PCO2, SIO4, TCO2

UNDERWAY_LOAD

Expediton and Data, see above

 

 

NOAA_EXPEDITION

Same as above tables

NOAA_BCTD

 

NOAA_PCTD

 

NOAA_CARBON

 

NOAA_IV

 

NOAA_TAXA

 

 

 

CTD

Mooring CTD: temp, sal, conduct, transmiss, fluor

Avgd_CTD

Daily Averaged CTD values

ATLAS

Temperature profiles: Air, SST, T10, T20, T40, T60, T80, T100, T150, T200, T250, T300

 

Key Fields

Key fields can be used to link tables or constrain queries, i.e. SELECT PROJECT, CRUISE, CTRB_ID, RJDAY, SEQ, DEPTH, CHL_GFF FROM BCTD WHERE PROJECT="SECRET" AND CTRB_ID LIKE "67-*" AND DEPTH<10 ORDER BY RJDAY, SEQ, DEPTH
Project and cruise IDs can be found in the BOG cruise list.

Field

Values

Description

PROJECT

PTLO

Point Lobos Time Series, 1989-2000

 

PP

Pegasus, 1988-1991

 

SU

Shift-Up, 1992-1993

 

COOP95

Coastal Ocean Processes, 1995

 

COFE96

Iron, 1996

 

SECRET

Secret, 1997-2000

CRUISE

'JJJYY'

Point Lobos, i.e. 07899

 

'PPNN'

Pegasus, sequential numbering, 1-17 i.e. PP17

 

'SUYY-N'

Shift Up, i.e. SU92-4, SU93-4

 

COOP95

Coastal Ocean Processes

 

COFE96

Iron

 

'SNYY'

Secret, i.e. S197, S200

SEQ

Integer

Cast number, 0-170

DEPTH

Integer

Nominal depth (m), usually 0, 5, 10, 20, 30, 40, 60, 80, 100, 150, 200

BOTTLE

Integer

CTD bottle number, usually 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1

PLATFORM

String

Name of Ship or Mooring, usually Point Lobos, Point Sur, New Horizon, Kai'imimoana, M1, M2

CTRB_ID

String

MBARI Station name, i.e. Mooring1, Mooring2, C1, H3, C7, H1, 67-50, 67-55, 67-60, 67-65, 67-70, 67-75, 67-80, 67-85, 67-90, 67-95, 67-100

 

Other Useful Fields

These fields can be useful in queries, graphs, and analysis.

Field

Description

Example of Use

RJDAY

Running Day of Year

Can be used as xaxis in graphs, sorting data

YEAR_DATE

Year and Day of Year

Can be used as query constraint, i.e. WHERE YEAR_DATE > 1996365 AND YEAR_DATE<1998001

DEC_LAT, DEC_LONG

Decimal Latitude and Longitude

Can be used in graphs

DATE_TIME

Date and Time

Can be used with DATEPART function to extract year, month, day, day of year, hour, minute i.e. DATEPART('M' [DATE_TIME]) extracts month

SQL Examples

Structured Query Language is used to extract data from the database.  SQL may be used in MSQuery and MSAccess.  If possible it is best to use the query builder in MSAccess.  If SQL is used in MSAccess the table names must be prefixed by dbo_, i.e. dbo_BCTD, dbo_CARBON, dbo_EXPEDITION.

SQL Description Output
Select project, cruise, rjday, seq, ctrb_id, depth, tmp, sal, chl_gff from BCTD where ctrb_id="mooring1" and project="PTLO" and depth<10 order by rjday, seq, depth; Extract temperature, salinity, and chlorophyll from the Monterey Bay Time Series for station Mooring1 for surface values.  Order data.

project

cruise

rjday

seq

ctrb_id

depth

tmp

sal

chl_gff

ptlo

09589

461

5

Mooring1

0

 

 

 

ptlo

10789

473

3

Mooring1

0

11.8411

 

2.497

ptlo

12489

490

4

Mooring1

0

 

 

 

ptlo

21489

580

2

MOORING1

0

12.179

33.7381

14.557

ptlo

22689

592

2

MOORING1

0

14.4082

33.6249

2.037

ptlo

24889

614

2

MOORING1

0

12.5933

33.5287

3.417

ptlo

26389

629

2

MOORING1

0

13.5861

33.451

2.629

ptlo

28589

651

2

MOORING1

0

13.3796

33.4402

2.037

 

Select project, rjday, year_date, seq, ctrb_id, depth, chl_gff from BCTD where year_date>1996365 and year_date<1998001 order by rjday, seq, depth; Extract chlorophyll for all projects, stations, and depths  for data collected in 1997.  Order data.

PROJECT

RJDAY

YEAR_DATE

SEQ

CTRB_ID

DEPTH

CHL_GFF

ptlo

3310

1997022

1

C1

0

1.16

ptlo

3310

1997022

1

C1

5

0.94

ptlo

3310

1997022

1

C1

10

0.97

ptlo

3310

1997022

1

C1

20

0.71

ptlo

3310

1997022

1

C1

30

0.41

ptlo

3310

1997022

1

C1

40

0.49

ptlo

3310

1997022

1

C1

60

0.51

ptlo

3310

1997022

1

C1

80

0.38

ptlo

3310

1997022

1

C1

100

0.1

ptlo

3310

1997022

1

C1

150

0.03

ptlo

3310

1997022

1

C1

200

0.02

 

SELECT BCTD.PROJECT, BCTD.CRUISE, BCTD.RJDAY, BCTD.SEQ, BCTD.CTRB_ID, CARBON.LIGHT, BCTD.CHL_GFF, CARBON.CARBON_GFF
FROM BCTD INNER JOIN CARBON ON (BCTD.BOTTLE = CARBON.BOTTLE) AND (BCTD.SEQ = CARBON.SEQ) AND (BCTD.CRUISE = CARBON.cruise)
WHERE (((BCTD.PROJECT)="SECRET") AND ((BCTD.CTRB_ID) In ('mooring1','67-50','H3')) AND ((CARBON.LIGHT)=100))
ORDER BY BCTD.RJDAY, BCTD.SEQ, CARBON.LIGHT;
Link the BCTD and CARBON tables to extract surface chlorophyll and carbon from the SECRET cruise for stations Mooring1, 67-50, and H3.  100% light level will give you the surface values.   Order data.

PROJECT

CRUISE

RJDAY

SEQ

CTRB_ID

LIGHT

CHL_GFF

CARBON_GFF

SECRET

S197

3350

5

H3

100

1.344

80.0776471208606

SECRET

S197

3353

17

H3

100

5.142

236.424259882419

SECRET

S297

3442

4

H3

100

4.391

94.4

SECRET

S397

3495

20

H3

100

0.994

114.08

SECRET

S497

3545

99

Mooring1

100

4.115506329

136.02

SECRET

S198

3675

2

H3

100

0.385

17.5417433813726

SECRET

S298

3736

29

Mooring1

100

0.155578571428571

4.56684218322852

SECRET

S398

3758

2

H3

100

6.42179023508137

257.384955189729

 

For more information on SQL (Structure Query Language) and databases, see the MBARI Technical Support Division's class schedule.  See the BOG User's Guide for further instructions on accessing data using MSQuery, MSExcel, and MSAccess.

 Last Updated: Friday, October 12, 2001

Home