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 inmate_population table in this repository, by referencing it like:
"delaware-gov/inmate-population-vnau-c4rn:latest"."inmate_population"or in a full query, like:
SELECT
":id", -- Socrata column ID
"year", -- Indicates the Calendar Year for which monthly snapshot data is partially or fully available
"ethnicity", -- Hispanic- Indicates that the individual’s ethnicity is Hispanic Non-Hispanic- Indicates that the individual’s ethnicity is Non-Hispanic
"institution", -- ABSC- Indicates that the individual has absconder status; individual not at a correctional institution AOD- Indicates that the individual is technically still under the original jurisdiction of another state, but Delaware has temporary custody. AOD is a method for an offender in another jurisdiction’s custody to come to Delaware to answer for new charges. ATR- Indicates that the individual is currently incarcerated in another state; individual will be returned to Delaware after out of state matter resolved BWCI- Indicates that the individual is incarcerated at Baylor Women’s Correctional Institution (women’s Level V prison facility). CC- Indicates that the individual is incarcerated at a correctional facility out of state due to a Compact Case CVOP- Indicates that the individual is incarcerated at Central Violation of Probation Center (men’s Level IV community corrections facility) DO51- Indicates that the individual is a probationer or parolee under Interstate Probation & Parole DO52- Indicates that the individual is a probationer or parolee at the Northern New Castle County Adult Probation & Parole (“Cherry Lane”) DO53- Indicates that the individual is a probationer or parolee at Dover Probation & Parole DO54- Indicates that the individual is a probationer or parolee at Georgetown Probation & Parole DO55- Indicates that the individual is a probationer or parolee at Seaford Probation & Parole DO56- Indicates that the individual is a probationer or parolee at New Castle Probation & Parole DO57- Indicates that the individual is a probationer or parolee at Georgetown Probation (Admin Services) DPC- Indicates that the individual is committed as patient at the Delaware Psychiatric Center (operated by the Delaware Division of Substance Abuse and Mental Health) HCK- Indicates that the individual is supervised on home confinement in Kent County HCN- Indicates that the individual is supervised on home confinement in New Castle County HCS- Indicates that the individual is supervised on home confinement in Sussex County HDP- Indicates that the individual is incarcerated at Hazel D. Plant Women’s Treatment Facility (women’s Level IV community corrections facility) HRYCI- Indicates that the individual is incarcerated at Howard R. Young Correctional Institution (men’s Level V prison facility) JTVCC- Indicates that the individual is incarcerated at James T. Vaughn Correctional Center (men’s Level V prison facility) MCCC- Indicates that the individual is incarcerated at Morris Community Corrections Center (men’s Level IV community corrections facility) PCCC- Indicates that the individual is incarcerated at Plummer Community Corrections Center (men’s Level IV community corrections facility) RSTK- Indicates that the individual is supervised by Probation & Parole for repayment of court-ordered restitution in Kent County RSTN- Indicates that the individual is supervised by Probation & Parole for repayment of court-ordered restitution in New Castle County RSTS- Indicates that the individual is supervised by Probation & Parole for repayment of court-ordered restitution in Sussex County SCCC- Indicates that the individual is incarcerated at Sussex Community Corrections Center (includes Sussex Work Release Center and Sussex Violation of Probation Center) (male and female Level IV community corrections facility) SCI- Indicates that the individual is incarcerated at Sussex Correctional Institution (men’s Level V prison facility) SCK- Indicates that the individual is a Level V offender who is supervised in the community by Probation & Parole in Kent County. SCN Indicates that the individual is a Level V offender who is supervised in the community by Probation & Parole in New Castle County. SCS- Indicates that the individual is a Level V offender who is supervised in the community by Probation & Parole in Sussex County. WCF—Indicates that the individual was incarcerated at Webb Correctional Facility (men’s Level IV community corrections facility). This facility was closed in February 2017 and inmates were moved to Plummer Community Corrections Center. WK- Indicates that the individual serves a sentence at a correctional facility (can be Level IV or V) on weekends.
"sentence_type", -- Death- Indicates that the individual is sentenced to the death penalty. Detentioner- Indicates that the individual is detained in a correctional facility facing criminal charges; individual has not been convicted and sentenced. Home Confinement- Indicates that the individual is supervised by Probation & Parole on home confinement. Indefinite- Indicates that the individual is serving a sentence of open-ended duration pending satisfaction of certain conditions/terms Jail- Indicates that the individual’s sentence is shorter than 12 months (note: Delaware has a unified system that does not have separate facilities for jail inmates and prison inmates; the term “jail” is used here only to indicate sentence length and does not denote being housed at a separate correctional facility from the prison population) Life- Indicates that the individual has been sentenced to a life sentence Other- Indicates a sentence type not categorized by the other available descriptors Prison- Indicates that the individual’s sentence is longer than 12 months (note: Delaware has a unified system that does not have separate facilities for jail inmates and prison inmates; the term “prison” is used here only to indicate sentence length and does not denote being housed at a separate correctional facility from the jail population) Probation- Indicates that the individual is serving a sentence of Probation or Parole Restitution- Indicates that the individual is under community supervision by Probation & Parole for repayment of court-ordered restitution
"offender_count",
"race", -- American Indian/Alaskan- Indicates that the individual is American Indian or Alaskan Native Asian/Pacific Islander- Indicates that the individual is Asian or Pacific Islander Black- Indicates that the individual is Black/African American Unknown- Indicates that the individual’s race is unknown White- Indicates that the individual is White/Caucasian
"gender", -- Male- Indicates that the individual is male Female- Indicates that the individual is female
"month", -- Indicates the calendar month for which the end of the month population snapshot count is provided. For example, selecting “January” will provide a snapshot population count taken on the last day of that month, January 31, on the year selected in the preceding column
"type_of_institution", -- Home Confinement- Indicates that the individual is being supervised on home confinement. Other- Indicates that the individual is held on Agreement on Detainer, Agreement to Return, Compact Case, Weekend Custody , or they are an Absconder Prison- Indicates that the individual is held at a Level V prison facility operated by the DOC Bureau of Prisons. Includes Baylor Women’s Correctional Institution (BWCI), James T. Vaughn Correctional Center (JTVCC), Howard R. Young Correctional Institution (HRYCI), and Sussex Correctional Institution (SCI). Probation- Indicates that the individual is under supervision of the Bureau of Community Corrections, Department of Probation & Parole. Includes SENTAC supervision levels I, II, and III as well as Pre-Trial Supervision. Psychiatric Center- Indicates that the individual is housed at Delaware Psychiatric Center. Restitution- Indicates that the individual is not incarcerated but is under supervision for fulfillment of court-ordered restitution. Supervised Custody- Indicates that the individual is a Level V offender who is supervised in the community by Probation & Parole. Supervised custody offenders must report to correctional facilities on weekends for breathalyzer tests. Work Release/VOP- Indicates that the individual is held at a Level IV Bureau of Community Corrections facility. Includes Central Violation of Probation Facility, Hazel D. Plant Women’s Treatment Center, Morris Community Corrections Center, Plummer Community Corrections Center, and Sussex Community Corrections Center (includes Sussex Work Release Center and Sussex Violation of Probation Center.
"county_name" -- New Castle- Indicates that the facility in the next column is located in New Castle County Kent- Indicates that the facility in the next column is located in Kent County Sussex- Indicates that the facility in the next column is located in Sussex County
FROM
"delaware-gov/inmate-population-vnau-c4rn:latest"."inmate_population"
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 delaware-gov/inmate-population-vnau-c4rn 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 delaware-gov/inmate-population-vnau-c4rn: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 delaware-gov/inmate-population-vnau-c4rnCheckout 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 delaware-gov/inmate-population-vnau-c4rn:latestThis will download all the objects for the latest tag of delaware-gov/inmate-population-vnau-c4rn 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 delaware-gov/inmate-population-vnau-c4rn: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 delaware-gov/inmate-population-vnau-c4rn: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, delaware-gov/inmate-population-vnau-c4rn is just another Postgres schema.