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 tampa_cv_pilot_traveler_information_message_tim table in this repository, by referencing it like:
"datahub-transportation-gov/tampa-cv-pilot-traveler-information-message-tim-in46-gmir:latest"."tampa_cv_pilot_traveler_information_message_tim"or in a full query, like:
SELECT
":id", -- Socrata column ID
"travelerdataframe_closedpath", -- Boolean field indicating if the geographical path in the TIM is closed or not - when true, last point closes to first. Assume that omitting element indicates closedPath == false. This field is based on the J2735 Standard.
"travelerinformation_timestamp", -- The number of elapsed minutes of the current year in the time system being used (typically UTC time). It is typically used to provide a longer range time stamp indicating when a message was created. This field is based on the J2735 Standard.
"travelerdataframe_anchor_4", -- Regional extension of geographical path described. This field is based on the J2735 Standard.
"travelerdataframe", -- Direction of use (forward, reverse, both, unavailable) - the allowed direction of travel on a street lane or path described by shape points. The presumed (default) direction is outward, away from the initial set of points. However, this data element can be used indicate a reverse direction or both directions as well as the original outward direction. This field is based on the J2735 Standard.
"travelerdataframe_desc_nodes", -- Description of a system of paths. This field is a stringified array of json objects and is based on the J2735 Standard.
"travelerdataframe_msgid_lat", -- Geographic latitude at start of speed recommendation, in units of 1/10th microdegrees. The value 900000001 shall be used when unavailable. This field is based on the J2735 Standard.
"travelerdataframe_msgid_msgcrc", -- Octet string used to provide a check sum. This field is based on the J2735 Standard.
"travelerdataframe_starttime", -- Start time of message - the number of elapsed minutes of the current year in the time system being used (typically UTC time). This field is based on the J2735 Standard.
"randomnum", -- Random decimal number, to be used for random sampling of data within Socrata. This field is created for use within Socrata and is not present in the data Sandbox.
"metadata_generatedat_timeofday", -- Time of day when the record was created, in unit of hour, based on the "metadata_generatedAt" field. This field is created for use within Socrata and is not present in the data Sandbox.
"travelerdataframe_content_5", -- Roadside available services - contains ITIS codes interspersed with free text. This field is a stringified json object and is based on the J2735 Standard.
"travelerdataframe_desc_scale", -- Scaling factor of geographical path described. The zoom value of zero implies a 1:1 scale (no zoom), while a larger value implies an increase by a power of X = 2^z, where z is the zoom scale. This field is based on the J2735 Standard.
"travelerdataframe_3", -- SSP Index for message content. The ability to send TIM Part III content information is controlled by this SSPIndex which links back to the sender's CERT. This is set to 0 in this dataset. This field is based on the J2735 Standard.
"metadata_logfilename", -- Name of the original file that deposited the message.
"travelerdataframe_anchor_2", -- Geographic longitude at start of speed recommendation, in units of 1/10th microdegrees. The value 1800000001 shall be used when unavailable. This is redundant with road sign position. This field is based on the J2735 Standard.
"travelerdataframe_lanewidth", -- Width of a lane in cm. Maximum value for a lane is 327.67 meters in width. This field is based on the J2735 Standard.
"travelerdataframe_startyear", -- Start Year of message. The year according to the Gregorian calendar date system. The value of zero shall represent an unknown value. This field is based on the J2735 Standard.
"travelerdataframe_anchor_1", -- Geographic latitude at start of speed recommendation, in units of 1/10th microdegrees. The value 900000001 shall be used when unavailable. This is redundant with road sign position. This field is based on the J2735 Standard.
"datatype", -- The data type.
":@computed_region_28hd_vqqn",
"travelerdataframe_name", -- Human readable and recognizable name for the spatial areas it describes. This field is based on the J2735 Standard.
"travelerinformation_regional", -- Regional extension of Traveler Information. This field is based on the J2735 Standard.
"travelerdataframe_msgid_5", -- Regional extension of Traveler Information. This field is based on the J2735 Standard.
"travelerdataframe_id_id", -- Road Segment ID - a unique mapping to the road segment in question within the Road Regular ID region of use during its period of assignment and use. Note that unlike intersection ID values, this value can be reused by the region. This field is based on the J2735 Standard.
"travelerdataframe_ssptimrights", -- SSP Index for TIM. The ability to send TIM is controlled by this SSPIndex which links back to the sender's CERT. This is set to 0 in this dataset. This field is based on the J2735 Standard.
"travelerdataframe_msgid_4", -- Further Info ID - a link number to other messages (described here and in other message set standards) which relate to the same event. Use zero when unknown or not present. This field is based on the J2735 Standard.
"travelerdataframe_msgid_3", -- Vehicle direction of travel while facing active side of sign, in the format of bit string. Each bit 22.5 degree starting from North and moving Eastward (clockwise) as one bit. A value of noHeading means no bits set, while a value of allHeadings means all bits would be set. This field is based on the J2735 Standard.
"travelerdataframe_content_4", -- Speed limits and cautions - contains ITIS codes interspersed with free text. This field is a stringified json object and is based on the J2735 Standard.
"metadata_schemaversion", -- Version of the metadata schema.
"metadata_psid", -- Provider Service Identifier. A number that identifies a service provided by an application. PSID is defined in IEEE Std 1609.12.
"travelerdataframe_priority", -- The relative importance of the sign, on a scale from zero (least important) to seven (most important). This field is based on the J2735 Standard.
"travelerinformation_urlb", -- A valid internet style URI / URL in the form of a text string which will form the base of a compound string which, when combined with the URL-Short data element, will link to the designated resource. This field is based on the J2735 Standard.
"travelerdataframe_content_2", -- Work zone signs and directions - contains ITIS codes interspersed with free text. This field is a stringified json object and is based on the J2735 Standard.
"metadata_kind", -- Metadata kind.
"travelerdataframe_msgid", -- Elevation location at start of speed recommendation, in units of 10cm above or below the reference ellipsoid. Providing a range of -409.5 to +6143.9 meters. The value -4096 shall be used when Unknown is to be sent. This field is based on the J2735 Standard.
"travelerdataframe_content", -- Advisory ITIS warnings - ITIS codes interspersed with free text. The complete set of ITIS codes can be found in Volume Two of the J2540 Standard. This is a set of nearly 1,500 items which are used to encode common events and list items in ITS. This field is a stringified json object and is based on the J2735 Standard.
"travelerinformation_packetid", -- Packet ID - a relatively unique value which can be used to connect to (link to) other supporting messages in other formats. This field is based on the J2735 Standard.
"travelerdataframe_id_region", -- Road regulator ID - a globally unique regional assignment value typically assigned to a regional DOT authority. The value zero shall be used for testing needs. This field is based on the J2735 Standard.
"travelerinformation_msgcnt", -- Message count, to be increased whenever the content of any TravelerDataFrame changes. The receipt of a non-sequential MsgCount value (from the same sending device and message type) implies that one or more messages from that sending device may have been lost, unless MsgCount has been re-initialized due to an identity change. This field is based on the J2735 Standard.
"travelerdataframe_msgid_1", -- Tag for MUTCD code (none, regulatory, warning, maintenance, motoristService, guide, rec) or "generic sign". MUTCD has no appropriate code for an ERDW speed advisory or wrong-way driver warning. This field is based on the J2735 Standard.
"travelerdataframe_msgid_long", -- Geographic longitude at start of speed recommendation, in units of 1/10th microdegrees. The value 1800000001 shall be used when unavailable. This field is based on the J2735 Standard.
"travelerdataframe_content_3", -- Generic MUTCD signs and directions - contains ITIS codes interspersed with free text. This field is a stringified json object and is based on the J2735 Standard.
"travelerdataframe_frametype", -- The type of message to follow in the rest of the message frame structure (advisory or road sign). This field is based on the J2735 Standard.
"travelerdataframe_duratontime", -- Duration of validity of this speed recommendation or wrong-way driver warning, in units of whole minutes, that a object persists for. A value of 32000 means that the object persists forever. This field is based on the J2735 Standard.
"travelerdataframe_msgid_2", -- Geographic point at start of speed recommendation, composed of (travelerdataframe_msgId_long, travelerdataframe_msgId_lat) in degrees. This field is created for use within Socrata and is not present in the data Sandbox.
"metadata_generatedat", -- Closest time to which the record was created, either signed or received by the generatedBy source in UTC format. This information is taken from the communication header.
"travelerdataframe_1", -- SSP Index for locations. The ability to send TIM Part II regions information is controlled by this SSPIndex which links back to the sender's CERT. This is set to 0 in this dataset. This field is based on the J2735 Standard.
"metadata_generatedby", -- Source of the record, whether [OBU, RSU, TMC].
"travelerdataframe_2", -- SSP Index for message types. The ability to send TIM Part III message type information is controlled by this SSPIndex which links back to the sender's CERT. This is set to 0 in this dataset. This field is based on the J2735 Standard.
"travelerdataframe_direction", -- Heading slice - field of view over which the TIM geographical path applies. In the format of bit string. Each bit 22.5 degree starting from North and moving Eastward (clockwise) as one bit. A value of noHeading means no bits set, while a value of allHeadings means all bits would be set. This field is based on the J2735 Standard.
"metadata_rsuid", -- Identifier of road side unit.
"travelerdataframe_anchor" -- Elevation at start of speed recommendation, in units of 10cm above or below the reference ellipsoid. Providing a range of -409.5 to +6143.9 meters. The value -4096 shall be used when Unknown is to be sent. This is redundant with road sign position. This field is based on the J2735 Standard.
FROM
"datahub-transportation-gov/tampa-cv-pilot-traveler-information-message-tim-in46-gmir:latest"."tampa_cv_pilot_traveler_information_message_tim"
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/tampa-cv-pilot-traveler-information-message-tim-in46-gmir 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/tampa-cv-pilot-traveler-information-message-tim-in46-gmir: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/tampa-cv-pilot-traveler-information-message-tim-in46-gmirCheckout 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/tampa-cv-pilot-traveler-information-message-tim-in46-gmir:latestThis will download all the objects for the latest tag of datahub-transportation-gov/tampa-cv-pilot-traveler-information-message-tim-in46-gmir 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/tampa-cv-pilot-traveler-information-message-tim-in46-gmir: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/tampa-cv-pilot-traveler-information-message-tim-in46-gmir: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/tampa-cv-pilot-traveler-information-message-tim-in46-gmir is just another Postgres schema.