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
"runlength1",
"jursplit", -- Denotes a Road Segment with a shared Maintenance Agreement. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/jurSplit
"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.
"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
"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
"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
"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
"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
"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
"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
"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
"from_descr", -- this segment starts from this landmark
"routename", -- The complete locally designated name of the roadway segment. http://dtdapps.coloradodot.info/otis/catalog/CiCoOff/routeName
"segmid", -- A number that describes an individual segment within a Route. Associated roads are broken into logical segments for record keeping
"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.
"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
"lrsroute", -- Linear Referencing System CDOT internal placeholder fields
"builtyr", -- The Year of Original Roadway Construction or the Year of the Last Major Change in Roadway Re-Construction.
"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
"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
"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
"to_descr", -- this segment ends at this landmark
"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
"surfname", -- Field Description:Name of the surface type (3 Concrete; 16 Soil, Gravel, or Stone; 15 Graded & Drained; 14 Unimproved; 11 Other; 1 Asphalt)
"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
"seg_length", -- Length of segment in miles
"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
"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
"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
"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
"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.
"aadt", -- The annual average daily traffic count for the segment. (Total of all vehicles counted in a year divided by 365 days)
"runlength_",
"thrulnwd",
"fundid", -- unknown, local or NonQual. Funding source: Local, Federal, or non-qualifying.
"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
"the_geom"
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.
This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.colorado.gov. When you querycolorado-gov/road-traffic-counts-in-colorado-2018-bk6n-e4g7: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 data.colorado.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 \
"colorado-gov/road-traffic-counts-in-colorado-2018-bk6n-e4g7" \
--handler-options '{
"domain": "data.colorado.gov",
"tables": {
"road_traffic_counts_in_colorado_2018": "bk6n-e4g7"
}
}'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, colorado-gov/road-traffic-counts-in-colorado-2018-bk6n-e4g7 is just another Postgres schema.