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 road_attributes_in_colorado_2016 table in this repository, by referencing it like:
"colorado-gov/road-attributes-in-colorado-2016-j8pu-22qi:latest"."road_attributes_in_colorado_2016"or in a full query, like:
SELECT
":id", -- Socrata column ID
"fips", -- A Domained Value Element (FIPS) Federal Information Processing Standard used to identify uniquely identify citys and counties equivalents in the United States, certain U.S. possessions, and certain freely associated states. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/FIPS
"route", -- A Unique (3 Number - 1 Letter) Highway Identification Code or Designating for a State Highway, Business Route, U.S. Route or Tolled Facility. Odd numbers typically run North and South and Even numbers typically Run East and West. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/Route
"segmid", -- A number that describes an individual segment within a Route. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/segmID
"length_", -- The Centerline Segment Length or Segment Distance (in Miles) to the Thousandth of a Mile. Length_ is the distance from one Road Segment to the next Road Segment. Length_ is a dimension or measurement that is carried out 3-decimal places. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/Length_
"updateyr", -- The Last Calendar year for which the data applies. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/updateYr
"fipscounty", -- A Domained 3-Digit County Code (FIPS, DisplayValue) that Identifies the County in which the individual record is located. (Assigned based on Federal Information Processing Standards). http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/FIPSCounty
"funcclassid", -- A Domained Value Element (funcClassID) that Indicates the functional category and usage limitations of the segment of road, as defined by FHWA, and is broken down between rural and urban areas. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/funcClassID
"govlevel", -- A Domained Value Element (govLevel 1-80) that Indicates the level of government responsible for the naming of the segment of the road and establish traffic controls on the segment as defined by FHWA. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/govLevel
"adminclass", -- A Domained Value Element (adminClass, 0-9) used to identify the Type of Administrative Class to which the roadway segment has been assigned. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/adminClass
"population", -- A Domained Numeric Element (population, 1-4) established by the U.S. Bureau of Census, which categorizes a geographic area by the population count. Rural/Urban. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/population
"urban", -- A Domained 5-Digit Numeric Code (urban) that Indicates if the Road Segment is within an Urban Boundary as established by the U.S. Bureau of census, which categorizes a geographic area by the population count. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/Urban
"naaqsid", -- A Domained Value Element (NAAQSID) used to identify whether the Road Segment is within an EPA designated nonattainment area. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/NAAQSID
"nhsdesig", -- A Domained Value Element (NHSDesig: 0-9) used to identify whether the Road Segment is designated as being part of the National Highway System. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/NHSDesig
"specialsys", -- A Domained Value Element (specialSys 0-2) used to code the special funding categories in which some existing and open to traffic highway segments fall. These special systems are separate and distinct from those outlined in previously defined fields. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/specialSys
"access_", -- A Domained Value Code (access) used to identify the type of Access Control assigned to a Road Segment as defined by FHWA. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/ACCESS_
"trkrestrict", -- A Domained Value Element (trkRestrict 0-1) used to identify a Road Segment that has been Designated as a Truck Route under Federal Regulatory Authority in 23 CFR 658. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/trkRestrict
"priiri", -- International roughness index (IRI) correlates somewhat with human exposure to whole-body vibration in vehicles and thus to perceived ride quality reading for the surface condition in the Primary Direction of Travel. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/priIRI
"priiridate", -- The calendar year for which the IRI applies on the Primary Direction of Travel. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/priIRIdate
"pripsi", -- Present Serviceability Index rating measures the longitudinal roughness, patch work, rutting and cracking within a road segment in the Primary Direction of Travel. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/priPSI
"projyr", -- The Project Improvement Year is used by FHWA as part of HPMS to track the Year of Record on Reconstructed Road Segments, Re-Aligned Road Segments and the Surface Improvement Activities on Road Segments. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/projYr
"builtyr", -- The Year of Original Roadway Construction or the Year of the Last Major Change in Roadway Re-Construction. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/builtYr
"inspyr", -- A 4-Digit Number that Represents the Last recorded Year that that a CDOT field Inspection was Done. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/inspYR
"pritreatmentdepth", -- Thickness of the most recent pavement overlay to the nearest .0.5 inch
"prisurf", -- A Domained Value Code (SurfaceType) an indicator of the type of material used in the construction of the roadway driving surface as defined by FWHA. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/priSurf
"prisurfwd", -- Total width of the driving surface, in whole feet, for the primary direction of traffic. (Legacy Attribution). http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/priSurfWd
"thrulnqty", -- The prevailing number of lanes carrying through traffic in both directions of Traffic, excluding, parking, turning, auxiliary, climbing, acceleration or deceleration lanes. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/thruLnQty
"prithrulnwd", -- The predominate width of the lane carrying through traffic in both directions of Traffic, excluding, parking, turning, auxiliary, climbing, acceleration or deceleration lanes.
"operation", -- A Domained Value Element (operation) that Identifies a Roadway as a One-Way or Two-Way Operation, Bridge, Tunnel or Causeway. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/Operation
"rrxid", -- An identifier assigned to the railroad crossing by the Federal Rail Administration to identify the crossing. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/rrxID
"strid", -- A Unique Structure Identification Code that is Associated to an Interchange, an Intersection, Roadway Crossing, Anchor or Node. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/strID
"region", -- A Domained Value Element (Region: 1-6) used to identify the Engineering Region number in which the Road Segment is located. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/Region
"tprid", -- The Transportation Planning District (1-15) in which the individual record is located. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/TPRID
"terrain", -- A Domained Value Element (terrain 0-3) used to identify the Predominant type of terrain through which the road segment passes as specified by AASHTO. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/terrain
"forestroute", -- A numbering system established by the U.S. Forest Service to identify national Forest access roads. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/forestRoute
"routesign", -- A Domained Value Element (routeSign) that Indicates a type of Interstate, State or U.S. Highway or Business Route relative to the manner in which it is signed. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/routeSign
"routesignqual", -- A Domained Value Element (routeSignQual) that indicates if the particular segment of road is designated as being a Business Route, Bypass, Alternate Route, Loop, Spur etc. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/routeSignQual
"jursplit", -- Denotes a Road Segment with a shared Maintenance Agreement. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/jurSplit
"routename", -- The complete locally designated name of the roadway segment. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/routeName
"fromfeature", -- A referenced beginning point of a road segment. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/fromFeature
"tofeature", -- The ending intersecting road of the street segment. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/toFeature
"segmdir", -- A General Running Direction of the Road segment heading to North, NorthEast, NorthWest, South, SouthEast, SouthWest, East or West. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/SEGMDIR
"segmprefix", -- One of four directional road name prefixes: East, West, South, North. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/SEGMPREFIX
"gisid", -- A Unique but flexible Feature Identifier generated by Concatenating ([FIPS], [Route] and [SegmID]) used as foreign key by ArcMap. (Yearly Derived). http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/GISID
"countstationid", -- A 6-Digit Numeric Designation or Identification Number that Represents the Location where Independent Traffic Volume Groups and Data Collection Efforts are being performed on a Road Segment. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/countStationID
"guid", -- A 36 character string that uniquely identifies a feature or table row within a geodatabase and across geodatabases.
"lrsroute", -- A Unique Route Identification Code used in the run-length based LRS that is submitted to HPMS
"frommeas", -- A Linear Reference Number or Address Key (to the Thousandth of a Mile) Representing the Starting Point of a roadway
"tomeas", -- A Linear Reference Number or Address Key (to the Thousandth of a Mile) Representing the Ending Point of a roadway Segment. Based on a validated run-length measure of a segment along a Route.
"priyrrehab", -- Year roadway segment was rehabilitated with pavement overlay
"isdivided", -- Is the roadway segment predominately divided by a positive median. For future collection]
"fundid" -- unknown, local or NonQual. Funding source: Local, Federal, or non-qualifying.
FROM
"colorado-gov/road-attributes-in-colorado-2016-j8pu-22qi:latest"."road_attributes_in_colorado_2016"
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 colorado-gov/road-attributes-in-colorado-2016-j8pu-22qi 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 colorado-gov/road-attributes-in-colorado-2016-j8pu-22qi: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 colorado-gov/road-attributes-in-colorado-2016-j8pu-22qiCheckout 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 colorado-gov/road-attributes-in-colorado-2016-j8pu-22qi:latestThis will download all the objects for the latest tag of colorado-gov/road-attributes-in-colorado-2016-j8pu-22qi 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 colorado-gov/road-attributes-in-colorado-2016-j8pu-22qi: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 colorado-gov/road-attributes-in-colorado-2016-j8pu-22qi: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, colorado-gov/road-attributes-in-colorado-2016-j8pu-22qi is just another Postgres schema.