|
BOG DATA SYSTEM : DATA
ACCESS
Table of Contents
 | DOCUMENTATION * |
|
| SYSTEM CONFIGURATION *
 | Setting Up ODBC * |
| Setting Up DSN (Data Source Name) * |
|
|
| APPLICATIONS * |
|
| DATABASE
*
|
|
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
|
