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 2014_its_world_congress_connected_vehicle_test_bed table in this repository, by referencing it like:
"datahub-transportation-gov/2014-its-world-congress-connected-vehicle-test-bed-i5z4-qh9m:latest"."2014_its_world_congress_connected_vehicle_test_bed"or in a full query, like:
SELECT
":id", -- Socrata column ID
"bundle_time_hour", -- This field contains the hour in which the bundle was generated.
"schema", -- This field contains the ID (number) of the schema that was used to convert the data from binary to human readable (text) format. The values will either be 20, indicating that the ASN.1.2.0 schema was used in the conversion, or 21, indicating that the ASN.1.2.1 was used in the conversion process.
"path_itemcnt", -- This field contains the number of records in the bundle. Up to 10 records may be present.
"bundle_time_month", -- This field contains the month in which the bundle was generated.
"bundle_time_seconds", -- This field contains the second in which the bundle was generated.
"bundle_fundamental_speed_meterspersecond", -- This field contains the speed, in meter per second, of the vehicle referenced by the bundle.
"bundle_fundamental_heading_deg", -- The current heading of the front of the vehicle, in degrees from North. North shall be defined as the axis defined by the WSG-84 coordinate system and its reference ellipsoid. Headings "to the east" are defined as the positive direction. A value of 360 is used when unavailable.
"bundle_fundamental_steeringangle_long", -- This field contains a value to be converted to degrees to communicate the steering wheel angle. The LSBs are in units of 1.5 degrees and entries in this field have a range of -126 to +127, which facilitates steering angles between -/+189 degrees and a value signifying that the steering angle is unavailable. For example: 0 = +1.5 degrees -126 = -189 degrees and beyond +126 = +189 degrees and beyond +127 = unavailable steering angle More generally, for values between 0 and 126 the steering angle is obtained by multiplying by 1.5 degrees. However, for values between 129 and 255, mask off the highest bit (which is being used as a sign bit) by doing a bitwise AND with a value of 127. Then swap the remaining bit values by doing a bitwise exclusive OR with a value of 127 and then multiply by -1.5.
"path_initialposition_utctime_hour", -- This field contains the hour in which the VSD message was generated. 0 is used for an unknown value.
"path_initialposition_utctime_minute", -- This field contains the minute in which the VSD message was generated. 0 is used for an unknown value.
"bundle_fundamental_accel_long", -- This field contains the signed acceleration of the vehicle along the X axis or the vehicle's direction of travel in parallel with a front to rear centerline, in units of 0.01 meters per second squared. Negative values indicate braking action. A range of over 2Gs is supported.
"bundle_pos_longitude", -- The geographic longitude of the intersection with reference to the horizontal datum then in use, in degrees. The value 180.0000001 shall be used when unavailable.
"bundle_fundamental_accel_vert", -- This field contains the signed vertical acceleration of the vehicle along the vertical axis in units of 0.02 G (where 9.80665 meters per second squared is one G, i.e 0.02 G = 0.1962 meters per second squared). This provides an uneven range of over +1.5 to -3.4G in each direction with some negative values for greater negative ranges as denoted. Of note, least significant bit (LSB) units of 0.02 G steps over a range +1.54 to -3.4G and offset by 50 where Value 50 = 0g and Value 0 = -1G. As further reference a value of +127 = 1.54G, -120 = -3.4G, -121 for ranges -3.4 to -4.4G, -122 for ranges -4.4 to -5.4G, -123 for ranges -5.4 to -6.4G, -124 for ranges -6.4 to -7.4G, -125 for ranges -7.4 to -8.4G, -126 for ranges larger than -8.4G, and -127 for unavailable data
"bundle_pos_elevation_long", -- The Elevation data element represents the geographic position above or below the reference ellipsoid (typically WSG-84), in decimeter. This data element initially recorded as a 16-bit (hexadecimal) number has a resolution of 1 decimeter and represents an asymmetric range of positive and negative values. The encoding is as follows: the range 0x0000 to 0xEFFF (0 to 61439 decimal) are positive numbers representing elevations from 0 to +6143.9 meters, i.e. above the reference ellipsoid. The range 0xF001 to 0xFFFF are negative numbers representing elevations from -409.5 meters to -0.1 meters, i.e. below the reference ellipsoid. An elevation higher than +6143.9 meters is represented 0xEFFF. An elevation lower than -409.5 meters is represented 0xF001. If the sending device does not know its elevation it shall encode the Elevation data element with 0xF000. Examples of this encoding are: the elevation 0 meters is encoded as 0x0000. The elevation -0.1 meters is encoded as 0xFFFF. The elevation +100.0 meters is encoded as 0x03E8.
"crc", -- A two byte data element calculated over the payload bytes of the message (starting with the initial sequence and ending with the last data element before the CRC itself and including all tag, length, and values bytes found in between). Always placed as the very last data element in the message. The generating polynomial used is the "CRC-CCITT" commonly expressed as x^16 + x^12 + x^5 + 1. An initial seed value of zero shall be used. Note that because the first byte of every DSRC message is never zero (it is 0x30), framing errors due to incorrectly clocking initial zero values cannot occur. Note that the MSB byte is always transmitted first, following the typical ASN bytes order. When a well formed DSRC message (including its last two bytes holding the CRC value) is decoded and input to the CRC process, the resulting CRC should always be the value zero.
"bundle_fundamental_vehsize_length", -- This field contains the length of the vehicle measured from the edge of the front bumper to the edge of rear bumper expressed in centimeters. 0 is used when the width is unavailable.
"bundle_fundamental_brakes_long", -- To determine the meaning of the entries in this field, the entries have to first be converted into an 8-bitstring (00000000). This string will then be partitioned in accordance with the J2735 Standard to communicate the relevant information regarding the state of a vehicle’s brake system.
"bundle_fundamental_accel_yaw", -- This field contains the yaw rate of the vehicle, a signed value (to the right being positive) and expressed in 0.01 degrees per second. The yaw rate reports the vehicle's rotation in degrees per second.
"bundle_fundamental_accelset", -- This field contains encoded information about the vehicle’s acceleration. It contains the longitudinal, lateral, and vertical acceleration, as well as the yaw rate. Please consult page 44 of the SAE J2735 Standard for further information. Note the decoded records from this field are provided in many of the subsequent columns of data that follow this field.
"seqid", -- This field communicate the message content type. All entries in this instance will be “data” indicating that the data portion of the transmitted message is presented.
"path_initialposition_utctime_year", -- This field contains the year in which the VSD message was generated. 0 is used for an unknown value.
"bundle_fundamental_vehsize_width", -- This field contains the width of the vehicle, in centimeters. The width is the widest point of the vehicle with all factory installed equipment. 0 is used when the width is unavailable.
"bundle_time_year", -- This field contains the year in which the bundle was generated.
"bundle_time_day", -- This field contains the day in which the bundle was generated.
"bundle_fundamental_accel_lat", -- This field contains the signed acceleration of the vehicle along the Y axis or perpendicular to the vehicle's direction of travel in parallel with a left-to-right centerline, in units of 0.01 meters per second squared. Negative values indicate left turning action and positive values indicate right-turning action. A range of over 2Gs is supported.
"path_initialposition_utctime_month", -- This field contains the month in which the VSD message was generated. 0 is used for an unknown value.
"bundle_time_minute", -- This field contains the minute in which the bundle was generated.
"path_initialposition_longitude", -- The geographic longitude of the vehicle with reference to the horizontal datum then in use, in degrees. The value 180.0000001 shall be used when unavailable.
"path_crumbdata_pathhistorypointsets_04", -- This field contains encoded latitude, longitude, elevation, and time data for a vehicle’s position. Data is encoded in an octet string. Please consult page 80 of the SAE J2735 Standard for further information. Note the decoded records from this field are provided in many of the subsequent columns of data that follow this field.
"path_initialposition_utctime_day", -- This field contains the day in which the VSD message was generated. 0 is used for an unknown value.
"type", -- This field contains the type of VSD message. When converted to decimal, message types are enumerated as follows: 1- VSD message contains only the fundamental data elements, 2 – the VSD message contains the VehicleStatus data frame, 4 – the VSD message contains weather data, 8 – the VSD message contains environmental data, and 16 – the VSD message contains electric vehicle data
"requestid", -- This field contains the ID (number) of the vehicle situational data message.
"dialogid", -- This field contains the ID of the type of data being transmitted. In this instance all entries will be “vehSitData” indicating that vehicle situation data (VSD) is being transmitted / presented here.
"path_initialposition_utctime_seconds", -- This field contains the second in which the VSD message was generated.
"bundle_pos_latitude", -- The geographic latitude of the intersection with reference to the horizontal datum then in use, in degrees. The value 90.0000001 shall be used when unavailable.
"path_initialposition_latitude", -- The geographic latitude of the vehicle with reference to the horizontal datum then in use, in degrees. The value 90.00000001 shall be used when unavailable.
"bundle_fundamental_speed_transmission", -- This field contains the transmission state of the vehicle. When converted to decimal, transmission states are enumerated as follows: 0 – neutral, speed relative to the vehicle alignment, 1 – park, speed relative to the vehicle alignment, 2 – forward gears, speed relative to the vehicle alignment, 3 – reverse gears, speed relative to the vehicle alignment, 4-6 reserved, 7 – unavailable (vehicle not equipped) and E undefined.
"path_initialposition_elevation_meters", -- The Elevation data element represents the geographic position above or below the reference ellipsoid (typically WSG-84), in meters. This data element initially recorded as a 16-bit (hexadecimal) number has a resolution of 1 decimeter and represents an asymmetric range of positive and negative values. The encoding is as follows: the range 0x0000 to 0xEFFF (0 to 61439 decimal) are positive numbers representing elevations from 0 to +6143.9 meters, i.e. above the reference ellipsoid. The range 0xF001 to 0xFFFF are negative numbers representing elevations from -409.5 meters to -0.1 meters, i.e. below the reference ellipsoid. An elevation higher than +6143.9 meters is represented 0xEFFF. An elevation lower than -409.5 meters is represented 0xF001. If the sending device does not know its elevation it shall encode the Elevation data element with 0xF000. Examples of this encoding are: the elevation 0 meters is encoded as 0x0000. The elevation -0.1 meters is encoded as 0xFFFF. The elevation +100.0 meters is encoded as 0x03E8.
"bundle_tempid" -- This field contains the ID (number) for the bundle.
FROM
"datahub-transportation-gov/2014-its-world-congress-connected-vehicle-test-bed-i5z4-qh9m:latest"."2014_its_world_congress_connected_vehicle_test_bed"
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/2014-its-world-congress-connected-vehicle-test-bed-i5z4-qh9m 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/2014-its-world-congress-connected-vehicle-test-bed-i5z4-qh9m: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/2014-its-world-congress-connected-vehicle-test-bed-i5z4-qh9mCheckout 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/2014-its-world-congress-connected-vehicle-test-bed-i5z4-qh9m:latestThis will download all the objects for the latest tag of datahub-transportation-gov/2014-its-world-congress-connected-vehicle-test-bed-i5z4-qh9m 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/2014-its-world-congress-connected-vehicle-test-bed-i5z4-qh9m: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/2014-its-world-congress-connected-vehicle-test-bed-i5z4-qh9m: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/2014-its-world-congress-connected-vehicle-test-bed-i5z4-qh9m is just another Postgres schema.