pa-gov/crash-incident-details-cy-1997-current-annual-dc5b-gebx
Icon for Socrata external plugin

Query the Data Delivery Network

Query the DDN

The easiest way to query any data on Splitgraph is via the "Data Delivery Network" (DDN). The DDN is a single endpoint that speaks the PostgreSQL wire protocol. Any Splitgraph user can connect to it at data.splitgraph.com:5432 and query any version of over 40,000 datasets that are hosted or proxied by Splitgraph.

For example, you can query the crash_incident_details_cy_1997_current_annual table in this repository, by referencing it like:

"pa-gov/crash-incident-details-cy-1997-current-annual-dc5b-gebx:latest"."crash_incident_details_cy_1997_current_annual"

or in a full query, like:

SELECT
    ":id", -- Socrata column ID
    "young_driver", -- The crash involved at least 1 driver age 16-20
    "child_passenger", -- The Crash involved at least one vehicle passenger under the age of 12.
    "urban", -- The crash took place in an Urban municipality
    "single_vehicle", -- The crash involved a single vehicle
    "opioid_related", -- At least one Driver or Non-Motorist was suspected of drug use and tested positive for opioids
    "multiple_vehicle", -- Crash involved at least 2 vehicles
    "limit_70mph", -- The Crash took place on a roadway that had a posted Speed limit of 70 Miles Per Hour
    "core_network", -- Crash took place on a Core Network Roadway.
    "atv", -- Crash involved at least one All-Terrain-Vehicle (ATV).
    "angle_crash", -- First Harmful Event involved a vehicle striking another at an angle
    "roadway_cleared", -- Time the roadway was opened to traffic
    "work_zone_ind", -- Did the crash occur in a work zone
    "relation_to_road", -- Code for the crash’s relativity to the road
    "chldpas_death_count", -- Total child passengers under the age of 8 killed in the crash
    "location_1_address",
    "location_1", -- This is a georeferenced latitude and longitude point for the location of the crash. This point can also be used to create visualizations such as maps. 
    "impaired_driver", -- At least one driver was impaired by drugs or alcohol. This flag is similar to drug_related, but it includes both alcohol and drugs and it only applies to drivers.  It defines whether the crash involved a driver suspected of using drugs or alcohol or was actually tested and had drugs or alcohol in their system.  If a driver is suspected and were tested, but the test results show no drugs, this situation would not be included.
    "drugged_driver", -- At least one driver with drugs reported or suspected. This flag is similar to drug_related, but it only applies to drivers.  It defines whether the crash involved a driver suspected of using drugs or was actually tested and had drugs in their system.  If a driver is suspected and were tested, but the test results show no drugs, this situation would not be included.
    "drug_related", -- At least one driver or pedestrian with drugs reported or suspected. This is a flag that defines whether the crash involved a driver or pedestrian was suspected of using drugs or was actually tested and had drugs in their system.  If a driver or pedestrian is suspected and were tested, but the test results show no drugs, this situation would not be included.
    "minor_injury", -- At least 1 person sustained a possible injury. Possible Injury: Any injury reported or claimed which is not a fatal, suspected serious or suspected minor injury. Examples include momentary loss of consciousness, claim of injury, limping, or complaint of pain or nausea. Possible injuries are those which are reported by the person or are indicated by his/her behavior, but no wounds or injuries are readily evident. *Note: In 2016, the injury severity descriptions and definitions changed to match federal standards.
    "major_injury", -- At least 1 person sustained a suspected serious injury. Suspected Serious Injury: Any injury other than fatal which results in one or more of the following: severe laceration, significant loss of blood, broken or distorted extremity, crush injuries, suspected skull, chest or abdominal injury, significant burns, unconsciousness, or paralysis. *Note: In 2016, the injury severity descriptions and definitions changed to match federal standards.
    "hazardous_truck", -- At least one heavy truck carrying hazardous material
    "vehicle_towed", -- At least 1 vehicle was towed from the scene
    "hit_parked_vehicle", -- At least one legally or illegally parked vehicle was struck
    "trolley", -- Trolley indicator
    "running_stop_sign", -- At least one driver ran a stop sign
    "deer_related", -- Deer struck or deer in roadway
    "nhtsa_agg_driving", -- The crash meets the National Highway Traffic Safety Administration (NHTSA) definition of aggressive driving
    "psp_reported", -- Crash investigated by the Pennsylvania State Police
    "comm_vehicle", -- Crash has at least 1 involved commercial vehicle
    "unbelted", -- At least one person in crash unbelted
    "driver_65_74yr", -- At least one driver between 65 and 74 years of age
    "driver_50_64yr", -- At least one driver between 50 and 64 years of age
    "driver_20yr", -- At least one driver 20 years of age
    "driver_19yr", -- At least one driver 19 years of age
    "driver_17yr", -- At least one driver 17 years of age
    "fatigue_asleep", -- Fatigue or asleep indicator
    "aggressive_driving", -- At least one aggressive driver action
    "speeding", -- Speeding indicator
    "limit_65mph", -- 65 miles per hour (MPH) speed limit indicator
    "curve_dvr_error", -- At least one driver action involving curve negotiation
    "cross_median", -- Cross median indicator
    "tailgating", -- Tailgating indicator
    "running_red_lt", -- Driver running red light indicator
    "distracted", -- At lease one driver action indicating a distraction. Distracted Driving: any activity that could divert a person's attention away from the primary task of driving.  Examples of distracted driving include, but are not limited to texting, eating, grooming, talking to passengers, etc.
    "unlicensed", -- Unlicensed driver indicator
    "alcohol_related", -- At least one driver or pedestrian with reported or suspected alcohol use. Alcohol-Related Crash: Any reportable crash in which one or more of the drivers was reported to have been drinking, or a drinking pedestrian was involved.
    "phantom_vehicle", -- Phantom vehicle indicator
    "train_trolley", -- Train or trolley indicator
    "hvy_truck_related", -- Heavy truck related indicator
    "motorcycle", -- Motorcycle indicator
    "hit_bridge", -- Hit bridge indicator
    "hit_barrier", -- Hit barrier indicator
    "hit_gdrail", -- Hit guide rail indicator
    "hit_embankment", -- Hit embankment indicator
    "hit_deer", -- Hit deer indicator
    "stop_controlled_int", -- Stop controlled intersection indicator
    "intersection", -- Intersection indicator
    "non_intersection", -- Non intersection indicator
    "fatal_or_maj_inj", -- Fatality or major injury indicator. Suspected Serious Injury: Any injury other than fatal which results in one or more of the following: severe laceration, significant loss of blood, broken or distorted extremity, crush injuries, suspected skull, chest or abdominal injury, significant burns, unconsciousness, or paralysis. *Note: In 2016, the injury severity descriptions and definitions changed to match federal standards.
    "injury_or_fatal", -- At least 1 person was injured or killed in the crash indicator
    "property_damage_only", -- Property damage only indicator
    "work_zone", -- Work zone indicator
    "hit_fixed_object", -- Hit fixed object indicator
    "rear_end", -- Rear end collision indicator
    "shldr_related", -- Shoulder related indicator
    "sudden_deer", -- Sudden deer indicator
    "icy_road", -- Icy road indicator
    "wet_road", -- Wet road indicator
    "interstate", -- Non-turnpike interstate indicator
    "rdwy_surf_type_cd", -- Identifies the roadway surface type - ONLY FOR FATAL CRASHES
    "dec_long", -- Decimal format of the longitude of the crash location
    "dec_lat", -- Decimal format for the latitude of the crash location
    "comm_veh_count", -- Total commercial vehicles involved in the crash
    "max_severity_level", -- Injury severity level of the crash (0 - Not Injured, 1 - Fatal Injury, 2 - Suspected Serious Injury, 3 - Suspected Minor Injury, 8 - Unknown Severity, 9 Unknown if injured)
    "ped_maj_inj_count", -- Total number of pedestrians sustaining suspected serious injuries. Suspected Serious Injury: Any injury other than fatal which results in one or more of the following: severe laceration, significant loss of blood, broken or distorted extremity, crush injuries, suspected skull, chest or abdominal injury, significant burns, unconsciousness, or paralysis. *Note: In 2016, the injury severity descriptions and definitions changed to match federal standards.
    "ped_count", -- Total Pedestrians involved in the crash
    "bicycle_maj_inj_count", -- Total number of bicyclists sustaining suspected serious injuries. Suspected Serious Injury: Any injury other than fatal which results in one or more of the following: severe laceration, significant loss of blood, broken or distorted extremity, crush injuries, suspected skull, chest or abdominal injury, significant burns, unconsciousness, or paralysis. *Note: In 2016, the injury severity descriptions and definitions changed to match federal standards.
    "mcycle_maj_inj_count", -- Total number of motorcyclists sustaining suspected serious injuries. Suspected Serious Injury: Any injury other than fatal which results in one or more of the following: severe laceration, significant loss of blood, broken or distorted extremity, crush injuries, suspected skull, chest or abdominal injury, significant burns, unconsciousness, or paralysis. *Note: In 2016, the injury severity descriptions and definitions changed to match federal standards.
    "belted_maj_inj_count", -- Total number of belted persons sustaining suspected serious injuries. Suspected Serious Injury: Any injury other than fatal which results in one or more of the following: severe laceration, significant loss of blood, broken or distorted extremity, crush injuries, suspected skull, chest or abdominal injury, significant burns, unconsciousness, or paralysis. *Note: In 2016, the injury severity descriptions and definitions changed to match federal standards.
    "unb_maj_inj_count", -- Total number of unbelted people sustaining suspected serious injuries. Suspected Serious Injury: Any injury other than fatal which results in one or more of the following: severe laceration, significant loss of blood, broken or distorted extremity, crush injuries, suspected skull, chest or abdominal injury, significant burns, unconsciousness, or paralysis. *Note: In 2016, the injury severity descriptions and definitions changed to match federal standards.
    "unb_death_count", -- Number of people killed not wearing a seatbelt
    "driver_count_75plus", -- Total number of drivers over 75 years of age involved in the crash
    "driver_count_50_64yr", -- Total number of drivers age 50 to 64 involved in the crash
    "driver_count_17yr", -- Total number of 17 year old drivers involved in the crash
    "unk_inj_per_count", -- Total number of people that it is unknown if they were injured during the crash
    "unk_inj_deg_count", -- Total count of injuries with unknown severity sustained during the crash
    "tot_inj_count", -- Total amount of all injuries sustained in the crash
    "mod_inj_count", -- Total amount of suspected minor injuries sustained in the crash. Suspected Minor Injury: Any injury that is evident at the scene of the crash, other than fatal or serious injuries. Examples include lump on the head, abrasions, bruises, minor lacerations (cuts on the skin surface with minimal bleeding and no exposure of deeper tissue/muscle). *Note: In 2016, the injury severity descriptions and definitions changed to match federal standards.
    "maj_inj_count", -- Total amount of suspected serious injuries sustained in the crash.  Suspected Serious Injury: Any injury other than fatal which results in one or more of the following: severe laceration, significant loss of blood, broken or distorted extremity, crush injuries, suspected skull, chest or abdominal injury, significant burns, unconsciousness, or paralysis.  *Note: In 2016, the injury severity descriptions and definitions changed to match federal standards.
    "van_count", -- Total amount of vans involved in the crash
    "suv_count", -- Total amount of sport utility vehicles (SUV) involved in the crash
    "heavy_truck_count", -- Total amount of heavy trucks involved in the crash
    "small_truck_count", -- Total amount of small trucks involved in the crash
    "bus_count", -- Total amount of buses involved in the crash
    "automobile_count", -- Total amount of automobiles involved in the crash
    "vehicle_count", -- Total number of all motor vehicles involved in the crash
    "wz_other", -- Identifies if it was a special type of work zone
    "wz_moving", -- Identifies if there was moving work in the zone
    "wz_ln_closure", -- Identifies if the work zone had a lane closure
    "wz_flagger", -- Identifies if the work zone had a flagman
    "wz_close_detour", -- Identifies if traffic was rerouted due to a work zone
    "tcd_func_cd", -- Defines the state of the traffic control device
    "spec_juris_cd", -- Defines any special jurisdiction - ONLY FOR FATAL CRASHES
    "ln_close_dir", -- Direction of traffic in closed lane(s)
    "lane_closed", -- Identifies if there was any lane closures due to the crash
    "est_hrs_closed", -- The estimated number of hours the roadway was closed due to the crash
    "cons_zone_spd_lim", -- The speed limit for the construction zone if the crash occurred in a construction zone
    "dispatch_tm", -- Time police were dispatched to the scene of the crash
    "arrival_tm", -- Time Police arrived at the scene (24 hour format)
    "longitude", -- Longitude of the crash location
    "latitude", -- Latitude of the crash location
    "sch_zone_ind", -- Identifies if the crash occurred in a school zone
    "total_units", -- Total count of all vehicles and pedestrians involved in the crash
    "person_count", -- Total number of people involved in the crash
    "injury_count", -- Total count of all injuries sustained in the crash
    "fatal_count", -- Total amount of fatalities involved in the crash
    "urban_rural", -- Classifies the crash as urban or rural based on the municipality
    "location_type", -- Defines the crash location
    "tcd_type", -- Defines the traffic control device for the location of the crash if applicable
    "intersect_type", -- Defines the type of intersection at the location of the crash
    "work_zone_loc", -- Defines the location of the work zone
    "work_zone_type", -- Defines the type of work zone
    "collision_type", -- Defines the collision category of the crash.  Non-Collision: A harmful event that does not involve a collision with a fixed object or a nonfixed object. These events include explosion, fire, overturn, immersion and vehicle struck by flying object. Angle: A crash in which two vehicles on opposite roadways collide at a point of junction, such as a road intersection, driveway, or entrance ramp. Rear-End: A crash in which vehicles traveling in the same direction, on the same road, collide (vehicle front into vehicle rear). Head-On: A crash in which vehicles traveling in opposite directions, on the same road, collide (vehicle front into vehicle front). Sideswipe: A crash between two vehicles (traveling in same direction or opposite direction) in which the sides of both vehicles engage. Hit Fixed Object: A collision in which a vehicle collides with stationary object(s) along and adjacent to the roadway, (i.e. bridge piers, trees, utility poles, embankment, guiderail, etc.). Hit Pedestrian: A collision between a motor vehicle and any person(s) not in or upon the vehicle.
    "road_condition", -- Defines the roadway surface condition at the time of the crash
    "weather", -- Defines the weather type at the time of the crash
    "hour_of_day", -- The hour of the day when the crash occurred (24 hour format)
    "time_of_day", -- The time of day when the crash occurred (24 hour time)
    "day_of_week", -- Day of the week code when crash occurred (1 = Sunday)
    "crash_month", -- Calendar month when the crash occurred
    "crash_year", -- Calendar year when the crash occurred
    "police_agcy", -- Code of the reporting Police Agency
    "district_name", -- The name of the district where the crash occurred (based on the County)
    "municipal_name", -- Name of the municipality where the crash occurred
    "municipality", -- Municipality code where the crash occurred
    "county_name", -- County name where the crash occurred
    "county", -- County code number where the crash occurred
    "crn", -- Key identifier for a crash for all crash related data sets.
    "agency_name", -- Name of the reporting Police Agency
    "other_freeway_expressway", -- Indicates that the crash took place on a non-turnpike/non-interstate freeway
    "nonmotr_susp_serious_inj_count", -- Total number of Non-motorists with suspected serious injures in the crash
    "nonmotr_death_count", -- Total number of Non-motorists killed in the crash
    "nonmotr_count", -- Total number of Non-motorists involved in the crash
    "horse_buggy", -- At least one Horse and Buggy Unit involved
    "driver_16yr", -- At least one driver 16 years of age
    "chldpas_susp_serious_inj_count", -- Total child passengers under the age of 8 with suspected serious injuries
    "backup_nonrecurring", -- Indicates that traffic was backed up due to a Nonrecurring special event
    ":@computed_region_amqz_jbr4",
    ":@computed_region_r6rf_p9et",
    ":@computed_region_rayf_jjgk",
    ":@computed_region_d3gw_znnf",
    ":@computed_region_nmsq_hqvv",
    "ramp", -- The crash involved an interchange ramp
    "mature_driver", -- The crash involved at least 1 driver over the age of 65
    "snowmobile", -- Crash involved at least one Snowmobile Unit
    "rural", -- Crash took place in a rural municipality
    "local_road", -- The crash involved at least one Local Road
    "left_turn", -- The crash had at least 1 unit that performed a left turn movement.
    "lane_departure", -- The crash had an indication that at least one vehicle departed their lane of travel during the crash events
    "backup_prior", -- Indicates that traffic was backed up due to a prior crash
    "backup_congestion", -- Indicates that traffic was backed up due to normal congestion
    "intersection_related", -- Was this midblock crash related to a nearby intersection?
    "secondary_crash", -- Was this crash caused at least in part to a prior crash?
    "wz_workers_inj_killed", -- Were any Work Zone workers injured or killed as a result of this crash?
    "location_1_zip",
    "horse_buggy_count", -- Total Number of Horse and Buggy Units involved in the Crash
    "location_1_state",
    "location_1_city",
    "school_bus_unit", -- School bus unit indicator
    "illegal_drug_related", -- At least one driver or pedestrian had reported or suspected illegal drug use. This flag is similar to drug_related, but it only applies to illegal drugs.  It defines whether the crash involved a driver or pedestrian suspected of using illegal drugs.  If a driver is suspected and were tested, but the test results show no drugs, this situation would not be included.
    "mc_drinking_driver", -- At least 1 motorcycle driver has reported or suspected alcohol use
    "moderate_injury", -- At least 1 person sustained a suspected minor injury. Suspected Minor Injury: Any injury that is evident at the scene of the crash, other than fatal or serious injuries. Examples include lump on the head, abrasions, bruises, minor lacerations (cuts on the skin surface with minimal bleeding and no exposure of deeper tissue/muscle). *Note: In 2016, the injury severity descriptions and definitions changed to match federal standards.
    "fire_in_vehicle", -- At least 1 vehicle with fire damage
    "train", -- Train indicator
    "illumination_dark", -- Illumination indicates that the crash scene lighting was dark
    "pedestrian", -- Pedestrian indicator
    "driver_75plus", -- At least one driver 75 plus years of age
    "driver_18yr", -- At least one driver 18 years of age
    "speeding_related", -- Speeding related indicator
    "curved_road", -- Curve in road
    "no_clearance", -- No clearance indicator
    "cell_phone", -- Driver using cell phone (hand held or hands free)
    "underage_drnk_drv", -- Underage drinking driver indicator
    "drinking_driver", -- Drinking driver indicator
    "vehicle_failure", -- Vehicle failure indicator
    "bicycle", -- Bicycle indicator
    "state_road", -- State road indicator
    "ped_death_count", -- Total pedestrian fatalities
    "bicycle_death_count", -- Total amount of bicyclist fatalities
    "mcycle_death_count", -- Total amount of motorcyclist fatalities
    "belted_death_count", -- Total deaths of belted occupants
    "unbelted_occ_count", -- Total count of all unbelted occupants
    "driver_count_65_74yr", -- Total number of 65 to 74 year old drivers involved in the crash
    "driver_count_20yr", -- Total amount of 19 year old drivers involved in the crash
    "driver_count_19yr", -- Total amount of 19 year old drivers involved in the crash
    "driver_count_18yr", -- Total amount of 18 year old drivers involved in the crash
    "driver_count_16yr", -- Total amount of 16 year old drivers involved in the crash
    "min_inj_count", -- Total amount of possible injuries sustained in the crash. Possible Injury: Any injury reported or claimed which is not a fatal, suspected serious or suspected minor injury. Examples include momentary loss of consciousness, claim of injury, limping, or complaint of pain or nausea. Possible injuries are those which are reported by the person or are indicated by his/her behavior, but no wounds or injuries are readily evident. *Note: In 2016, the injury severity descriptions and definitions changed to match federal standards.
    "bicycle_count", -- Total amount of bicycles involved in the crash
    "motorcycle_count", -- Total amount of motorcycles involved in the crash
    "wz_shlder_mdn", -- Identifies if a median or shoulder was in the zone
    "overturned", -- Overturned vehicle indicator
    "hit_gdrail_end", -- Hit guide rail end indicator
    "hit_pole", -- Hit pole indicator
    "hit_tree_shrub", -- Hit tree or shrub indicator
    "school_zone", -- School zone indicator
    "school_bus", -- School bus indicator
    "unsignalized_int", -- Unsignalized intersection indicator
    "signalized_int", -- Signalized intersection indicator
    "fatal", -- Fatality indicator
    "injury", -- At least 1 person was injured in the crash
    "sv_run_off_rd", -- Single vehicle run off road indicator
    "ho_oppdir_sdswp", -- Head on or side swipe indicator
    "snow_slush_road", -- Snow slush road indicator
    "turnpike", -- Turnpike indicator
    "local_road_only", -- Local road only indicator
    "wz_law_offcr_ind", -- Identifies if the work zone had a patrolman
    "workers_pres", -- Identifies if construction personnel were present
    "tfc_detour_ind", -- Identifies if traffic was detoured
    "ntfy_hiwy_maint", -- Identifies if PENNDOT highway maintenance was notified
    "sch_bus_ind", -- Identifies if a school bus was involved in the crash
    "illumination", -- Defines the lighting at the crash scene
    "district" -- District number where crash occurred (based on County)
