Data Documentation

The data displayed on RMP Map originates from the U.S. EPA Risk Management Program database and follows this chain of custody:

What We Import

We import the Data Liberation Project's SQLite database into PostgreSQL with minimal transformation:

• Direct table copy — All 50+ tables copied row-for-row
• No merging or deduplication — All submission records preserved
• No schema changes — Column names and types match source
• Null normalization — Empty strings converted to NULL (PostgreSQL best practice)

The One Enrichment: Geocoding Validation

We maintain a separate facility_geocoding table that validates facility-reported coordinates against state bounding boxes. This table is additive—it doesn't modify source data, just supplements it. Coordinates that don't match the facility's stated location are flagged for address-based geocoding.

The EPA RMP database has a key structural characteristic that can create apparent duplicates if not handled correctly.

Two Types of Facility IDs

When a facility submits a new RMP (every 5 years, or when changes occur), it receives a new FacilityID but retains its same EPAFacilityID.

Example: A Facility with Multiple Submissions

The Accident History Problem

Each RMP submission includes the facility's complete accident history. This means the same accident appears in multiple rows with different AccidentHistoryID values (one per submission).

We apply deduplication logic at display time, not at import. This preserves the source data while presenting a coherent view to users.

Strategy: EPAFacilityID as Canonical Identifier

Throughout the application, we treat EPAFacilityID as the "real" facility identifier:

• URLs use EPAFacilityID: /facilities/100000193471
• Search counts unique EPAFacilityID values
• Map markers are deduplicated by EPAFacilityID

Facility Deduplication: Latest Submission Wins

When displaying facility information, we use PostgreSQL's DISTINCT ON to select the most recent submission:

SELECT DISTINCT ON ("EPAFacilityID")
  "FacilityName", "FacilityStr1", "FacilityCity", ...
FROM tbls1facilities
WHERE "EPAFacilityID" = '100000193471'
ORDER BY "EPAFacilityID", "ReceiptDate" DESC

This returns one row per facility—the most recently submitted data.

Accident Deduplication: Unique Events by Date + Time

Since the same accident appears in multiple submissions, we identify unique accidents by their date and time:

-- Get unique accidents for a facility
SELECT DISTINCT ON ("AccidentDate", "AccidentTime")
  "AccidentHistoryID", "AccidentDate", "AccidentTime", ...
FROM tbls6accidenthistory
WHERE "FacilityID" IN (all FacilityIDs for this EPAFacilityID)
ORDER BY "AccidentDate" DESC, "AccidentTime"

Why Date + Time?

We use (AccidentDate, AccidentTime) as the deduplication key because:

1. It identifies the real-world event — Two records with the same date/time at the same facility are the same accident
2. It's always populated — Unlike other fields that may vary between submissions
3. It's stable — The accident date doesn't change across submissions

Note: AccidentHistoryID is not a good deduplication key because each submission generates new IDs for the same historical accidents.

Summary Table

Facility Detail Page

• Current facility info (from most recent submission)
• Count of unique accidents (deduplicated)
• List of all RMP submissions for this facility (for transparency)
• List of accidents (deduplicated by date/time)

Search Results

• One result per physical facility (by EPAFacilityID)
• Accurate accident counts (deduplicated)
• Facility name/address from most recent submission

Map View

• One marker per physical facility
• Popup shows current name and accurate accident counts

RMP Map provides a free, public API for programmatic access to this data. The API returns JSON and requires no authentication.

Key Points

• Deduplication is applied — API responses use the same deduplication logic described above
• EPAFacilityID is canonical — Use this ID to reference facilities, not FacilityID
• Accident counts are accurate — Counts reflect unique events, not submission records
• GeoJSON support — Request format=geojson for map-ready data

Common Endpoints

For complete endpoint documentation, parameters, and examples, see the full API documentation.

API Metadata

All API responses include a _meta block with data provenance information:

{
  "_meta": {
    "source": "U.S. EPA Risk Management Program via Data Liberation Project",
    "license": "CC BY-SA 4.0",
    "disclaimer": "Data as reported by facilities to EPA"
  }
}

See API Terms of Use for rate limits and attribution requirements.