BTS Home > Programs > Maritime Data Working Group > Marine Safety Management System

Front Matter

Introduction

The U.S. Coast Guard has arranged with the National Technical Information Service (NTIS) for the distribution of the Coast Guard’s marine casualty, personnel injury/death, and pollution database. The Coast Guard furnishes NTIS with copies of the database and documentation on CD-ROM each quarter. The database is transferred to NTIS approximately three (3) weeks following the end of each calendar year quarter. The CD-ROM contains forty data tables derived from marine casualty and pollution investigations from the U. S. Coast Guard Marine Safety Information System (MSIS). Nine data files have been added to include information from the Marine Information for Safety and Law Enforcement (MISLE) system. Questions concerning data should be directed to Mr. Harold Krevait at (202)267-6833.

U.S. Coast Guard Headquarters received quarterly extracts from the Marine Safety Information System (MSIS). MSIS was a network database residing in Martinsburg, West Virginia. Investigation cases were entered at each marine safety unit by Coast Guard investigators. Analysis of data is conducted at Coast Guard Headquarters using a 4GL Relational Database Management System.

On December 21, 2001, the U.S. Coast Guard deployed the MISLE computer system. MISLE is the replacement system for the MSIS. A separate document with information on MISLE tables is included on the CD-ROM. Please refer to the document MisleReadMe.doc for more information.

The purpose of this document is to define the entities and attributes of the database, provide a conceptual view of the data, and suggest solutions to general marine pollution, vessel casualty, and personnel injury and death questions. Documentation explaining vessel casualties and personnel injuries and deaths from 1980 to 1991 and marine pollution incidents from 1973 to 1991 is located in the CASMAIN_DICTIONARY.doc and pirs.doc dictionaries, respectively.

ENTITY DEFINITION

Excel | CSV

ASCII character set
Columns are tab delimited
Data extracted from the U.S. Coast Guard's Marine Safety Information System.
Data is current through December 13, 2001.

Excel | CSV

Abstract

In 1980, the U.S. Coast Guard deployed an automated system of records to report on marine pollution discharges in and around the U.S. This system of records known as the pollution incident reporting system(PIRS) supported the investigation of marine pollution incidents. PIRS was replaced in 1985 with the preliminary marine investigation module(Pre-MINMod).

Deployment of the preliminary marine investigation module was launched on
1 October, 1985. The Pre-MINMod was designed to support the reporting and investigation of marine pollution incidents and serve as the replacement system for the pollution incident reporting system (PIRS). The module contains products that capture significant information on pollution investigation cases.

The PIRS and Pre-MINMod systems operated in parallel from 1985 to 1991. An effort was undertaken in 1997 to consolidate the two systems into one computer system. The data transfer resulted in loss of some key data elements. The data in vessel name, vessel type, facility name and facility type were lost. The data reservoirs for the missing information are located in mpsf70.txt, mpsf80.txt, mpsv70.txt, and mpsv80.txt.

In 1992, the pollution fragment module and the commercial vessel casualty fragment(not included on the CD-ROM) were combined into the Marine Investigation module(MINMod). The module captures extensive information on marine casualties resulting in injury or death, vessel mishaps and pollution incidents.

Information on commercial vessel casualties, injuries, and deaths, before 1992, is stored in the CASMAIN database. The database was developed in 1980 to collect, organize, and report on commercial vessel accidents and personnel injuries and deaths. CASMAIN contains two single table structures: vcas and pcas. Many of the data elements employ a coding scheme to define facts. A separate instruction in included on the CD-ROM to add clarity to the data.

In 1994, the Coast Guard began issuing notice of violations(tickets) to parties know to have discharged 100 gallons or less of oil into the navigable waters of the United States. The notice of violation program accounts for 50 percent of the marine pollution incidents in the U.S.--but only a fraction of the quantity of oil discharged into U.S. waters. Ticket cases account for three(3) percent of the volume of oil spilled in U.S. waters. Oil spills over 100 gallons or other substances discharged into the navigable waters of the U.S. are stored in the MINMod database.

CONVERTA.TXT

The marine pollution database contains three separate pollution tables on the amounts and substances discharged into the waters of the U.S. Units of measure were often recorded in units other than pounds or gallons. To simplify and standardize the units of measure and reduce the number of joins to other tables, the Database Administration Division created the converta.txt table. Records from psst.txt, cpdt.txt, and ticket cases were combined into a single data store. Developers of converta.txt created algorithms to convert all liquid measures to gallons and solid measures to pounds.

SAMPLE SOLUTIONS

OIL SPILLS INTO U.S. WATERS BETWEEN 1985 AND 2001 FROM VESSEL SOURCES

MINMod(1992-2001)

