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 cambridge_building_energy_use_disclosure_ordinance table in this repository, by referencing it like:
"cambridgema-gov/cambridge-building-energy-use-disclosure-ordinance-72g6-j7aq:latest"."cambridge_building_energy_use_disclosure_ordinance"or in a full query, like:
SELECT
":id", -- Socrata column ID
"property_gfa_self_reported_ft2", -- The building’s gross floor area, user submitted.
"longitude", -- The longitude of the point assigned for the location of this report. If a report represents a single building, this is the center of the building footprint. If a report represents multiple buildings, this is the center of the parcel.
"latitude", -- The latitude of the point assigned for the location of this report. If a report represents a single building, this is the center of the building footprint. If a report represents multiple buildings, this is the center of the parcel.
"indoor_water_intensity_all_water_sources_gal_ft2", -- Total indoor water use per square foot, in gal/ft2, generated by ENERGY STAR Portfolio Manager.
"water_use_all_water_sources_kgal", -- Total water use, user submitted.
"total_ghg_emissions_intensity_kgco2e_ft2", -- Total greenhouse gas emissions intensity, emissions per square foot, in kgCO2e/ft2, generated by ENERGY STAR Portfolio Manager.
"total_ghg_emissions_metric_tons_co2e", -- Total greenhouse gas emissions, in metric tons CO2e, generated by ENERGY STAR Portfolio Manager.
"source_eui_kbtu_ft2", -- Source energy use intensity (EUI), the amount of source energy used per square foot, generated by ENERGY STAR Portfolio Manager.
"source_energy_use_kbtu", -- The total amount of source energy used, in kBtu, user submitted.
"weather_normalized_site_eui_kbtu_ft2", -- The weather normalized site energy use intensity (EUI), the amount of weather normalized site energy used per square foot, generated by ENERGY STAR Portfolio Manager.
"weather_normalized_site_energy_use_kbtu", -- The total weather normalized amount of site energy used, in kBtu, generated by ENERGY STAR Portfolio Manager.
"site_energy_use_kbtu", -- The total amount of site energy used, in kBtu, user submitted.
"electricity_use_generated_from_onsite_renewable_systems_kwh", -- Amount of electricity used that was generated from onsite renewable systems, in kWh, user submitted.
"district_steam_use_kbtu", -- Amount of district steam used in calendar year, in kBtu, user submitted.
"diesel_2_use_kbtu", -- Amount of diesel #2 used in calendar year, in kBtu, user submitted.
"natural_gas_use_kbtu", -- Amount of natural gas used in calendar year, in kBtu, user submitted.
"natural_gas_use_therms", -- Amount of natural gas used in calendar year, in therms, user submitted.
"electricity_use_grid_purchase_kwh", -- Amount of electricity used in calendar year, in kWh, user submitted.
"energy_star_score", -- 1-100 energy rating developed by the U.S. EPA, generated by ENERGY STAR Portfolio Manager.
"owner_line_2", -- Property owner information continued.
"owner", -- The property owner.
"reported_residential_units", -- The number of residential units in buildings covered by this report, as entered in ENERGY STAR Portfolio manager.
"all_property_uses", -- The list of all property uses as reported in ENERGY STAR Portfolio manager. May include details about the total area of each use.
"primary_property_type_self_selected", -- Primary property type for this report, user submitted.
"buildings_included_count", -- The count of building IDs in Buildings Included field.
"buildings_included", -- The list of building IDs included in this report. Building IDs come from the Cambridge GIS building footprints layer. The IDs included in a given data year represent the building IDs as they existed at that time. Building IDs may have changed over time, even though they refer to the same building. You can ensure comparability across years by comparing properties with the same Reporting ID, even if the list of buildings included may change for different data years.
"address", -- The street address assigned to this report. Reports may cover multiple buildings with multiple addresses.
"pd_parcel_living_area", -- The total living area of all buildings on the parcel from the Property Database. The report itself may not include all buildings on the parcel or may include buildings on other parcels as well.
"ml", -- The map-lot ID locator for the property. Some reports include buildings on multiple map/lots.
"location",
"weather_normalized_source_eui_kbtu_ft2", -- The weather normalized source energy use intensity (EUI), the amount of weather normalized site energy used per square foot, generated by ENERGY STAR Portfolio Manager.
"weather_normalized_source_energy_use_kbtu", -- The total weather normalized amount of source energy used, in kBtu, generated by ENERGY STAR Portfolio Manager.
"site_eui_kbtu_ft2", -- Site energy use intensity (EUI), the amount of site energy used per square foot, generated by ENERGY STAR Portfolio Manager.
"district_chilled_water_use_kbtu", -- Amount of district chilled water used in calendar year, in kBtu, user submitted.
"fuel_oil_1_use_kbtu", -- Amount of fuel oil #1 used in calendar year, in kBtu, user submitted.
"pd_parcel_units", -- The total number of residential units in all buildings on the parcel (MapLot) from the Property Database. The report itself may not include all buildings on the parcel or may include buildings on other parcels as well.
"annual_report_received", -- Describes whether the City received an energy use report for the building. "Yes" values indicate reports that include values for grid-purchased electricity or natural gas use. "No" values indicate that no report was received, or the report did not include electricity or natural gas use.
"reporting_id", -- The ID assigned to this report. Building IDs (Buildings Included) and parcel IDs (MapLot) can change over time even when there are no changes to the actual buildings. If there are significant changes for a subject property, such as buildings being demolished, new buildings constructed, or changes in parcel boundaries that change the buildings included on the parcel, a property will be assigned a new Reporting ID. When comparing data across multiple data years, you should only make direct comparisons between the same reporting IDs.
":@computed_region_rcj3_ccgu", -- This column was automatically created in order to record in what polygon from the dataset 'cambridge_zipcodes' (rcj3-ccgu) the point in column 'location' is located. This enables the creation of region maps (choropleths) in the visualization canvas and data lens.
":@computed_region_e4yd_rwk4", -- This column was automatically created in order to record in what polygon from the dataset 'Census Blocks 2010' (e4yd-rwk4) the point in column 'location' is located. This enables the creation of region maps (choropleths) in the visualization canvas and data lens.
":@computed_region_swkg_bavi", -- This column was automatically created in order to record in what polygon from the dataset 'cambridge_cdd_zoning' (swkg-bavi) the point in column 'location' is located. This enables the creation of region maps (choropleths) in the visualization canvas and data lens.
":@computed_region_rffn_qbt6", -- This column was automatically created in order to record in what polygon from the dataset 'cambridge_neighborhoods' (rffn-qbt6) the point in column 'location' is located. This enables the creation of region maps (choropleths) in the visualization canvas and data lens.
":@computed_region_v7jj_366k", -- This column was automatically created in order to record in what polygon from the dataset 'Police Response Districts' (v7jj-366k) the point in column 'location' is located. This enables the creation of region maps (choropleths) in the visualization canvas and data lens.
":@computed_region_guic_hr4a", -- This column was automatically created in order to record in what polygon from the dataset 'Police Neighborhood Regions' (guic-hr4a) the point in column 'location' is located. This enables the creation of region maps (choropleths) in the visualization canvas and data lens.
"beudo_category", -- Properties may be subject to the BEUDO ordinance under three categories: Residential (50+ units), Non-Residential (25,000+ SF), or Municipal (municipal properties 10,000+ SF).
"data_year", -- The year in which the energy and water in the report was used. Properties subject to BEUDO must report their data for the prior year (the Data Year) by May of the following year.
"fuel_oil_4_use_kbtu", -- Amount of fuel oil #4 used in calendar year, in kBtu, user submitted.
"kerosene_use_kbtu", -- Amount of kerosene used in calendar year, in kBtu, user submitted.
"fuel_oil_5_6_use_kbtu", -- Amount of fuel oil #5 & 6 used in calendar year, in kBtu, user submitted.
"fuel_oil_2_use_kbtu", -- Amount of fuel oil #2 used in calendar year, in kBtu, user submitted.
"electricity_use_grid_purchase_kbtu", -- Amount of electricity used in calendar year, in kBtu, user submitted.
"year_built" -- The value entered in ENERGY STAR Portfolio Manager for the construction year. Reports may include multiple buildings with different construction years.
FROM
"cambridgema-gov/cambridge-building-energy-use-disclosure-ordinance-72g6-j7aq:latest"."cambridge_building_energy_use_disclosure_ordinance"
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 cambridgema-gov/cambridge-building-energy-use-disclosure-ordinance-72g6-j7aq 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 cambridgema-gov/cambridge-building-energy-use-disclosure-ordinance-72g6-j7aq: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 cambridgema-gov/cambridge-building-energy-use-disclosure-ordinance-72g6-j7aqCheckout 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 cambridgema-gov/cambridge-building-energy-use-disclosure-ordinance-72g6-j7aq:latestThis will download all the objects for the latest tag of cambridgema-gov/cambridge-building-energy-use-disclosure-ordinance-72g6-j7aq 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 cambridgema-gov/cambridge-building-energy-use-disclosure-ordinance-72g6-j7aq: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 cambridgema-gov/cambridge-building-energy-use-disclosure-ordinance-72g6-j7aq: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, cambridgema-gov/cambridge-building-energy-use-disclosure-ordinance-72g6-j7aq is just another Postgres schema.