C.H.E.M. Group

Chemical Hazard Emergency Management: GIS Solutions

 

 

The following data files were provided to us by the City of Victoria, Texas, GIS Department: 

StreetCenterline.shp

CountyBoundry.shp

CityLimit.shp

Fire_Stations.shp

Hospitals.shp

Police_Stations.shp

Schools.shp

Data from other sources:

The cities of Victoria County shape file was an extraction from the USACities.zip file downloaded from TIGER files on the US Census website.

Victoria County T-2.zip which is the database that functions within the Tier2 software.

Figure 1: All data on chemical facilities was extracted from the database that functions within this Tier2 software. Facilities that contain hazardous chemicals must submit, according to law, a Tier2 report.

Receiving Data

Creating Data

First, Excel tables were extracted from the Tier 2 reporting database.  Theses included a chemical inventory, emergency contact information, facility locations, chemical mixture inventory, and facility IDs.

The extracted data in its raw form needed to be formatted and compiled to suit the purpose of the project.  For example, addresses for the facilities and latitude/longitude coordinates needed to be researched and verified. Also, columns for the ERG information (ERG guide number, IPM, Large Spill, and Fire) needed to be inserted into the Chemicals Inventory table, which we created from Tier II data.

Each chemical was located within the Emergency Response Guidebook (See Figure 2), and the guide number for each chemical was entered into the Chemical Inventory table. 

Figure 2: The Emergency Response Guidebook (ERG) was created by the US Department of Transportation, Transport Canada, and the Secretariat of Communications and Transportation of Mexico (SCT) for use by firefighters, police, and other emergency services personnel who may be the first to arrive at the scene of a transportation incident involving a hazardous material. It helps responders to (1) quickly identify the specific or generic classification of the material(s) involved in the incident, and (2) protect themselves and the general public during this initial response phase of the incident.

Now that information about the facilities (contact information, location) had been successfully extracted and combined from the Tier II reports, we began to utilize GIS software– ArcMap. Using ArcMap’s ’ArcCatalog’ tool- ‘Create Feature Class from XY table’, a shapefile was created from the Victoria_Facilities table, which displayed all of the chemical facilities in Victoria County in ArcMap (Figure 5). A satellite image file of Victoria County was downloaded to verify the accuracy of the chemical facility coordinates. An ArcMap process called ’geocoding’ takes textual coordinates and matches them with physical locations in space.

Our Chemical Inventory Table (now containing 3 additional columns describing the protective distances for each chemical) was then joined to the Victoria Facilities table, followed by being joined to the Victoria_Facilities shape file.  The ERG individual guides were converted to pdf files and hyperlinks to the applicable individual Guides were established for each facility. A ‘Table 1’ Excel table was created to be able to highlight, within the GIS, the hazardous chemicals, toxic chemicals, and water reactive chemicals.  Hyperlinks were also established for facilities that had applicable Table 1 chemicals (Figure 6), and for any facilities that had site plans (Figure 7).

After some facilities were eliminated from the project due to their location not being within the Victoria County area, a Tier II ‘contacts’ table was joined to a Tier II ‘phone’ table via the ‘ContactRecordID’ column in the ‘contacts’ table and the ‘ParentRecordID’ column in the Tier II ‘phone’ table. Click on Figure 3 for a .pdf of this join.

The resulting table was joined again with the Tier II ‘ContactsLink’  table via the ‘ContactRecordID’ column.  The resulting table was exported to a database table, converted to an excel table, and named Contact_Phone_Listing.xls.  It was important to join the ‘Tier2ContactsLink’ table because the OtherRecordID column will create a join to the facilities table. 

The Contact_Phone_Listing table was then joined to the facilities table and the resulting table was named Victoria_Facilities. Click on Figure 4 for an image of this join.

Figure 3: Tier II contact join

Figure 4: Tier II facilities join

Figure 5: Victoria County chemical facilities shapefile

Figure 8: Buffer zones for one chemical facility (Invista) on top of a satellite image of Victoria County

Figure 7: Adding hyperlinks to ERG guide pages, Table 1 pages, and site plans in ArcMap

Figure 6: Example of an ERG Table 1 guide

Now, shapefiles for each individual facility were created from the Victoria Facilities table.  This was done in order to next create individual buffer shape files (Figure 8) for each facility rather than one set of buffers for all facilities.  One set of buffers was not efficient in determining safety distances for individual facilities, as the buffers overlapped and were difficult to distinguish. The buffer zone distances were derived from the Chem_Inv_Dist.xls table.

 

Our final results can be seen here.