----------------------------------------------- HOSS Data Table Extract - Field Descriptions ----------------------------------------------- The following information relates to the text files found on the State of Maine web site at: http://www.maine.gov/dep/ftp/hoss/ This document was last updated November 2014 by Lyle Hall. Reading the information provided in this file will help you understand the structure of the HOSS data files you can download from the web. A valuable supplemental document is our list of HOSS spill report code descriptions found in the file stored at: http://www.maine.gov/dep/maps-data/documents/hosscodesheet.pdf Questions or suggestions regarding this data set or the documentation can be forwarded to the DEP at: deprwm@maine.gov Please refer to the name of the text file and the HOSS database in the body of your message. This data is exported from an ORACLE 11g data instance and the field types used below come from ORACLE's naming conventions. Each table is generated using a character separated value format, where the astrix (*) character is used as the separator between fields. >>> Start of Data Table and Feild Descriptions ========================= Report_Attachement.txt L.SPILL_NUMBER VARCHAR2 (20) NOT NULL, DESCRIPTION VARCHAR2 (4000), ATTACH_TYPE_CODE VARCHAR2 (5), ATTACH_TYPE_VALUE VARCHAR2 (80), FILE_NAME VARCHAR2 (50), FILE_EXT_CODE VARCHAR2 (20), FILE_SIZE VARCHAR2 (10), FILE_MODIFY_DATE DATE, ========================= Report_Change_Log.txt L.SPILL_NUMBER VARCHAR2 (20) NOT NULL, CHANGE_TYPE_CODE VARCHAR2 (5) NOT NULL, CHANGE_TYPE_VALUE VARCHAR2 (80), CHANGE_DESCRIPTION VARCHAR2 (4000), CHANGE_DATE DATE, CHANGE_BY VARCHAR2 (30), ========================= Report_Contact.txt L.SPILL_NUMBER VARCHAR2 (20) NOT NULL, CONTACT_TYPE_CODE VARCHAR2 (5) NOT NULL, CONTACT_TYPE_VALUE VARCHAR2 (80), POTENTIAL_RP_FLAG VARCHAR2 (1) DEFAULT 'F', FIRST_NAME VARCHAR2 (30), MID_INIT VARCHAR2 (1), LAST_NAME VARCHAR2 (30), TITLE VARCHAR2 (30), COMPANY VARCHAR2 (100), ADDRESS_LINE1 VARCHAR2 (80), ADDRESS_LINE2 VARCHAR2 (80), CITY_TOWN VARCHAR2 (50), STATE_PROVINCE VARCHAR2 (2), COUNTRY VARCHAR2 (40), POSTAL_CODE VARCHAR2 (15), PHONE VARCHAR2 (15), PHONE_EXTENSION VARCHAR2 (10), COMMENTS VARCHAR2 (4000), ========================= Report_Employee.txt L.SPILL_NUMBER VARCHAR2 (20) NOT NULL, PRIMARY_FLAG VARCHAR2 (1) DEFAULT 'F', FIRST_NAME VARCHAR2 (30), MID_INIT VARCHAR2 (1), LAST_NAME VARCHAR2 (30), ========================= Report_Inc_Medium.txt L.SPILL_NUMBER VARCHAR2 (20) NOT NULL, MEDIUM_CODE VARCHAR2 (5) NOT NULL, MEDIUM_CODE_VALUE VARCHAR2 (80), ========================= Report_Material_Recovered.txt L.SPILL_NUMBER VARCHAR2 (20) NOT NULL, MATERIAL_RECOVERED_TYPE VARCHAR2 (5) NOT NULL, MATERIAL_RECOVERED_VALUE VARCHAR2 (80), MATERIAL_AMOUNT NUMBER (11,2), MATERIAL_UNITS VARCHAR2 (5), MATERIAL_UNITS_VALUE VARCHAR2 (80), MATERIAL_AMOUNT_QUALIFIER VARCHAR2 (20), ========================= Report_Prod_Recovery_Method.txt L.SPILL_NUMBER VARCHAR2 (20) NOT NULL, RECOVERY_METHOD_CODE VARCHAR2 (5) NOT NULL, RECOVERY_METHOD_VALUE VARCHAR2 (80), ========================= Report_Product_Found.txt L.SPILL_NUMBER VARCHAR2 (20) NOT NULL, PRODUCT_CODE VARCHAR2 (5) NOT NULL, PRODUCT_CODE_VALUE VARCHAR2 (80), PRODUCT_OTHER VARCHAR2 (80), PRODUCT_AMOUNT NUMBER (11,2), PRODUCT_AMOUNT_UNIT VARCHAR2 (5), PRODUCT_AMOUNT_UNIT_VALUE VARCHAR2 (80), PRODUCT_AMOUNT_QUALIFIER VARCHAR2 (20), PRIMARY_PRODUCT_FLAG VARCHAR2 (1) DEFAULT 'F', ========================= Report_Spill_Point.txt L.SPILL_NUMBER VARCHAR2 (20) NOT NULL, CREATE_DATE DATE, CREATE_BY VARCHAR2 (30), MODIFY_DATE DATE, MODIFY_BY VARCHAR2 (30), POINT_TYPE_CODE VARCHAR2 (5) NOT NULL, UTM_NORTH NUMBER (9,2), UTM_EAST NUMBER (9,2), GPS_UNIT VARCHAR2 (100), GPS_DATE VARCHAR2 (30), GPS_TIME VARCHAR2 (30), GIS_FEATURE_CLASS VARCHAR2 (100), GIS_OBJECT_ID NUMBER, GIS_SYNC_FLAG VARCHAR2 (1) DEFAULT 'F', ========================= Report_Tank_Involved.txt L.SPILL_NUMBER VARCHAR2 (20) NOT NULL, INC_TANK_CODE VARCHAR2 (5) NOT NULL, INC_TANK_VALUE VARCHAR2 (80), REMOVAL_FLAG VARCHAR2 (1) DEFAULT 'F', UST_REGISTERED_FLAG VARCHAR2 (1) DEFAULT 'F', UST_TANK_SITE_NUMBER NUMBER (5), UST_TANK_NUMBER NUMBER (3), AST_INSIDE_FLAG VARCHAR2 (1) DEFAULT 'F', ========================= Spill_File.txt L.SPILL_NUMBER VARCHAR2 (20) NOT NULL, CREATE_DATE DATE, CREATE_BY VARCHAR2 (30), MODIFY_DATE DATE, MODIFY_BY VARCHAR2 (30), FILE_NUM_SHEETS NUMBER (3), FILE_NOTES VARCHAR2 (4000), FILE_RECONCILE_DATE DATE, FILE_RECONCILE_BY VARCHAR2 (30), ========================= Spill_Log.txt L.SPILL_NUMBER VARCHAR2 (20) NOT NULL, SPILL_VOID_FLAG VARCHAR2 (1) DEFAULT 'F', SPILL_OFFICE_CODE VARCHAR2 (5) NOT NULL, SPILL_OFFICE_VALUE VARCHAR2 (80), SPILL_OFFICE_SEQ_NUMBER VARCHAR2 (15) NOT NULL, SPILL_YEAR NUMBER (4) NOT NULL, CREATE_DATE DATE, CREATE_BY VARCHAR2 (30), MODIFY_DATE DATE, MODIFY_BY VARCHAR2 (30), LOG_SPILL_TYPE_CODE VARCHAR2 (5), LOG_SPILL_TYPE_VALUE VARCHAR2 (80), LOG_SPILL_DATETIME DATE, LOG_SPILL_TIME_UNKNOWN_FLAG VARCHAR2 (1) DEFAULT 'F', LOG_SPILL_DATE_UNKNOWN_FLAG VARCHAR2 (1) DEFAULT 'F', LOG_REPORTED_DATETIME DATE, LOG_REPORTED_PRODUCT_CODE VARCHAR2 (5), LOG_REPORTED_PRODUCT_VALUE VARCHAR2 (80), LOG_EMPLOYEE_FIRST VARCHAR2 (30), LOG_EMPLOYEE_MIDDLE_INIT VARCHAR2 (1), LOG_EMPLOYEE_LAST VARCHAR2 (30), LOG_LOCATION_DESCRIPTION VARCHAR2 (4000), LOG_LOCATION_TOWN VARCHAR2 (50), LOG_TANK_INVOLVED_CODE VARCHAR2 (5), LOG_TANK_INVOLVED_VALUE VARCHAR2 (80), LOG_NOTE VARCHAR2 (4000), ========================= Spill_Report_Narrative.txt L.SPILL_NUMBER VARCHAR2 (20) NOT NULL, NARRATIVE CLOB, ========================= Spill_Report.txt L.SPILL_NUMBER VARCHAR2 (20) NOT NULL, CREATE_DATE DATE, CREATE_BY VARCHAR2 (30), MODIFY_DATE DATE, MODIFY_BY VARCHAR2 (30), REPORT_STATUS VARCHAR2 (5), REPORT_STATUS_VALUE VARCHAR2 (80), ACTUAL_SPILL_DATETIME DATE, ACTUAL_SPILL_DATE_UNKNOWN_FLAG VARCHAR2 (1) DEFAULT 'F', ACTUAL_SPILL_TIME_UNKNOWN_FLAG VARCHAR2 (1) DEFAULT 'F', LOCATION_NAME VARCHAR2 (80), LOCATION_DESCRIPTION VARCHAR2 (4000), LOCATION_TOWN_MCD VARCHAR2 (5), LOCATION_TOWN_MCD_VALUE VARCHAR2 (30), LOCATION_TOWN_LOCAL_NAME VARCHAR2 (80), LOCATION_STATE_PROVINCE VARCHAR2 (2), NUMBER_WELLS_AT_RISK NUMBER (3), NUMBER_WELLS_IMPACTED NUMBER (3), DTREE_COMPLETED_FLAG VARCHAR2 (1) DEFAULT 'F', DTREE_DATE DATE, DTREE_CODE VARCHAR2 (15), DTREE_VALUE VARCHAR2 (40), MATERIAL_DISPOSAL_INFO VARCHAR2 (4000), FURTHER_RESPONSE_ACTION_FLAG VARCHAR2 (1) DEFAULT 'F', SPILL_TYPE_CODE VARCHAR2 (5), SPILL_TYPE_VALUE VARCHAR2 (80), REPORTER_TYPE_CODE VARCHAR2 (5), REPORTER_TYPE_VALUE VARCHAR2 (80), DETECTION_METHOD_CODE VARCHAR2 (5), DETECTION_METHOD_VALUE VARCHAR2 (80), INC_LOCATION_CODE VARCHAR2 (5), INC_LOCATION_VALUE VARCHAR2 (80), INC_SOURCE_CODE VARCHAR2 (5), INC_SOURCE_VALUE VARCHAR2 (80), SPILL_CAUSE_CODE VARCHAR2 (5), SPILL_CAUSE_VALUE VARCHAR2 (80), = = = = = = = = = = = = = = >>> End of Data Table and Feild Descriptions = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = Were you to visit our offices, and look up the information on file for HOSS about a spill, you would need a Spill Number. Each spill has it's own unique 'name' comprised of numbers and letters combined together so as to create a unique key which we call the Spill Number. The Spill Number contains the Response Office, Year, and Sequence number. In our ORACLE database system these three fields are used to create a compound primary key. However, as mentioned above, we have incluced in our exported data tables a single unique key feild called L.SPILL_NUMBER. Each of the tables above includes the L.SPILL_NUMBER field, as a way of connecting each table together, within a database structure. ----------------------------------------------- Other Online HOSS Data Resources ----------------------------------------------- With a Spill number, it is quite easy to use either one of two additional online resources to research a spill report. Additionally, each tool allows for broader search parameters, such as Town, or a Date range. Using Internet Explorer 10 or higher, Visit our online library of scanned paper files at: https://fortisportal.maine.gov/fortisportal/DisplayQueryPrompts.aspx?Database=OfficeDocs&Query=DEP_Spills&QuerySet=Portal_Queries&User=Portal.DEP&Password=DEPPortal1 Or You can see a summarized version of a report by visiting: http://www.maine.gov/dep/rwm/hoss/