Begin with the cirt.txt table. Cirt.txt contains general information on spill incidents, i.e., spill date and time, location, waterbody, and total damage(vessel damage only.) Cirt.txt links to the civt.txt(vessels) table through the primary key column mccase. The civt.txt table contains the service of vessel, vessel name, official number, flag state, and other vessel specific data elements for those vessels involved in a vessel casualty, personnel injury or death, or marine pollution incident.

Next, join the results of the query above to the converta.txt table. The table contains spill details(quantity spilled into water, quantity spilled out of water, substance spilled, units of measure, etc.) Join the civt.mccase and civt.subject to converta.Cases and converta.Supid, respectively. To restrict the query to oil spill incidents, add converta.Type = "P"(Petroleum) to your SQL statement.

Subject Number

The subject column plays a key role in most join operations. The subject number is a unique identifier for each vessel or facility involved in a pollution incident, vessel casualty, or personnel injury or death case. When more than one vessel or facility is involved in a case, the subject number insures the correct substance(s), injured or dead party(s), or casualty event(s) is/are credited to the proper vessel.

Ticket Cases(late 1994 to 2001)

The purpose of this step is to include ticket cases in your result set. You may skip this section, if you are not interested in ticket cases. The fundamental data in ticket cases can be found in the prittk.txt table. The service of vessel and flag state is stored in the mvcttk.txt file. To view this information, join the prittk.txt table to the mvcttk.txt table. Prittk.mpcase is the primary key field; mvcttk.mvcase is the foreign key. Mvcttk.txt contains spills from vessels and facilities. To exclude spills from sources other than vessels, test the column service for null values. If the service column contains a non-null value, then the source of the spill is from a vessel.

Next, join the converta.txt table using prittk.mpcase to converta.Cases. You will not have to use the subject or Supid as you did in the MINMod section. Ticket cases can have more than one substance spilled, but only one vessel will be involved in a spill case. Note: you will not have to use converta.Type = “P” in the query, since ticket cases are violations for petroleum discharges.

Lastly, union the results of the MINMod and Ticket queries together to obtain the quantity and number of oil spill incidents for 1992 to 2001.

Pre-MINMod(October 1985 to 1991)

For spills from 1985 to 1991, select the prit.txt table. This table is comparable with the cirt.txt table in content. Execute a join between prit.txt and pvst.txt tables on prit.mpcase and pvst.mpcase. This is the same step used in the MINMod query above.

Next, join the pvst.txt table to converta.txt table on pvst.mpcase and pvst.supplement_id to converta.Cases and converta.Supid, respectively. Include converta.Type = “P” for petroleum discharges in the SQL statement.

Below are SQL solutions to determine the quantities and substances spilled from vessel sources in the MINDMod, ticket and Pre-MINMod database. The query solutions presented here are written in ANSI89 structured query language.

MINMod(1992-2001)

/*incident_dt=date of spill*/
/*vname=vessel name*/
/*service=service of vessel */
/*waterbody=body of water(you can substitute latitude and longitude)*/
/*Spin=quantity spilled into water*/
/*Spout=quantity spilled out of water(from land sources or containment spills)*/
/*Name=substance name*/
/*Measure=units of measure(gallons, pounds)*/
/*total_damage=vessel damage only. Does not include cost of cleanup and damage to the environment*/

select incident_dt, vname, service, waterbody, Spin, Spout, Name, Measure, total_damage
from cirt, civt, converta
where cirt.mccase=civt.mccase and
     civt.mccase=converta.Cases and
     civt.subject=converta.Supid and
     Type = "P"                            /* Petroleum category */

Tickets(late 1994- 2001)

/*spdt=date of spill*/
/*subj_name=vessel name */
/*service=service of vessel*/
/*water=body of water*/
/*Spout=quantity spilled in water*/ /*see note on converta.txt*/
/*Name=substance name*/
/*Measure=units of measure*/ /*units are in gallons*/
/*damage to vessel and cargo are not collected in this database*/

select spdt, subj_name, service, water, Spout, Name, Measure
from prittk, mvcttk, converta
where prittk.mpcase=mvcttk.mvcase and
     service is not null and
     prittk.mpcase=converta.Cases and
     Type = "P"

Pre-MINMod(1985-1991)

select spdt, vname, service, water, Spin, Spout, Name, Measure
from prit, pvst, converta
where prit.mpcase=pvst.mpcase and
     pvst.mpcase=converta.Cases and
     pvst.supplement_id=converta.Supid and /*Supid and supplement_id are numeric abbreviations for a vessel*/
     Type = “P”

Finally, union the results of these queries together.

These queries will generate details on marine petroleum spills (from vessel sources) in and around the United States. Of course, tens of tens of thousands of records will be generated from these queries. You can import the results into your favorite spreadsheet or store them back into the database for further processing. Note: Spreadsheets have a limitation on the number of records that can be stored.

