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 public_crash_data table in this repository, by referencing it like:
"delaware-gov/public-crash-data-827n-m6xc:latest"."public_crash_data"or in a full query, like:
SELECT
":id", -- Socrata column ID
"road_surface", -- Code to describe the road surface condition: 01 - Dry; 02 - Wet; 03 - Snow; 04 - Ice/Frost; 05 - Sand; 06 - Water (standing, moving); 07 - Slush; 08 - Oil; 09 - Mud, Dirt, Gravel; 88 - Other; 99 - Unknown;
"impact", -- Code to describe the manner of impact: 01 - Front to rear; 02 - Front to front; 03 - Angle; 04 - Sideswipe, same direction; 05 - Sideswipe, opposite direction; 06 - Rear to side; 07 - Rear to rear; 88 - Other; 99 - Unknown; 00 - Not a collision between two vehicles;
"weather_1_desc", -- The prevailing atmospheric condition that existed at the time of the crash.
"weather_2_desc", -- The secondary atmospheric condition that existed at the time of the crash, if reported.
"priv_prop_coll", -- Private property is defined here as location inputs of “outside of right of way (private property)” and “private parking lot” in the collision reports. See also 21 Del. C. §101(55). Trafficways are excluded from private property. This field can be used in conjunction with the “Crash Classification” field to determine if a “Non-Reportable” report involved a “Property Damage Only” collision on a trafficway that resulted in an apparent extent (dollar value estimate) of damage below the statutory reporting threshold. Text: No; Yes;
"day_of_week", -- Code to describe the day of week: 01 - Sunday; 02 - Monday; 03 - Tuesday; 04 - Wednesday; 05 - Thursday; 06 - Friday; 07 - Saturday;
"work_zone", -- Specifies if the crash is in a construction, maintenance, or utility work zone or if it related to activity within a work zone. Text: No; Yes;
"seatbelt_used", -- Indicates if seatbelts are used by all occupants (i.e., drivers and passengers in aggregate) required to do so in all vehicles involved in the collision. A “Yes” response indicates all occupants were compliant. A “No” response indicates that one or more individuals in the collision was not using the required occupant protection system. Text: No; Yes;
"workers_present", -- Specifies if workers were present in the work zone at the time of the crash: 01 - No; 02 - Yes; 09 - Unknown;
"bike_involved", -- Indicates whether a bicycle (i.e., two-wheel, non-motorized cycle) or other cycle (i.e., non-motorized pedal-powered vehicle other than a bicycle, such as a unicycle or adult tricycle) was involved in the crash. The “Bicycle Involved” field populates by aggregating if any non-motorist is designated a “bicyclist” or “other cyclist.” Text: No; Yes;
"mc_involved", -- Indicates whether a motorcycle or scooter was involved in the crash. This field is included to provide context to the “Motorcycle Helmet Used” field. Text: No; Yes;
"drug_involved", -- Indicates suspected drug use by “any person” involved in the crash, excluding passengers. “Any person” could include a driver, pedestrian, bicyclist/cyclist, occupant of a non-motor vehicle, an unknown type of non-motorist, or a reporting person for a hit and run, regardless of if that person was at fault or which vehicle, if any, they occupied. Text: No; Yes;
"school_bus_involved_desc", -- Indicates whether a school bus or motor vehicle functioning as a school bus for a school-related purpose is involved in the crash.
"work_zone_type_desc", -- Specifies the type of work zone in which the crash occurs.
"weather_1", -- Code to describe the primary weather condition: 01 - Clear; 02 - Cloudy; 03 - Fog, Smog, Smoke; 04 - Rain; 05 - Sleet, Hail (freezing rain or drizzle); 06 - Snow; 07 - Blowing Snow; 08 - Severe Crosswinds; 09 - Blowing Sand, Soil, Dirt; 88 - Other; 99 - Unknown;
"mc_helmet_used", -- Indicates whether a helmet was used by any person involved in a crash that involved either a motorcycle or a scooter. For example, a collision involving two passenger sedans (i.e., a vehicle type with no helmet requirements) will populate a value of “No.” This field is to be used in conjunction with the “Motorcycle Involved” field to filter the results to collisions that involve a motorcycle or scooter. Text: No; Yes;
"school_bus_involved", -- Code to describe if a school bus is involved in a crash: 00 - No; 01 - Yes, School Bus Directly Involved; 02 - Yes, School Bus Indirectly Involved;
"work_zone_type", -- Code to describe the work zone type: 01 - Lane Closure; 02 - Lane Shift/Crossover; 03 - Work on Shoulder or Median; 04 - Intermittent or Moving Work; 88 - Other;
"road_surface_desc", -- The prevailing apparent condition of the road at the time of the crash.
"latitude", -- The geographic latitude of the crash, given in decimal degrees.
"pri_contrib_circum_desc", -- Primary circumstance that may have contributed to the crash.
"pri_contrib_circum", -- Code to describe the primary contributing circumstance: 01 - Speeding; 02 - Failed to yield right of way; 03 - Passed Stop Sign; 04 - Disregard Traffic Signal; 05 - Wrong side or wrong way; 06 - Improper passing; 07 - Improper lane change; 08 - Following too close; 09 - Made improper turn; 10 - Driving under the influence; 11 - Driver inattention, distraction, or fatigue; 12 - Driving in a careless or reckless manner; 13 - Driving in an aggressive manner; 14 - Improper backing; 15 - Other improper driving; 16 - Mechanical defects; 17 - Animal in Roadway - Deer; 18 - Animal in Roadway - Other Animal; 19 - Other environmental circumstances - weather, glare, visibility obstructed, etc.; 20 - Roadway circumstances - debris, holes, work zone, etc.; 21 - Pedestrian; 88 - Other; 99 - Unknown;
"work_zone_location_desc", -- Specifies the section of the work zone (which extends from the first warning sign, signal or flashing lights to the “End Road Work” sign or the last traffic control device pertinent for that work activity) in which the crash occurs.
"day_of_week_desc", -- Day of the week that the crash occurred.
"ped_involved", -- Indicates if a pedestrian was involved in the collision.
"weather_2", -- Code to describe the secondary weather condition: 01 - Clear; 02 - Cloudy; 03 - Fog, Smog, Smoke; 04 - Rain; 05 - Sleet, Hail (freezing rain or drizzle); 06 - Snow; 07 - Blowing Snow; 08 - Severe Crosswinds; 09 - Blowing Sand, Soil, Dirt; 88 - Other; 99 - Unknown;
"county_desc", -- The name of the county in which the crash occurred.
"alcohol_involved", -- Indicates suspected alcohol use by “any person” involved in the crash, excluding passengers. “Any person” could include a driver, pedestrian, bicyclist/cyclist, occupant of a non-motor vehicle, an unknown type of non-motorist, or a reporting person for a hit and run, regardless of if that person was at fault or which vehicle, if any, they occupied. Text: No; Yes;
"light_cond", -- Code to describe the ambient lighting condition: 01 - Daylight; 02 - Dawn; 03 - Dusk; 04 - Dark-Lighted; 05 - Dark-Not Lighted; 06 - Dark-Unknown Lighting; 88 - Other; 99 - Unknown;
"the_geom", -- The geometry of the data point.
"impact_desc", -- Where the first harmful event involves a collision between two motor vehicles in transport, the manner of impact identifies how the two motor vehicles initially came together, without regard to the direction of force. Motor vehicles not in transport (e.g., parked and working motor vehicles, like short term stationary utility work (repairing electric, gas, or water lines within the trafficway)) are not included.
"light_cond_desc", -- The type/level of ambient light that existed at the time of the crash.
"bike_helmet_used", -- Indicates if a non-motorist bicyclist or other cyclist has “Helmet” listed in the safety equipment field of the Person table of ECRASH. Text: No; Yes;
"longitude", -- The geographic longitude of the crash, given in decimal degrees.
"work_zone_location", -- Code to describe the work zone location: 01 - Before the First Work Zone Warning Sign; 02 - Advance Warning Area; 03 - Transition Area; 04 - Activity Area; 05 - Termination Area;
"county", -- Code used to define the county: K - Kent; N - New Castle; S - Sussex
"crash_datetime", -- The date and time at which the crash occurred.
"crash_class", -- Code to describe the ECRASH Report type crash classification: 01 - Non-Reportable; 02 - Property Damage Only; 03 - Personal Injury Crash; 04 - Fatality Crash;
"crash_class_desc" -- ECRASH reports are sorted into four categories. The categories are determined by the following factors, which are applied in sequence: 1) the nature of the crash location (i.e., private property vs. trafficway); 2) the most serious level of injury (i.e., fatal involved, personal injury involved, or property damage only); and 3) if a property damage collision only, the apparent extent (dollar value estimate) of damage. Private property is defined here as location inputs of “outside of right of way (private property)” and “private parking lot” in the collision reports. See also 21 Del. C. § 101(55). Trafficways are excluded from private property. A “Non-Reportable” classification occurs under two circumstances. First, all collisions on private property, regardless of whether the collision results in a fatality, personal injury, or property damage only, are included in this category. Second, all collisions on trafficways that result in property damage only (i.e., a non-fatal or non-personal injury event) and are of an apparent extent below $2,000 (or $1,500 if before September 30, 2021), which is Delaware’s duty to report a collision threshold. See generally 21 Del. C. § 4203. A “Property Damage Only” classification refers to a collision that occurs on a trafficway and results in property damage only (i.e., a non-fatal or non-personal injury event) to an apparent extent of $2,000 or more (or $1,500 or more if before September 30, 2021). A “Personal Injury Crash” or “Fatality Crash” classification refers to a collision that occurs on a trafficway and the category reflects the most serious degree of injury present.
FROM
"delaware-gov/public-crash-data-827n-m6xc:latest"."public_crash_data"
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 delaware-gov/public-crash-data-827n-m6xc 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 delaware-gov/public-crash-data-827n-m6xc: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 delaware-gov/public-crash-data-827n-m6xcCheckout 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 delaware-gov/public-crash-data-827n-m6xc:latestThis will download all the objects for the latest tag of delaware-gov/public-crash-data-827n-m6xc 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 delaware-gov/public-crash-data-827n-m6xc: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 delaware-gov/public-crash-data-827n-m6xc: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, delaware-gov/public-crash-data-827n-m6xc is just another Postgres schema.