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 comprehensive_affordable_housing_directory table in this repository, by referencing it like:
"datahub-austintexas-gov/comprehensive-affordable-housing-directory-4syj-z4ky:latest"."comprehensive_affordable_housing_directory"or in a full query, like:
SELECT
":id", -- Socrata column ID
"num_units_mfi_100", -- Number of units at the project which are income-restricted at or below 100% of the median family income. Not cumulative with lower thresholds.
"num_units_mfi_30", -- Number of units at the project which are income-restricted at or below 30% of the median family income. Not cumulative with lower thresholds.
"cmty_served_descriptions", -- Description of the communities served by the property.
"num_units_mfi_70", -- Number of units at the project which are income-restricted at or below 70% of the median family income. Not cumulative with lower thresholds.
"home_program_funding", -- Amount of funds received through the United States Department of Housing and Urban Development HOME Investment Partnerships Program
"num_units_mfi_65", -- Number of units at the project which are income-restricted at or below 65% of the median family income. Not cumulative with lower thresholds.
"bus_transport_dist", -- Distance to nearest bus stop.
"num_ir_2_bed_units", -- The number of income-restricted two bedroom units in the property.
"data__criminal_history_criteria", -- Types of criminal backgrounds accepted by property.
"elementary_school", -- Assigned elementary school within Austin ISD’s attendance areas (geographic boundaries)
"latitude", -- Property Latitude
":@computed_region_q9nd_rr82",
"num_units_mfi_90", -- Number of units at the project which are income-restricted at or below 90% of the median family income. Not cumulative with lower thresholds.
"city", -- City
"num_ir_5_bed_units", -- The number of income-restricted five bedroom units in the property.
"property_name", -- Property Name
"comp_affordable_housing_id", -- Unique ID and primary key for the property
":@computed_region_8spj_utxs",
"security", -- Description of the property's security.
"washer_dryer_other", -- Further description of washer/dryer amenities at the property.
"num_ir_3_bed_units", -- The number of income-restricted three bedroom units in the property.
"housing_program", -- Housing program the property falls under.
"state", -- State
"low_income_housing_tax_credit", -- Indicates participation in the Texas Department of Housing and Community Affairs' Low-Income Housing Tax Credit award. [4:4%, 9:9%]
"total_income_restricted_units", -- The total number of income-restricted units in the property.
"has_4_bed_unit", -- The property has four-bedroom units. [1:True, 0:False]
"has_playground", -- The property has a playground. [1:True, 0:False]
"washer_dryer_hookup", -- The unit includes washer and dryer hookups. [1:True, 0:False]
"affordability_expiration", -- Date when the property's affordability period expires.
"source_ahi", -- Is this data sourced from the City of Austin's Affordable Housing Inventory? [1:True, 0:False]
"tdhca_funded", -- Is this property funded by the Texas Department of Housing and Community Affairs? [1:True, 0:False]
"num_units_mfi_120", -- Number of units at the project which are income-restricted at or below 120% of the median family income. Not cumulative with lower thresholds.
"washer_dryer_onsite", -- The property has an onsite laundry facility. [1:True, 0:False]
"community_mental", -- Property dedicated for tenants with mental health needs. [1:True, 0:False]
"allows_pet", -- The property allow pets. [1:True, 0:False]
"hatc_funded", -- Is this property funded by the Housing Authority of Travis County? [1:True, 0:False]
"pet_other", -- Description of pet policy.
"num_units_mfi_60", -- Number of units at the project which are income-restricted at or below 60% of the median family income. Not cumulative with lower thresholds.
"washer_dryer_unit", -- The unit includes a washer and dryer. [1:True, 0:False]
"website", -- Website
"num_ir_0_bed_units", -- The number of income-restricted studio units in the property.
"num_ir_1_bed_units", -- The number of income-restricted one bedroom units in the property.
"middle_school", -- Assigned middle school within Austin ISD’s attendance areas (geographic boundaries)
"has_off_street_parking", -- The property has off street parking. [1:True, 0:False]
"is_duplicate", -- Property is a duplicate. [1:True, 0:False]
"community_elderly", -- Property dedicated for the elderly. [1:True, 0:False]
"broken_lease", -- Does the property approve tenants with a broken lease history? [Yes, No, Depends]
"num_units_mfi_40", -- Number of units at the project which are income-restricted at or below 40% of the median family income. Not cumulative with lower thresholds.
"has_1_bed_unit", -- The property has one-bedroom units. [1:True, 0:False]
"has_3_bed_unit", -- The property has three-bedroom units. [1:True, 0:False]
"has_pool", -- The property has a pool. [1:True, 0:False]
"has_air_conditioning", -- The property has air conditioning. [1:True, 0:False]
"has_ceiling_fans", -- The property has ceiling fans. [1:True, 0:False]
"community_development_block_gr", -- Amount of funds received through the United States Department of Housing and Urban Development Community Development Block Grant (CDBG)
"nhcd_funded", -- Is this property funded by Neighborhood Housing and Community Development at the City of Austin? [1:True, 0:False]
"geocoded_column", -- Location specific mapping idenifier
"num_units_mfi_110", -- Number of units at the project which are income-restricted at or below 110% of the median family income. Not cumulative with lower thresholds.
"has_5_bed_unit", -- The property has five-bedroom units. [1:True, 0:False]
"community_veteran", -- Property dedicated for veterans. [1:True, 0:False]
"has_permanent_support_units", -- The property has units allocated for permanent supportive housing [1:True, 0:False]
"students_only", -- Property dedicated for student renters only. [1:True, 0:False]
"total_permanent_support_units", -- Total number of permanent supportive housing units in the property.
"broken_lease_criteria", -- Description of the property's policy regarding tenants with past broken leases.
"num_units_mfi_80", -- Number of units at the project which are income-restricted at or below 80% of the median family income. Not cumulative with lower thresholds.
"longitude", -- Property Longitude
"zip_code", -- Zip Code
"total_accessible_ir_units", -- The total number of accessible income-restricted units.
"property_manager_or_landlord", -- Property Manager or Landlord
"eviction_history", -- Does the property approve tenants with an eviction history? [Yes, No, Depends]
"address", -- Address
"has_allocated_acc_ir_units", -- The property has accessible income-restricted units allocated. [1:True, 0:False]
"eviction_history_criteria", -- Description of the property's eviction history policy.
"community_disabled", -- Property dedicated for the disabled. [1:True, 0:False]
"community_military", -- Property dedicated for those currently in the military. [1:True, 0:False]
"council_district", -- City Council District
"unit_type", -- Unit type: [ADU, Apartment, Duplex, Fourplex, Individual and Multifamily, Multifamily, Senior Housing, Single Family]
"total_housing_choice_units", -- The total number of Housing Choice Voucher (i.e., Section 8) units in the property.
"austin_housing_inventory_id", -- Property ID corresponds to ID in the Affordable Housing Inventory.
"affordability_start", -- Date when the property's affordability period begins.
"has_allocated_ir_units", -- The property has income-restricted units allocated [1:True, 0:False]
"criminal_history", -- Does the property accept tenants with criminal histories? [Yes, No, Depends]
"phone", -- Phone
"high_school", -- Assigned high school within Austin ISD’s attendance areas (geographic boundaries)
"has_0_bed_unit", -- The property has studio units. [1:True, 0:False]
"only_serves_designated_cmtys", -- Property only serves specific communities. 1=yes. 0=no.
"accepts_housing_choice", -- The property accepts Housing Choice Vouchers (i.e., Section 8) [1:True, 0:False]
"source_atc_guide", -- Is the data sourced from the Austin Tenant's Council Guide to Affordable Housing? [1=True, 0=False].
"haca_funded", -- Is this property funded by the Housing Authority City of Austin? [1:True, 0:False]
"has_2_bed_unit", -- The property has two-bedroom units. [1:True, 0:False]
"census_tract", -- Geographic census region
"total_units", -- Total number of units in the property.
"has_waitlist", -- The property has a waitlist [1:True, 0:False]
"email", -- Email
"cmty_domestic_abuse_survivor", -- Property dedicated for survivors of domestic violence. [1:True, 0:False]
":@computed_region_jcrc_4uuy",
"data__data_source_tdhca", -- Is the data sourced from the Texas Department of Housing and Community Affairs database? [1:True, 0:False]
"num_units_mfi_50", -- Number of units at the project which are income-restricted at or below 50% of the median family income. Not cumulative with lower thresholds.
":@computed_region_rxpj_nzrk",
":@computed_region_m2th_e4b7",
"developer", -- Property Developer
"owner", -- Property Owner
"num_ir_4_bed_units", -- The number of income-restricted four bedroom units in the property.
":@computed_region_e9j2_6w3z"
FROM
"datahub-austintexas-gov/comprehensive-affordable-housing-directory-4syj-z4ky:latest"."comprehensive_affordable_housing_directory"
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 datahub-austintexas-gov/comprehensive-affordable-housing-directory-4syj-z4ky 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 datahub-austintexas-gov/comprehensive-affordable-housing-directory-4syj-z4ky: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 datahub-austintexas-gov/comprehensive-affordable-housing-directory-4syj-z4kyCheckout 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 datahub-austintexas-gov/comprehensive-affordable-housing-directory-4syj-z4ky:latestThis will download all the objects for the latest tag of datahub-austintexas-gov/comprehensive-affordable-housing-directory-4syj-z4ky 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 datahub-austintexas-gov/comprehensive-affordable-housing-directory-4syj-z4ky: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 datahub-austintexas-gov/comprehensive-affordable-housing-directory-4syj-z4ky: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, datahub-austintexas-gov/comprehensive-affordable-housing-directory-4syj-z4ky is just another Postgres schema.