Cleanup costs are of great interest to our customers. To obtain the cost of a cleanup operation, we have included the ttl_cost column in the prit.txt table. These data were collected for the years 1985 through 1991. The ttl_cost column contains funds expended to clean up a spill. This column is only used when a spill is federalized. In most cases, the responsible party for the spill pays the cost of the clean up. To obtain 1992-2001 federalized clean-up costs, contact the National Pollution Fund Center at (703)235-4740.

In the years between 1973 and 1991, clean-up costs were reported in the tcost column. Funds expended by the federal government in a clean-up operation are recorded in the expend column. The responsible party for the clean up is registered in the rempty column. The values for rempty can be found in the 1973 through 1991 marine pollution incident reporting system coding instruction.

Vessel Identification Information

Detail vessel information can be obtained by querying the vidt.txt table. The table contains foreign and domestic vessels entering into or operated in the U.S. and certain recreational vessels. The data collection period began in 1984 and continues to this day. The table can be joined to the MINMod, Pre-MINMod, Ticket and other Coast Guard databases wherever vessel keys(vkey) or vessel identification numbers(vins) are known. The Primary key in vidt.txt is vkey. The alternate key is vin.

Facility Identification Information

Detail facility information can be obtained by querying the fidt.txt table. The table contains details on facilities inspected by the Coast Guard, facilities involved in a marine casualty or facilities involved in a pollution incident. Facilities can include: railroad cars, automobiles, aircraft, mobile tank trucks, bridges, or other non-vessel sources. The data collection period began in 1984 and continues to this day. The table can be joined to the MINMod, Pre-MINMod, Ticket and other Coast Guard databases wherever facility keys(fkey) or facility identification numbers(fins) are known. The Primary key in fidt.txt is fkey. The alternate key is fin.

ADDITIONAL SQL SOLUTIONS TO GENERAL VESSEL CASUALTY AND PERSONNEL INJURY AND DEATH QUESTIONS.

SQL SOLUTION FOR COMMERCIAL VESSEL GROUNDINGS FROM 1992 TO 2001

/*vin=vessel identification number*/
/*vname=vessel name*/
/*service=service of vessel(TANK SHIP, FREIGHT SHIP, etc.*/
/*incident_dt=date of casualty*/
/*type=nature of event*/
/*class=defines event*/
/*cevt.state=defines class*/ /*Note state appears in cirt and cevt. You must qualify the field with the table name to avoid ambiguous references.*/

select vin, vname, service, incident_dt, type, class, cevt.state
from cirt, civt, cevt
where cirt.mccase=civt.mccase and
     civt.mccase=cevt.mccase and
    (civt.subj_ref_num=cevt.subject1 or  /*subj_ref_num, subject1, subject2 are numeric abbreviations for a vessel*/      
     civt.subj_ref_num=cevt.subject2) and
     type in (“GROUNDING ACC”, “GROUNDING INT”) /*GROUNDINGS ACCIDENTAL AND INTENTIONAL*/

SQL SOLUTION FOR COMMERCIAL VESSEL GROUNDINGS FROM 1980 TO 1991

/*vin=vessel identification number*/
/*vname=vessel name*/
/*service=service of vessel*/
/*case_date=date of casualty*/
/*nature1=primary event in the vessel casualty*/

select vin, vname, service, case_date, nature1
from vcas
where nature1 in (“GRNDGA”, “GRNDGI”)        /*GROUNDINGS ACCIDENTAL AND INTENTIONAL*/

SQL SOLUTION FOR DETAILS ON PERSONNEL INJURIES AND DEATHS FROM 1992 TO 2001

/*vin=vessel identification number*/
/*vname=vessel name*/
/*incident_dt=date of casualty*/
/*injury=”X” if injured*/
/*dead=”X” if death*/
/*missing=”X” if missing*/  /*Note missing appears in cirt and cpct. You must qualify the field with the table name to avoid ambiguous references.*/

/*accident_typ=type of accident*/

select vin, vname, incident_dt, injury, dead, cpct.missing, accident_typ
from cirt, civt, cpct
where cirt.mccase=civt.mccase and
     civt.mccase=cpct.mccase and
     civt.subject=cpct.subject        /*subject is a numeric abbreviation for a vessel*/

SQL SOLUTION FOR DETAILS ON PERSONNEL INJURIES AND DEATHS FROM 1980 TO 1991

/*vin=vessel identification number*/
/*vslname=vessel name*/
/*case_date=date of casualty*/
/*result=injury, death, missing with/without vessel casualty*/
/*acc_id=type of accident*/

select vin, vslname, case_date, result, acc_id
from pcas