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 traffic_crashes table in this repository, by referencing it like:
"norfolk-gov/traffic-crashes-w6gf-9gws:latest"."traffic_crashes"or in a full query, like:
SELECT
":id", -- Socrata column ID
"vehicle_maneuver", -- A semicolon delimited list indicating the movement of each vehicle involved in the crash prior to the crash, ordered by the vehicle number of the corresponding vehicle.
"number_of_pedestrian_fatalities", -- The number of pedestrian fatalities in the crash.
"number_of_pedestrians_injured", -- The number of pedestrians injured in the crash.
"most_harmful_crash_event", -- A semicolon delimited list indicating the crash event for each vehicle involved in the crash that resulted in the most severe injury or greatest property damage for that vehicle, ordered by the vehicle number of the corresponding vehicle.
"number_of_people_with_possible_injury", -- The number of people with a possible injury as outline in the “Possible Injury” KABCO scale definition.
"direction_of_travel", -- A semicolon delimited list of the direction each vehicle was traveling prior to the crash, ordered by the vehicle number of the corresponding vehicle.
"work_zone", -- A crash in which the first harmful event occurred within the boundaries of a work zone as determined by the reporting officer.
"alcohol_involved", -- A crash in which any driver (including motorcyclist and bicyclist) or pedestrian involved had been drinking alcohol prior to the incident. A crash can be marked as alcohol-related based on blood alcohol tests, breath tests, or visual assessments by the reporting officer.
"animal_involved", -- A crash that involved a driver striking an animal or crashing after avoiding an animal.
"distraction_involved", -- A crash in which any drive or bicyclist involved had been distracted prior to the incident.
"drowsy_involved", -- A crash in which any driver or bicyclist involved had been drowsy prior to the incident.
"drug_involved", -- A crash in which any driver, bicyclist, or pedestrian involved had been using drugs prior to the incident.
"guard_rail_crash", -- A crash that involved a vehicle making contact with a guard rail.
"speeding", -- A crash in which any vehicle involved is driving too fast for conditions or exceeding the posted speed limit. If the maximum safe speed was reported as zero (e.g., a vehicle that ran a red light), the crash is a speeding crash only if a driver was driving faster than the speed limit.
"senior_driver_or_pedestrian_involved", -- A crash that involved any driver or pedestrian who is 65 years of age or older.
"young_driver_or_pedestrian_involved", -- A crash that involved any driver or pedestrian who is at least 15 years of age and not yet 21 years of age at the time of the crash.
"mainline_crash", -- A crash that occurred on the mainline of the roadway (i.e., not on the ramp or collector-distributor road).
"night_crash", -- A crash that occurred in darkness.
"roadway_description", -- The type of roadway facility at the location of the first harmful event in the crash.
"owner_of_roadway", -- The owner (State Hwy Agency, City or Town Hwy Agency, Private/Unknown Roads, etc.) of the roadway where the crash occurred.
"ward",
"superward",
"route_or_street_name", -- The common route or street name where the crash occurred as recorded by the reporting officer.
"first_harmful_event", -- The first injury or damaged-producing event that characterizes the collision type. This may or may not be the first event of the crash.
"location_of_first_harmful_event", -- The location of the first injury or damage-producing event that characterizes the collision type.
"driver_action", -- A semicolon delimited list of the action of each driver involved in the crash that contributed to the crash, ordered by the driver vehicle number of the corresponding driver.
"pedestrian_involved", -- A crash that involved at least one pedestrian as obtained from the pedestrian table.
"weather_condition", -- The weather condition at the time of the crash.
"roadway_surface_condition", -- The roadway surface condition of the travel lanes at the time and location of the first harmful event in the crash.
"relation_to_roadway", -- The location of the first harmful event in relation to the roadway.
"roadway_alignment", -- The geometric layout and inclination of the roadway at the location of the first harmful event in the crash.
"roadway_defects", -- The roadway defects at the scene of the crash that may have contributed to the crash.
"traffic_control_device_status", -- Indicates if the traffic control device selected in the Traffic Control Type was functional and visible at the time of the crash.
"civic_league",
"number_of_people_with_suspected_serious_injury", -- The number of people with a suspected serious injury as outlined in the “Suspected Serious Injury” KABCO scale definition.
"number_of_people_injured", -- The total number of people with a suspected serious injury, suspected minor injury, and possible injury resulting from the crash.
"type_of_collision", -- The type of collision. Further definitions and supporting imaged are provided in Appendix A of the Crash Data Dictionary: https://vdot.maps.arcgis.com/sharing/rest/content/items/490d94134bf342c281ded44ca22a8630/data
"hit_and_run", -- A crash that involved any driver who fled the scene.
"maximum_speed_difference", -- The maximum speed differential between a vehicle’s speed before the crash and the minimum of the maximum safe speed for driving conditions (as recorded by the reporting officer) and posting speed limit among all vehicles involved in the crash. If the maximum safe speed was reported as zero (e.g., a vehicle that ran a red light), the maximum speed differential is calculated based on the speed limit.
"traffic_control_type", -- The traffic control type at the scene of the crash.
"latitude", -- The latitude where the crash occurred.
"longitude", -- The longitude where the crash occurred.
"number_of_people_with_suspected_minor_injury", -- The number of people with a suspected minor injury as outlined in the “Suspected Minor Injury” KABCO scale definition.
"number_of_fatalities", -- The number of fatalities in the crash as outlined in the “Fatality” KABCO scale definition.
"crash_severity", -- The most severe injury to any person involved in the crash. For example, if the driver of one vehicle suffered a Suspected Serious Injury and a passenger in another vehicle in the same crash suffered a Possible Injury, the crash severity would be recorded as “Suspected Serious Injury”. Crash severity is coded using the KABCO scale.
"number_of_vehicles_involved", -- The number of vehicles involved in the crash.
"datetime", -- The date and time at which the crash occurred.
"document_number", -- A unique identifier that is generated by the Virginia Department of Motor Vehicles (DMV).
"intersection_type", -- The configuration of the intersection at which the crash occurred.
"light_condition", -- The light condition at the time of the crash.
"motorcycle_involved", -- A crash that involved a motorcycle.
"heavy_vehicle_involved", -- A crash that involved one or more vehicles that were a commercial vehicle or had the configuration type/body type of a heavy vehicle.
"bicycle_involved", -- A crash that involved a bicycle.
"wearing_seatbelt_or_restraint", -- A crash in which any occupant involved was not wearing a seatbelt or proper child restraint prior to the incident.
"school_zone" -- A crash in which the first harmful event occurred within a school zone as determined by the reporting officer.
FROM
"norfolk-gov/traffic-crashes-w6gf-9gws:latest"."traffic_crashes"
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 norfolk-gov/traffic-crashes-w6gf-9gws 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 norfolk-gov/traffic-crashes-w6gf-9gws: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 norfolk-gov/traffic-crashes-w6gf-9gwsCheckout 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 norfolk-gov/traffic-crashes-w6gf-9gws:latestThis will download all the objects for the latest tag of norfolk-gov/traffic-crashes-w6gf-9gws 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 norfolk-gov/traffic-crashes-w6gf-9gws: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 norfolk-gov/traffic-crashes-w6gf-9gws: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, norfolk-gov/traffic-crashes-w6gf-9gws is just another Postgres schema.