Query the Data Delivery Network
Query the DDNThe 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 fra_regulated_mode_major_security_events table in this repository, by referencing it like:
"datahub-transportation-gov/fra-regulated-mode-major-security-events-65fa-qbkf:latest"."fra_regulated_mode_major_security_events"or in a full query, like:
SELECT
":id", -- Socrata column ID
"pedestrian_not_in_crosswalk_2", -- Count of serious Pedestrian: not in crosswalk Injuries for the given event.
"suicide_injuries", -- Count of Suicide related injuries for the given event.
"other_serious_injuries", -- Count of serious Other Injuries for the given event.
"occupant_of_other_vehicle_1", -- Count of Occupant of other vehicle Injuries for the given event.
"pedestrian_walking_along_2", -- Count of serious Pedestrian: person walking along tracks Injuries for the given event.
"pedestrian_crossing_tracks_2", -- Count of serious Pedestrian: person crossing tracks Injuries for the given event.
"pedestrian_not_in_crosswalk_1", -- Count of Pedestrian: not in crosswalk Injuries for the given event.
"other_worker_serious_injuries", -- Count of serious Other worker (e.g., commercial worker / utilities worker / etc.) Injuries for the given event.
"transit_employee_serious", -- Count of serious Transit employee or contractor Injuries for the given event.
"transit_employee_injuries", -- Count of Transit employee or contractor Injuries for the given event. Includes "Other Transit Staff" inside the vehicle. Does not include vehicle operators.
"people_waiting_or_leaving_1", -- Count of Person waiting / leaving from transit Injuries for the given event.
"suicide_fatalities", -- Count of Suicide related fatalities for the given event.
"other_fatalities", -- Count of Other fatalities for the given event.
"occupant_of_other_vehicle", -- Count of Occupant of other vehicle fatalities for the given event.
"pedestrian_walking_along", -- Count of Pedestrian: person walking along tracks fatalities for the given event.
"other_worker_fatalities", -- Count of Other worker (e.g., commercial worker / utilities worker / etc.) fatalities for the given event.
"other_vehicle_action", -- Identifies the other involved vehicle's action at the time of the collision or derailment.
"manufacturer", -- Identifies the manufacturer of the transit vehicle for collisions.
"non_rail_transit_vehicle", -- Identifies the form of non-rail passenger conveyance used for revenue operations.
"intentional_y_n", -- Identifies if the security event was intentional or not.
"intersection", -- Identifies the intersection control device located at the intersection for a non-rail collision or rail collision.
"right_of_way_condition", -- Identifies the right-of-way (ROW) conditions for non-rail or rail collision or rail derailment.
"rail_grade_crossing_control", -- Identifies the grade crossing control device used at the grade crossing for non-rail or rail collision or rail derailment.
"path_condition", -- Identifies the pathway (roadway) conditions for a non-rail collision.
"tide", -- Identifies the tide conditions at the time of the ferryboat collision.
"lighting", -- Identifies the indoor or outdoor lighting conditions at the time of the collision or derailment.
"weather", -- Identifies the weather at the time of the event.
"other_fire_fuel_description", -- Text explanation from the 'Please describe" box used when "Other'' is selected for the Fire Fuel
"fire_fuel", -- For vehicle fires, identifies the type of fuel used to power the vehicle.
"hazmat_type", -- Identifies the type of substance that caused the Hazardous Material Spill. [The spill or release of any amount of hazardous material that creates an imminent danger to life, health, or the environment and requires special attention be given to clean up the material. ]
"evacuation_location", -- Identifies what was evacuated.
"self_evacuation_y_n", -- Identifies a Self-evacuation, which occur when people vacate transit property without direction from transit personnel or another authority, are not automatically reportable.
"latitude", -- The geographic coordinate that specifies the north–south position of a point on the Earth's surface. NTD requires a minimum of 4 decimal places, and if 7 decimals are not provided, the NTD systems fills to 7 decimals.
"longitude", -- The geographic coordinate that specifies the east–west position of a point on the Earth's surface. NTD requires a minimum of 4 decimal places, and if 7 decimals are not provided, the NTD systems fills to 7 decimals.
"event_location", -- Location of the event
"number_of_vehicles_involved", -- Identifies the number of non-transit vehicles involved in the event.
"towed_y_n", -- Identifier of collisions involving towing away from the scene for a transit roadway vehicle or other non-transit roadway vehicle that incurred disabling damage as a result of a collision.
"derailment_type", -- Identifies the type of rail derailment.
"property_damage_type", -- Non-rail mode: The estimated dollar value of all property that is damaged in a Reportable Event. This includes transit-owned property and other vehicles and property involved in the event that are not owned by the transit agency (excludes personal property such as cell phones and computers). Property damage also includes the cost of clearing wreckage.
"property_damage", -- Non-rail mode: The estimated dollar value of all property that is damaged in a Reportable Event. This includes transit-owned property and other vehicles and property involved in the event that are not owned by the transit agency (excludes personal property such as cell phones and computers). Property damage also includes the cost of clearing wreckage.
"event_type", -- The type of event that occurred.
"incident_time", -- The actual time of day the event occurred.
"incident_date", -- The date on which the event occurred.
"incident_number", -- A unique system-generated identification number for each Major Safety Event.
"agency", -- The legal name of the entity reporting to the NTD.
"transit_vehicle_rider_serious", -- Count of serious Transit vehicle rider Injuries for the given event.
"transit_employee_fatalities", -- Count of Transit employee or contractor fatalities for the given event. Includes "Other Transit Staff" inside the vehicle. Does not include vehicle operators.
"manufacturer_description", -- Text explanation from the 'Please describe" box used when "Other'' is selected for the Manufacturer type.
"vehicle_speed", -- Identifies the actual or estimated speed at the time of the collision or derailment.
"fuel_type", -- Identifies the type of fuel used to power the transit vehicle involved.
"vehicle_action", -- Identifies the Non-rail, ferry or rail vehicle action at the time of the collision or derailment.
"approximate_address", -- Identifies the exact or approximate address or location of the event.
"year", -- The calendar year in which the event occurred.
"ntd_id", -- A five-digit identifying number for each agency used in the current NTD system.
"occupant_of_other_vehicle_2", -- Count of serious Occupant of other vehicle Injuries for the given event.
"pedestrian_not_in_crosswalk", -- Count of Pedestrian: not in crosswalk fatalities for the given event.
"service_stop_control_device", -- Identifies the service stop control device located at the service stop for a rail collision.
"runaway_train_flag", -- Identifier of events involving a runaway train. [A train which is no longer under the control of a driver regardless of whether the operator is physically on the vehicle at the time. This is limited to revenue vehicles.]
"person_list", -- For each person injured or killed in an event, the following information is supplied separated by semi colons: I (Injury) or F (Fatality), Person Type, T if the person was a trespasser, Age Group, Gender, Y if medical transport was required
"other_injuries", -- Count of Other Injuries for the given event.
"pedestrian_walking_along_1", -- Count of Pedestrian: person walking along tracks Injuries for the given event.
"pedestrian_crossing_tracks_1", -- Count of Pedestrian: person crossing tracks Injuries for the given event.
"pedestrian_in_crosswalk_2", -- Count of serious Pedestrian: in crosswalk Injuries for the given event.
"pedestrian_in_crosswalk_1", -- Count of Pedestrian: in crosswalk Injuries for the given event.
"bicyclist_serious_injuries", -- Count of serious Pedestrian: bicyclist Injuries for the given event.
"bicyclist_injuries", -- Count of Pedestrian: bicyclist Injuries for the given event.
"other_worker_injuries", -- Count of Other worker (e.g., commercial worker / utilities worker / etc.) Injuries for the given event.
"transit_vehicle_operator_2", -- Count of serious Transit vehicle operator Injuries for the given event.
"transit_vehicle_operator_1", -- Count of Transit vehicle operator Injuries for the given event.
"transit_vehicle_rider_injuries", -- Count of Transit vehicle rider Injuries for the given event.
"pedestrian_crossing_tracks", -- Count of Pedestrian: person crossing tracks fatalities for the given event.
"bicyclist_fatalities", -- Count of Pedestrian: bicyclist fatalities for the given event.
"other_involved_veh", -- Identifies the other involved vehicle type in the collision or derailment.
"transit_y_n", -- Identifies the number of transit (Y) and non-transit (N) vehicles involved in the event.
"rail_alignment", -- Identifies the rail alignment of the track on which the collision or derailment occurred.
"road_config", -- Identifies the roadway configuration at the scene of non-rail collision.
"current_condition", -- Identifies the water current conditions at the time of the ferryboat collision
"fire_type", -- Identifies the source of the fire. A fire is an uncontrolled combustion made evident by flame that requires suppression by equipment or personnel.
"hazmat_type_description", -- Text explanation from the 'Please describe" box used when "Other'' is selected for the "Haz Mat Type"
"evacuation_comment", -- The details of the evacuation. [A reportable evacuation is a condition that occurs when persons depart from transit vehicles or facilities for life safety reasons. Evacuations to a location that may put passengers or patrons in imminent danger (such as controlled rail right-of-way) must also be reported.]
"number_of_derailed_cars", -- Identifies the number of derailed cars.
"number_of_transit_vehicles", -- Identifies the number of transit vehicles involved in the event.
"total_fatalities", -- Total number of involved fatalities as a result of the event
"total_injuries", -- Total number of involved injuries as a result of the event.
"collision_with", -- Identifies what the transit vehicle collided with: Motor Vehicle, Non-Transit Motor Vehicle, Non-Rail Transit Vehicle, Rail Transit Vehicle/Rail Passenger Train – Revenue Service, Rail Transit Vehicle/Rail Passenger Train – Not in Revenue Service, Rail Transit Vehicle/Rail Transit Maintenance or Service Vehicle on the rail fixed guideway, Person, Animal, Fixed Object, Transit vehicle, Vessel, Dock/Terminal center, Non-revenue vehicle, Other.
"safety_security", -- Identifier of a Safety (SFT) or (Security Event). [Safety Event: A collision, derailment, fire, hazardous material spill, act of nature (Act of God), evacuation, or OSONOC occurring on transit right-of-way, in a transit revenue facility, in a transit maintenance facility, or involving a transit revenue vehicle and meeting established NTD thresholds.] OR [Security event: An occurrence of a bomb threat, bombing, arson, hijacking, sabotage, cyber security event, assault, robbery, rape, burglary, suicide, attempted suicide (not involving a transit vehicle), larceny, theft, vandalism, homicide, CBR (chemical/biological/radiological) or nuclear release, or other event.]
"incident_description", -- A 2000 character field used to describe the details of the event.
"suicide_serious_injuries", -- Count of serious Suicide related injuries for the given event.
"people_waiting_or_leaving_2", -- Count of serious Person waiting / leaving from transit Injuries for the given event.
"pedestrian_in_crosswalk", -- Count of Pedestrian: in crosswalk fatalities for the given event.
"transit_vehicle_operator", -- Count of Transit vehicle operator fatalities for the given event.
"people_waiting_or_leaving", -- Count of Person waiting / leaving from transit fatalities for the given event.
"transit_vehicle_rider", -- Count of vehicle passenger fatalities for the given event
"action_type", -- Identifies the Non-rail, ferry or rail vehicle impact point at the time of the collision.
"configuration", -- Identifies the railway configuration at the scene for derailments.
"fixed_route_flag", -- Identifies whether the Mode reported is operating on a fixed route.
"rail_bus_ferry", -- Groups modes based on whether they are: Rail Running Non-Rail Surface Transportation Ferryboat
"type_of_service", -- Describes how public transportation services are provided by the transit agency: directly operated (DO) or purchased transportation which also includes from Taxi Operators and Transportation Network Companies (PT, TX, TN) services.
"mode" -- Abbreviation of mode. For a complete list of modes, please find the most current Safety and Security Policy Manual at https://www.transit.dot.gov/ntd/manuals
FROM
"datahub-transportation-gov/fra-regulated-mode-major-security-events-65fa-qbkf:latest"."fra_regulated_mode_major_security_events"
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 datahub-transportation-gov/fra-regulated-mode-major-security-events-65fa-qbkf with SQL in under 60 seconds.
Query Your Local Engine
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, 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 (like this repository), where the author has pushed Splitgraph Images to the repository, you can "clone" and/or "checkout" the data using sgr cloneand sgr checkout.
Cloning Data
Because datahub-transportation-gov/fra-regulated-mode-major-security-events-65fa-qbkf:latest is a Splitgraph Image, you can clone the data from Spltgraph Cloud to your local engine, where you can query it like any other Postgres database, using any of your existing tools.
First, install Splitgraph if you haven't already.
Clone the metadata with sgr clone
This will be quick, and does not download the actual data.
sgr clone datahub-transportation-gov/fra-regulated-mode-major-security-events-65fa-qbkfCheckout the data
Once you've cloned the data, you need to "checkout" the tag that you want. For example, to checkout the latest tag:
sgr checkout datahub-transportation-gov/fra-regulated-mode-major-security-events-65fa-qbkf:latestThis will download all the objects for the latest tag of datahub-transportation-gov/fra-regulated-mode-major-security-events-65fa-qbkf and load them into the Splitgraph Engine. Depending on your connection speed and the size of the data, you will need to wait for the checkout to complete. Once it's complete, you will be able to query the data like you would any other Postgres database.
Alternatively, use "layered checkout" to avoid downloading all the data
The data in datahub-transportation-gov/fra-regulated-mode-major-security-events-65fa-qbkf:latest is 0 bytes. If this is too big to download all at once, or perhaps you only need to query a subset of it, you can use a layered checkout.:
sgr checkout --layered datahub-transportation-gov/fra-regulated-mode-major-security-events-65fa-qbkf:latestThis will not download all the data, but it will create a schema comprised of foreign tables, that you can query as you would any other data. Splitgraph will lazily download the required objects as you query the data. In some cases, this might be faster or more efficient than a regular checkout.
Read the layered querying documentation to learn about when and why you might want to use layered queries.
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, datahub-transportation-gov/fra-regulated-mode-major-security-events-65fa-qbkf is just another Postgres schema.