FROM
    "pa-gov/crash-incident-details-cy-1997-current-annual-dc5b-gebx:latest"."crash_incident_details_cy_1997_current_annual"
LIMIT 100;

Connecting to the DDN is easy. All you need is an existing SQL client that can connect to Postgres. As long as you have a SQL client ready, you'll be able to query pa-gov/crash-incident-details-cy-1997-current-annual-dc5b-gebx with SQL in under 60 seconds.

This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.pa.gov. When you querypa-gov/crash-incident-details-cy-1997-current-annual-dc5b-gebx:latest on the DDN, we "mount" the repository using the socrata mount handler. The mount handler proxies your SQL query to the upstream data source, translating it from SQL to the relevant language (in this case SoQL).

We also cache query responses on the DDN, but we run the DDN on multiple nodes so a CACHE_HIT is only guaranteed for subsequent queries that land on the same node.

Query Your Local Engine

Install Splitgraph Locally
bash -c "$(curl -sL https://github.com/splitgraph/splitgraph/releases/latest/download/install.sh)"
 

Read the installation docs.

Splitgraph Cloud is built around Splitgraph Core (GitHub), which includes a local Splitgraph Engine packaged as a Docker image. Splitgraph Cloud is basically a scaled-up version of that local Engine. When you query the Data Delivery Network or the REST API, we mount the relevant datasets in an Engine on our servers and execute your query on it.

