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_traffic_counts_in_colorado_2018 table in this repository, by referencing it like:
"colorado-gov/road-traffic-counts-in-colorado-2018-bk6n-e4g7:latest"."road_traffic_counts_in_colorado_2018"or in a full query, like:
SELECT
":id", -- Socrata column ID
"the_geom",
"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
"fips", -- A 3-digit or 5-digit Federal Information Processing Standard (FIPS) code that is used to uniquely identify city and county equivalents in the United States, certain U.S. possessions, and certain freely associated states. http://dtdapps.coloradodot.info/otis/catalog/CICOHPMS/FIPS
"route", -- The number designating the State Route, including a section identifier designated by a letter for the State database. This is what the local jurisdiction owners call their routes/streets. These are not CDOT administered. No lookup or dictionary.
"segmid", -- A number that describes an individual segment within a Route. Associated roads are broken into logical segments for record keeping
"seg_length", -- Length of segment in miles
"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/ClassOn/FIPSCounty
"routename", -- The complete locally designated name of the roadway segment. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/routeName
"from_descr", -- this segment starts from this landmark
"segmdir", -- A General Running Direction of the Road segment heading to North, NorthEast, North West, South, SouthEast, SouthWest, East or West. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/SEGMDIR
"to_descr", -- this segment ends at this landmark
"funcclassi", -- 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
"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
"hpmsid", -- A unique 12-character identifier for the sample section. ID used by the federal Highway Performance Monitoring System program - http://www.fhwa.dot.gov/policyinformation/hpms.cfm. http://dtdapps.coloradodot.info/otis/catalog/CICOHPMS/HPMSID
"surf_type", -- An indication of the type of material used in the construction of the surface of the road in the direction of increasing mileposts which is generally west to east, and south to north. 1 Bladed (dirt smoothed by grader), 2 Gravel, 3 Paved
"surfname", -- Field Description:Name of the surface type (3 Concrete; 16 Soil, Gravel, or Stone; 15 Graded & Drained; 14 Unimproved; 11 Other; 1 Asphalt)
"surfwd", -- 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
"psi", -- Present Serviceability Index rating measures the longitudinal roughness, patch work, rutting and cracking within a road segment in the Primary Direction of Travel. Serviceability is quantified by the Present Serviceability Index, PSI. Although PSI theoretically ranges between 5 and 0, the actual range for real pavements is between about 4.5 to 1.5. – The 24 could be an error. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/priPSI
"iri", -- Primary 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. Values not available. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/priIRI
"thrulnqty", -- The prevailing number of lanes carrying through traffic in the in both directions of Traffic, excluding, parking, turning, auxiliary, climbing, acceleration or deceleration lanes. http://dtdapps.coloradodot.info/otis/catalog#statewide-tab
"thrulnwd",
"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
"urban", -- [need clarification, some values null, some 0] 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. 0 and null both mean non-Urban. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/Urban
"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
"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. Some rows in Major Roads will have these populated; Local Roads do not typically carry this data. 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. Some rows in Major Roads will have these populated; Local Roads do not typically carry this data. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/specialSys
"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 Federal Highway Act/Administration (FHWA). http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/govLevel
"aadt", -- The annual average daily traffic count for the segment. (Total of all vehicles counted in a year divided by 365 days)
"aadtyr", -- The calendar year (YYYY) in which the annual average daily traffic count applies for the highway segment.
"aadtderiv", -- A Domained Value Element (AADTDeriv) indicating the Code Associated with the Factoring Method used in calculating the AADT determination Value.
"countstati", -- 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.
"builtyr", -- The Year of Original Roadway Construction or the Year of the Last Major Change in Roadway Re-Construction.
"jursplit", -- Denotes a Road Segment with a shared Maintenance Agreement. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/jurSplit
"forestrout", -- A numbering system established by the U.S. Forest Service to identify national Forest access roads. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/forestRoute
"lrsroute", -- Linear Referencing System CDOT internal placeholder fields
"runlength_",
"runlength1",
"fundid" -- unknown, local or NonQual. Funding source: Local, Federal, or non-qualifying.
FROM
"colorado-gov/road-traffic-counts-in-colorado-2018-bk6n-e4g7:latest"."road_traffic_counts_in_colorado_2018"
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-traffic-counts-in-colorado-2018-bk6n-e4g7 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-traffic-counts-in-colorado-2018-bk6n-e4g7: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-traffic-counts-in-colorado-2018-bk6n-e4g7Checkout 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-traffic-counts-in-colorado-2018-bk6n-e4g7:latestThis will download all the objects for the latest tag of colorado-gov/road-traffic-counts-in-colorado-2018-bk6n-e4g7 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-traffic-counts-in-colorado-2018-bk6n-e4g7: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-traffic-counts-in-colorado-2018-bk6n-e4g7: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-traffic-counts-in-colorado-2018-bk6n-e4g7 is just another Postgres schema.