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 small_area_profile_supervisory_district_level_2023 table in this repository, by referencing it like:
"sccgov/small-area-profile-supervisory-district-level-2023-n57v-y6s6:latest"."small_area_profile_supervisory_district_level_2023"or in a full query, like:
SELECT
":id", -- Socrata column ID
"suicide_deaths_per_100_000", -- Suicide rate per 100,000 people
"firearm_deaths_per_100_000", -- Firearm death rate per 100,000 people
"homicide_deaths_per_100_000", -- Homicide rate per 100,000 people
"influenza_and_pneumonia_deaths", -- Influenza and pneumonia death rate per 100,000 people
"alzheimer_s_disease_deaths", -- Alzheimer's disease death rate per 100,000 people
"chronic_lower_respiratory", -- Chronic lower respiratory disease death rate per 100,000 people
"diabetes_deaths_per_100_000", -- Diabetes death rate per 100,000 people
"stroke_deaths_per_100_000", -- Stroke death rate per 100,000 people
"hypertension_deaths_per_100", -- Hypertension (high blood pressure) death rate per 100,000 people
"heart_disease_deaths_per", -- Heart disease death rate per 100,000 people
"cancer_deaths_per_100_000", -- Cancer death rate per 100,000 people
"teen_live_births_per_1_000", -- Teen live birth rate per 1,000 females ages 15 to 19 years
"overweight_or_obese_in_1st", -- Percent of mothers who were overweight or obese in the 1st trimester of pregnancy
"preterm_births", -- Preterm birth rate per 100 live births
"low_birth_weight_infants", -- Low birth weight rate per 100 live births
"births_per_1_000_people", -- Birth rate per 1,000 people
"covid_booster_vaccination", -- COVID-19 booster vaccination rate
"covid_complete_vaccination", -- COVID-19 complete vaccination rate
"covid_test_rate_per_100_000", -- COVID-19 test rate per 100,000 people
"_5_yr_average_of_early", -- Early syphilis new diagnosis rate per 100,000 people (5-year average)
"gonorrhea_new_diagnosis_per", -- Gonorrhea new diagnosis rate per 100,000 people
"chlamydia_new_diagnosis_per", -- Chlamydia new diagnosis rate per 100,000 people
"_5_yr_average_of_tb_new", -- Tuberculosis (TB) new diagnosis rate per 100,000 people (5-year average)
"cognitive_disability", -- Percent of population living with a cognitive disability
"ambulatory_disability", -- Percent of population living with an ambulatory disability
"healthcare_coverage_age_limit", -- Percent of population ages 19 to 64 years with healthcare insurance
"number_of_alcohol_retail", -- Number of alcohol retail outlets per square mile
"housing_units_without_complete", -- Percent of housing units without complete plumbing facilities
"housing_units_heated_by_gas", -- Percent of housing units that were heated by gas fuel source
"housing_units_build_before", -- Percent of housing units built before 1980
"overcrowded_households", -- Percent of overcrowded households
"homeowner_cost_burden", -- Percent of owner-occupied households with monthly expenses accounting for 30% or more of household income
"home_ownership_rate", -- Home ownership rate
"households_with_gross_rent", -- Percent of renter-occupied households with gross rent accounting for 30% or more of household income
"renter_households", -- Percent of renter occupied households
"vacant_housing_units", -- Count of vacant housing units
"housing_units", -- Count of housing units
"households_receiving_calfresh", -- Percent of households which received Cal-Fresh benefits in the past 12 months
"other_means", -- Percent of population ages 16 years and older who used some other means to commute to work
"work_from_home", -- Percent of population ages 16 years and older who worked from home
"bicycle", -- Percent of population ages 16 years and older who rode a bicycle to work
"carpooled", -- Percent of population ages 16 years and older who carpooled to work
"drove_alone", -- Percent of population ages 16 years and older who drove alone to work
"population_that_resides_within", -- Percent of population that resides within 1/2 mile of transit stop with a headway of 15 minutes or less during peak commute hours
"household_with_vehicle_access", -- Percent of households with vehicle access
"number_of_motor_vehicle", -- Average annual count of motor vehicle collisions
"number_of_vehicle_bicycle", -- Average annual count of vehicle-bicycle injury collisions
"college_graduate_or_higher", -- Percent of adults ages 25 years and older with bachelor's degree or higher education attainment
"high_school_graduate", -- Percent of adults ages 25 years and older with a high school diploma
"less_than_high_school", -- Percent of adults ages 25 years and older with less than high school education attainment
"high_school_enrollment_15", -- Percent of children ages 15-17 years enrolled in a high school
"children_ages_3_4_enrolled", -- Percent of children ages 3-4 years enrolled in a preschool or a nursery school
"total_population_200_fpl", -- Percent of total population living below 200% of Federal Poverty Level
"children_ages_0_17_below", -- Percent of children ages less than 18 years living below 200% of Federal Poverty Level
"families_below_200_fpl", -- Percent of families living below 200% of Federal Poverty Level
"employed_25_64_years", -- Percent of civilian population ages 25-64 with employment
"median_household_income", -- Median household income
"per_capital_income", -- Per Capita Income
"u_s_citizenship", -- Percent of foreign-born population with U.S. citizenship
"elderly_living_alone", -- Percent of population 65 years and older living alone
"average_household_size", -- Average household size
"foreign_born", -- Percent of foreign-born population
"people_living_in_group_quarter", -- Count of population living in group quarters
"latino", -- Percent of Latino population
"pacific_islander", -- Percent of Native Hawaiian and Pacific Islander, non-Hispanic population
"asian", -- Percent of Asian, non-Hispanic population
"african_american", -- Percent of African American, non-Hispanic population
"white", -- Percent of White, non-Hispanic population
"ages_65_and_older", -- Percent of population ages 65years and older
"_25_34_years", -- Percent of population ages 25 to 34 years
"_10_14_years", -- Percent of population ages 10 to 14 years
"male", -- Percent of male population
"supervisor_district", -- Geography name
"_18_24_years", -- Percent of population ages 18 to 24 years
"liguistically_isolated", -- Percent of linguistically isolated households
"_15_17_years", -- Percent of population ages 15 to 17 years
"number_of_tobacco_retail", -- Number of tobacco retail outlets per square mile
"covid_case_rate_per_100_000", -- COVID-19 case rate per 100,000 people
"_45_54_years", -- Percent of population ages 45 to 54 years
"travel_time_to_work_over", -- Percent of population ages 16 years and older whose work commute time is more than 30 minutes
"_5_yr_average_of_hiv_new", -- HIV new diagnosis rate per 100,000 people (5-year average)
"bipoc_black_indigenous_and", -- Percent of Black, Indigenous and People of Color population
"mothers_who_received_early", -- Percent of mothers who received early and adequate prenatal care during pregnancy
"single_parent_households", -- Percent of households with a single parent
"housing_units_without_internet", -- Percent of housing units without internet access
"_5_9_years", -- Percent of population ages 5 to 9 years
"households_without_full_time", -- Percent of households without a full-time year-around employment
"speaks_english_less_than", -- Percent of population ages 5 and older who speak English 'less than well'
"average_distance_miles_to_1", -- Average distance (in miles) to the nearest park or open space
"_55_64_years", -- Percent of population ages 55 to 64 years
"_0_4_years", -- Percent of population ages less than 5 years
"_35_44_years", -- Percent of population ages 35 to 44 years
"average_distance_miles_to", -- Average distance (in miles) to the nearest farmers' market
"outdoor_occupation_workers", -- Percent of population ages 16 years and older who work in outdoor occupations
"number_of_vehicle_pedestrian", -- Average annual count of vehicle-pedestrian injury collisions
"population_size", -- Count of total population
"multi_unit_housing", -- Percent of housing units that are multiunit structures
"walk", -- Percent of population ages 16 years and older who walked to work
"children_living_in_households", -- Percent of children living in households that received assistance in the past 12 months
"public_transportation", -- Percent of population ages 16 years and older who used public transit to commute to work
"drug_overdose_deaths_per", -- Drug overdose death rate per 100,000 people
"households_with_children", -- Percent of households with children under 18 years of age
"female", -- Percent of female population
"all_cause_deaths_per_100", -- All-cause death rate per 100,000 people
"unintentional_injury_deaths", -- Unintentional injury death rate per 100,000 people
"speaks_a_language_other_than", -- Percent of population ages 5 and older who speaks a language other than English at home
"some_college_or_associate" -- Percent of adults ages 25 years and older with some college education attainment or an associate's degree
FROM
"sccgov/small-area-profile-supervisory-district-level-2023-n57v-y6s6:latest"."small_area_profile_supervisory_district_level_2023"
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 sccgov/small-area-profile-supervisory-district-level-2023-n57v-y6s6 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 sccgov/small-area-profile-supervisory-district-level-2023-n57v-y6s6: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 sccgov/small-area-profile-supervisory-district-level-2023-n57v-y6s6Checkout 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 sccgov/small-area-profile-supervisory-district-level-2023-n57v-y6s6:latestThis will download all the objects for the latest tag of sccgov/small-area-profile-supervisory-district-level-2023-n57v-y6s6 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 sccgov/small-area-profile-supervisory-district-level-2023-n57v-y6s6: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 sccgov/small-area-profile-supervisory-district-level-2023-n57v-y6s6: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, sccgov/small-area-profile-supervisory-district-level-2023-n57v-y6s6 is just another Postgres schema.