It's possible to run this engine locally. You'll need a Mac, Windows or Linux system to install sgr, and a Docker installation to run the engine. You don't need to know how to actually use Docker; sgrcan manage the image, container and volume for you.

There are a few ways to ingest data into the local engine.

For external repositories (like this repository), the Splitgraph Engine can "mount" upstream data sources by using sgr mount. This feature is built around Postgres Foreign Data Wrappers (FDW). You can write custom "mount handlers" for any upstream data source. For an example, we blogged about making a custom mount handler for HackerNews stories.

For hosted datasets, where the author has pushed Splitgraph Images to the repository, you can "clone" and/or "checkout" the data using sgr cloneand sgr checkout.

Mounting Data

This repository is an external repository. It's not hosted by Splitgraph. It is hosted by data.pa.gov, and Splitgraph indexes it. This means it is not an actual Splitgraph image, so you cannot use sgr clone to get the data. Instead, you can use the socrata adapter with the sgr mount command. Then, if you want, you can import the data and turn it into a Splitgraph image that others can clone.

First, install Splitgraph if you haven't already.

Mount the table with sgr mount

sgr mount socrata \
  "pa-gov/crash-incident-details-cy-1997-current-annual-dc5b-gebx" \
  --handler-options '{
    "domain": "data.pa.gov",
    "tables": {
        "crash_incident_details_cy_1997_current_annual": "dc5b-gebx"
    }
}'

That's it! Now you can query the data in the mounted table like any other Postgres table.

Query the data with your existing tools

Once you've loaded the data into your local Splitgraph engine, you can query it with any of your existing tools. As far as they're concerned, pa-gov/crash-incident-details-cy-1997-current-annual-dc5b-gebx is just another Postgres schema.