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 advanced_messaging_concept_development_probe_data table in this repository, by referencing it like:
"datahub-transportation-gov/advanced-messaging-concept-development-probe-data-q3ms-nja6:latest"."advanced_messaging_concept_development_probe_data"or in a full query, like:
SELECT
":id", -- Socrata column ID
"obu_id", -- The ID of the OBU that received the message for the current communication sequence
"time_received", -- The time at which the message was received by the OBU in milliseconds UTC time.
"sendall", -- An indicator to send data on all conditions.
"test_no", -- Testing ID Number
"transmit_interval", -- A time value expressed in seconds that defines time between transmissions of PVD messages.
"message_id", -- : A message ID value generated by the PDCM configuration client that increases sequentially with each new PDCM message created starting with 1. Empty
"send_on_less_then_value", -- Threshold for a less than conditional.
"send_on_more_then_value", -- Threshold for a more than conditional.
"sample_start", -- Allows the Probe Management message to apply its settings to a random sample of vehicles (all vehicles within the stated range defined by Sample Start and Sample End). This uses the last single digit of the current probe segment number (PSN) to determine if probe management is to be used. If the current PSN falls between Sample Start and Sample End, then the Probe Data Management policy will be applied.
"heading_slice", -- An indication of heading ranges for which the PDCM policy should apply. The bit is set to 1 if the heading slice should be included. Bit1 = 0 to 22.5 degrees Bit2 = 22.5 to 45 degrees Bit3 = 45 to 67.5 degrees Bit4 = 67.5 to 90 degrees Bit5 = 90 to 112.5 degrees Bit6 = 112.5 to 135 degrees Bit7 = 135 to 157.5 degrees Bit8 = 157.5 to 180 degrees Bit9 = 180 to 202.5 degrees Bit10 = 202.5 to 225 degrees Bit11 = 225 to 247.5 degrees Bit12 = 247.5 to 270 degrees Bit13 = 270 to 292.5 degrees Bit14 = 292.5 to 315 degrees Bit15 = 315 to 337.5 degrees Bit16 = 337.5 to 360 degrees
"termination_time", -- A time value that provides a time-to-live type of time-out. It allows users to provide the number of seconds at which time the probe management process ceases and the default condition is applied.
"snapshot_speed_s2", -- A speed value to be used in a calculation that allows users to change the snapshot collection policy based on elapsed time. Two time and speed pairs are included in the message—T1, S1 and T2, S2—to be used by the OBU as follows. • If speed < S1 then time to next snapshot is T1 – default 20 mph (8.9 m/s) and 6 seconds • If speed > S2 then time to next snapshot is T2 – default 60 mph (26.8 m/s) and 20 seconds • If S1 < speed < S2 then time to snapshot is linearly interpolated between T1 and T2 • If S1 is zero, then the time to snapshot is always T1 This is S2, which is the instantaneous speed in meters per second when the calculation is performed.
"snapshot_time_t2", -- : A speed value to be used in a calculation that allows users to change the snapshot collection policy based in elapsed time. Two time and speed pairs are included in the message—T1, S1 and T2, S2—to be used by the OBU as follows. • If speed < S1 then time to next snapshot is T1 – default 20 mph (8.9 m/s) and 6 seconds • If speed is > S2 then time to next snapshot is T2 – default 60 mph (26.8 m/s) and 20 seconds • If S1 < speed < S2 then time to snapshot is linearly interpolated between T1 and T2 • If S1 is zero, then the time to snapshot is always T1 This is T2, which is expressed as time in seconds.
"snapshot_distance_s1", -- A speed value to be used in a calculation that allows users to change the snapshot collection policy based in speed and distance. Two distance and speed pairs are included in the message—D1, S1 and D2, S2—to be used by the OBU as follows. • If speed is < S1 then distance to next snapshot is D1 • If speed is > S2 then distance to next snapshot is D2 • If S1 < speed < S2 then distance to snapshot is linearly interpolated between D1 and D2 • If S1 is zero, then the distance to snapshot is always D1 This is speed S1, which is expressed meters per second.
"snapshot_distance_d1", -- A distance value to be used in a calculation that allows users to change the snapshot collection policy based in speed and distance. Two distance and speed pairs are included in the message—D1, S1 and D2, S2—to be used by the OBU as follows. • If speed is < S1 then distance to next snapshot is D1 • If speed is > S2 then distance to next snapshot is D2 • If S1 < speed < S2 then distance to snapshot is linearly interpolated between D1 and D2 • If S1 is zero, then the distance to snapshot is always D1 This is distance D1, which is expressed meters.
"snapshot_distance_s2", -- A speed value to be used in a calculation that allows users to change the snapshot collection policy based in speed and distance. Two distance and speed pairs are included in the message—D1, S1 and D2, S2—to be used by the OBU as follows. • If speed is < S1 then distance to next snapshot is D1 • If speed is > S2 then distance to next snapshot is D2 • If S1 < speed < S2 then distance to snapshot is linearly interpolated between D1 and D2 • If S1 is zero, then the distance to snapshot is always D1 This is speed S2, which is expressed meters per second.
"snapshot_distance_d2", -- A distance value to be used in a calculation that allows users to change the snapshot collection policy based in speed and distance. Two distance and speed pairs are included in the message—D1, S1 and D2, S2—to be used by the OBU as follows. • If speed is < S1 then distance to next snapshot is D1 • If speed is > S2 then distance to next snapshot is D2 • If S1 < speed < S2 then distance to snapshot is linearly interpolated between D1 and D2 • If S1 is zero, then the distance to snapshot is always D1 This is distance D2, which is expressed meters.
"vehicle_status_request_count_number", -- Indicates the value of the current sequence count number represented on this row of the CSV data file.
"time_sent", -- The time at which the message was sent from the VCC Cloud server to the OBU in milliseconds UTC time.
"mode_of_transmission", -- An indication of which mode of transmission was used to transmit the message (DSRC or Cellular).
"sample_end", -- Allows the Probe Management message to apply its settings to a random sample of vehicles (all vehicles within the stated range defined by Sample Start and Sample End). This uses the last single digit of the current PSN to determine if probe management is to be used. If the current PSN falls between Sample Start and Sample End, then the Probe Data Management policy will be applied.
"termination_distance", -- A distance value that provides a distance-to-live type of time-out. It allows users to provide distance to travel in meters until the probe management process ceases and the default condition is applied.
"snapshot_speed_s1", -- A speed value to be used in a calculation that allows users to change the snapshot collection policy based on elapsed time. Two time and speed pairs are included in the message—T1, S1 and T2, S2—to be used by the OBU as follows. • If speed < S1 then time to next snapshot is T1 – default 20 mph (8.9 m/s) and 6 seconds • If speed > S2 then time to next snapshot is T2 – default 60 mph (26.8 m/s) and 20 seconds • If S1 < speed < S2 then time to snapshot is linearly interpolated between T1 and T2 • If S1 is zero, then the time to snapshot is always T1 This is S1, which is the instantaneous speed in meters per second when the calculation is performed.
"snapshot_time_t1", -- A speed value to be used in a calculation that allows users to change the snapshot collection policy based on elapsed time. Two time and speed pairs are included in the message—T1, S1 and T2, S2—to be used by the OBU as follows. • If speed < S1 then time to next snapshot is T1 – default 20 mph (8.9 m/s) and 6 seconds • If speed > S2 then time to next snapshot is T2 – default 60 mph (26.8 m/s) and 20 seconds • If S1 < speed < S2 then time to snapshot is linearly interpolated between T1 and T2 • If S1 is zero, then the time to snapshot is always T1 This is T1, which is expressed as time in seconds.
"count", -- A number that provides a count of the number of Vehicle Status Request segments that are included in the message.
"vehicle_status_device_type_tag" -- : An enumeration of the possible values that may be requested from VehicleStatusDeviceType data frame to be used as conditional indicators of whether data will be sent in subsequent PVD messages. The vehicle device type tag values include: 0 = Unknown 1 = Exterior Lights 2 = Wipers 3 = Brakes Applied 4 = Stability Control 5 = Traction Control 6 = Anti-lock Brakes 7 = Sun Sensor 8 = Rain Sensor 9 = Air Temperature 10 = Steering 11 = Vertical Acceleration Threshold (wheel that exceeded) 12 = Vertical Acceleration (vertical g-force value) 13 = Longitudinal Acceleration 14 = Lateral Acceleration 15 = Lateral Acceleration Confidence 16 = Acceleration 4-way 17 = Confidence Set 18 = Obstacle Distance 19 = Obstacle Direction 20 = Yaw Rate 21 = Yaw Rate Confidence 22 = Date and Time 23 = Full Position (complete set of time, position, speed, heading) 24 = Position 2D (lat, lon) 25 = Position 3D (lat, lon, elevation) 26 = Vehicle (height, mass, type) 27 = SpeedHeadC 28 = SpeedC;
FROM
"datahub-transportation-gov/advanced-messaging-concept-development-probe-data-q3ms-nja6:latest"."advanced_messaging_concept_development_probe_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 datahub-transportation-gov/advanced-messaging-concept-development-probe-data-q3ms-nja6 with SQL in under 60 seconds.
This repository is an "external" repository. That means it's hosted elsewhere, in this case at datahub.transportation.gov. When you querydatahub-transportation-gov/advanced-messaging-concept-development-probe-data-q3ms-nja6: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
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 datahub.transportation.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 \
"datahub-transportation-gov/advanced-messaging-concept-development-probe-data-q3ms-nja6" \
--handler-options '{
"domain": "datahub.transportation.gov",
"tables": {
"advanced_messaging_concept_development_probe_data": "q3ms-nja6"
}
}'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, datahub-transportation-gov/advanced-messaging-concept-development-probe-data-q3ms-nja6 is just another Postgres schema.