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 energy_code_annual_report_submissions_beginning table in this repository, by referencing it like:
"ny-gov/energy-code-annual-report-submissions-beginning-vfqs-vuqv:latest"."energy_code_annual_report_submissions_beginning"or in a full query, like:
SELECT
":id", -- Socrata column ID
"e_sup_lte3_envelope_conf", -- 1 if, in the case of residential buildings, the municipality require that building envelope values be confirmed as code compliant by at least one of the following methods? Prescriptive, Trade-off method (RESCheck), Energy Rating Index (2015 IECC Section R406). 0 otherwise. Not applicable before 2016.
"e_sup_existing_commercial_1", -- Completed existing commercial building renovation projects that are compliant. Not applicable before 2016.
"e_sup_new_residential", -- New residential building construction projects completed. Not applicable before 2016.
"e_sup_new_commercial_compliant", -- Completed new commercial building construction projects that are compliant. Not applicable before 2016.
"e_sup_gt3_iecc_c408", -- 1 if, in the case of commercial buildings, the municipality require construction documents to clearly indicate provisions for commissioning and completion requirements in accordance with Section C408 of the 2015 IECC. 0 otherwise. Not applicable before 2016.
"e_sup_lte3_blower_door", -- 1 if, in case of detached one-and-two family dwellings, townhouses, and multiple family dwellings of three stories or less, the municipality require that an air leakage test (blower door test) be performed on the completed building envelope. 0 otherwise. Not applicable before 2016.
"e_sup_insp_final", -- 1 if a final inspection is completed that verifies, in the case of a commercial building, the installation and proper operation of all building controls required by the Energy Code, the receipt of documentation verifying that activities associated with building commissioning required by the Energy Code have been conducted and that findings of noncompliance corrected, and the receipt of the HVAC system certification required by the Energy Code; and the final inspection shall verify, in the case of a residential building, the installation of all building systems, equipment, and controls required by the Energy Code and their proper operation and the installation of the number of high-efficacy lamps and fixtures required by the Energy Code. 0 otherwise. Not applicable before 2016.
"e_sup_docs_duct_sealing_and", -- 1 if municipality require that construction documents submitted as part of an application for a building permit show duct sealing, duct and pipe insulation and location. 0 otherwise. Not applicable before 2016.
"e_sup_docs_commercial", -- 1 if municipality require that construction documents submitted as part of an application for a building permit show mechanical system design criteria (Manual "J" and Manual "S" for residential buildings). 0 otherwise. Not applicable before 2016.
"compliance_new_commercial", -- Average number of construction inspections are conducted for Energy Code compliance for each new commercial building. Before 2016, options were 0 to 4 or more. Not applicable before 2008.
"complianceenergyenforcem_3", -- 1 if “compliance checklists” are among procedures used by the local government to ensure compliance with the Energy Code. 0 otherwise. Not applicable before 2008.
"complianceenergyenforcem", -- 1 if “field inspections” are among procedures used by the local government to ensure compliance with the Energy Code. 0 otherwise. Not applicable before 2008.
"authorizing_legislation", -- Local law(s), ordinance(s) or other legislation that provides for the administration and enforcement of the Uniform Code and/or the Energy Code in the local government. Blank if responsibility transferred to another local government.
"contractor_meets_educational", -- 1 if the third party meets the education requirements of 1203.2(e)(1). 0 if no. -1 if unknown. Blank if not answered (i.e. municipality does not use contracted services)
"municode", -- OSC issued unique identifier for municipality
"complaints_estimate", -- 1 if any complaints numbers reported are estimates. 0 otherwise. Not applicable prior to 2016.
"complaints_energy_action", -- Energy Code complaints acted upon
"complaints_energy_received", -- Energy Code complaints received
"e_sup_compliance_methods", -- With respect to the response to previous question, provide a description of the methods used to verify such compliance with the Energy Code.
"e_sup_existing_residential", -- Existing residential building renovation projects completed. Not applicable before 2016.
"e_sup_new_commercial_completed", -- New commercial building construction projects completed. Not applicable before 2016.
"e_sup_gt3_compliance_method", -- 1 if, in the case of commercial buildings, the municipality require the demonstration of compliance with one of the following compliance paths: * *ASHRAE Compliance Path*: Compliance with the requirements of ASHRAE 90.1-2013, as amended by the 2016 Energy Code Supplement.* *Prescriptive Compliance Path*: Compliance with the requirements of (1) Sections C402 through C405 of the 2015 IECC, as amended by the 2016 Energy Code Supplement, (2) Section C406 of the 2015 IECC, as amended by the 2016 Energy Code Supplement, and (3) in the case of tenant spaces, Section C406.1.1 of the 2015 IECC, as amended by the 2016 Energy Code Supplement. * *Performance Compliance Path*: Compliance with the requirements of Sections C402.5, C403.2, C404, C405.2, C405.3, C405.5, C405.6, and C407 of the 2015 IECC, as amended by the 2016 Energy Code Supplement, with building energy cost to be equal to or less than 85 percent of the standard reference design building. 0 otherwise. Not applicable before 2016.
"e_sup_lte3_external_duct_1", -- 1 if, in the case of residential buildings, the municipality require the code official receive a written report of the results of the test signed by the party conducting the test. 0 otherwise. Not applicable before 2016.
"e_sup_lte3_blower_door_report", -- 1 if, in the case of residential buildings, the municipality require that a written report of the results of the air leakage test be signed by the party conducting the test and provided to the municipality. 0 otherwise. Not applicable before 2016.
"e_sup_lte3_permanent", -- 1 if, in the case of residential buildings, the municipality require that a permanent certificate listing the predominant R-values of insulation installed be completed by the builder or registered design professional and posted on a wall in the space where the furnace is located. 0 otherwise. Not applicable before 2016.
"e_sup_insp_electrical", -- 1 if inspections are completed at electrical rough-in to verify compliance as required by the Energy Code and approved plans and specifications as to installed lighting systems, components and controls and, if applicable, installation of an electric meter for each dwelling unit. 0 otherwise. Not applicable before 2016.
"e_sup_insp_plumbing", -- 1 if inspections are completed at plumbing rough-in to verify compliance as required by the Energy Code and approved plans and specifications as to types of insulation and corresponding R-values and protection, required controls, and required heat traps on potable water heaters. 0 otherwise. Not applicable before 2016.
"e_sup_submit_certification", -- 1 if construction documents are required to be prepared by a New York State registered architect or licensed professional engineer, and the municipality require that the documents bear the seal or stamp and signature of such registered architect or licensed professional engineer and include, immediately above the signature, a statement substantially similar to the following: “To the best of the knowledge, belief, and professional judgment of the undersigned [registered architect or licensed professional engineer], the plans and specifications depicted on these drawings are in compliance with the applicable provisions of the New York State Uniform Fire Prevention and Building Code and the New York State Energy Conservation Construction Code, as currently in effect.”. 0 otherwise. Not applicable before 2016.
"e_sup_docs_fenestration_shgc", -- 1 if municipality require that construction documents submitted as part of an application for a building permit show fenestration U-factor and solar heat gain coefficient (SHGC). 0 otherwise. Not applicable before 2016.
"e_sup_docs_thermal_envelope", -- 1 if municipality require that construction documents submitted as part of an application for a building permit show representation of building's thermal envelope. 0 otherwise. Not applicable before 2016.
"complianceenergyenforcem_4", -- 1 if any other procedures are used by the local government to ensure compliance with the Energy Code. 0 otherwise. Not applicable before 2008.
"complianceenergyenforcem_2", -- 1 if “software reports or printouts” are among procedures used by the local government to ensure compliance with the Energy Code. 0 otherwise. Not applicable before 2008.
"compliance_more_restrictive_2", -- Year and number of the local law(s) or ordinance(s) that impose different standards from the Energy Code. Not applicable before 2018.
"compliance_more_restrictive_1", -- 1 if each such local law or ordinance is filed with State Fire Prevention and Building Code Council pursuant to Energy Law section 11-109. 0 if no. -1 if unknown.
"period_start_eff", -- When reporting period starts. Generally January 1 of the reporting year. Not applicable before 2018.
"energy_code_municode", -- Municode identifier for municipality that enforces energy code. Blank if completed by submitter.
"munitype", -- Type of municipality (City, County, Town, Village)
"muniname", -- Name of municipality
"countyname", -- Name of county which municipality belongs
"reportyear", -- Reporting year for municipality's annual submission
"e_sup_existing_residential_1", -- Completed existing residential building renovation projects that are compliant. Not applicable before 2016.
"e_sup_review_certification", -- 1 if the municipality determines the construction documents to be in compliance with the applicable requirements of the Energy Code and accept the documents, and the municipality stamp the construction documents “Reviewed for Energy Code Compliance” and endorse the documents in writing. 0 otherwise. Not applicable before 2016
"contracted_services_used", -- 1 if third-party contracted services used to meet the requirements of 19 NYCRR Part 1203. 0 otherwise. Not applicable before 2008.
"swiskey", -- Unique identifier for municipality
"e_sup_insp_mechanical", -- 1 if inspections are completed at mechanical rough-in to verify compliance as required by the Energy Code and approved plans and specifications as to installed HVAC equipment type and size, required controls, system insulation, and corresponding R-value; system and damper air leakage (in the case of a commercial building) or system air leakage control (in the case of a residential building); and required energy recovery and economizers (in the case of a commercial building) or programmable thermostats, dampers, whole-house ventilation, and minimum fan efficiency (in the case of a residential building). 0 otherwise. Not applicable before 2016.
"e_sup_insp_framing_insulation", -- 1 if inspections are completed at framing and rough-in, made before application of interior finish and to verify compliance with the Energy Code as to types of insulation and corresponding R-values and their correct location and proper installation, fenestration properties (U-factor and SHGC and, in the case of a commercial building, VT), and proper installation and air leakage components as required by the Energy Code and approved plans and specifications. 0 otherwise. Not applicable before 2016.
"e_sup_insp_footing_insulation", -- 1 if inspections are completed of footings and foundations to verify compliance with the Energy Code as to R-value, location, thickness, depth of burial and protection of insulation as required by the Energy Code and approved plans and specifications. 0 otherwise. Not applicable before 2016.
"e_sup_docs_commercial_daylight", -- 1 if municipality require that construction documents submitted as part of an application for a building permit show location of daylight zones on floor plans (in the case of a commercial building). 0 otherwise. Not applicable before 2016.
"e_sup_docs_commercial_fan", -- 1 if municipality require that construction documents submitted as part of an application for a building permit show fan motor horsepower (hp) and controls (in the case of a commercial building). 0 otherwise. Not applicable before 2016.
"compliance_new_residential", -- Average number of construction inspections are conducted for Energy Code compliance for each new residential building. Before 2016, options were 0 to 4 or more. Not applicable before 2008.
"period_end_eff", -- When reporting period ends. Generally December 31 of the reporting year. Not applicable before 2018.
"e_sup_existing_commercial", -- Existing commercial building renovation projects completed. Not applicable before 2016.
"e_sup_docs_commercial_2", -- 1 if municipality require that construction documents submitted as part of an application for a building permit show equipment and system controls (in the case of a commercial building). 0 otherwise. Not applicable before 2016.
"e_sup_docs_water_heating", -- 1 if municipality require that construction documents submitted as part of an application for a building permit show mechanical and service water heating system and equipment types, sizes, and efficiencies. 0 otherwise. Not applicable before 2016.
"opt_out_per_exc_381_2", -- 1 if local government adopted a local law providing it will **not** enforce the Uniform Code and Energy Code that was in effect during the reporting period. 0 otherwise. Not applicable before 2018.
"e_sup_new_residential_1", -- Completed new residential building construction projects that are compliant. Not applicable before 2016.
"e_sup_lte3_blower_door_3", -- 1 if, in the case of residential buildings, the municipality require verification that the air leakage rate is 3 ACH (50 pascals) or less. 0 otherwise. Not applicable before 2016.
"compliance_more_restrictive", -- 1 if local government has in effect any local law or ordinance that imposes energy conservation standards that are more stringent, less stringent, or otherwise different from the requirements imposed by the Energy Code. 0 otherwise. Not applicable before 2008.
"shared_per_gml_art_5g", -- 1 if local government entered into an agreement pursuant to General Municipal Law, Article 5-G, with one or more other local governments to jointly administer the Uniform or Energy Code. 0 otherwise. Not applicable before 2018.
"e_sup_docs_insulation_r_values", -- 1 if municipality require that construction documents submitted as part of an application for a building permit show insulation materials and their R-values. 0 otherwise. Not applicable before 2016.
"e_sup_docs_air_sealing", -- 1 if municipality require that construction documents submitted as part of an application for a building permit show air sealing details. 0 otherwise. Not applicable before 2016.
"e_sup_docs_area_shgc", -- 1 if municipality require that construction documents submitted as part of an application for a building permit show area-weighted U-factor calculations and SHGC values (where applicable). 0 otherwise. Not applicable before 2016.
"inspections_estimate", -- 1 if any of the reported numbers of inspections are estimated. 0 otherwise. Not applicable prior to 2016.
"e_sup_docs_commercial_1", -- 1 if municipality require that construction documents submitted as part of an application for a building permit show economizer description (in the case of a commercial building). 0 otherwise. Not applicable before 2016.
"e_sup_lte3_external_duct", -- 1 if, in a case where any part of the HVAC duct system located outside of the residential building envelope, the municipality require that a duct tightness test be performed. 0 otherwise. Not applicable before 2016.
"e_sup_docs_commercial_lighting", -- 1 if municipality require that construction documents submitted as part of an application for a building permit show lighting fixture schedule with wattage and control narrative (in the case of a commercial building). 0 otherwise. Not applicable before 2016.
"complianceenergyenforcem_1", -- 1 if “plan reviews” are among procedures used by the local government to ensure compliance with the Energy Code. 0 otherwise. Not applicable before 2008.
"includes_personnel" -- 1 if submitter is employee of the local government. 0 otherwise. Not applicable before 2018 or if municipality has not transferred enforcement to another local government's responsibility.
FROM
"ny-gov/energy-code-annual-report-submissions-beginning-vfqs-vuqv:latest"."energy_code_annual_report_submissions_beginning"
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 ny-gov/energy-code-annual-report-submissions-beginning-vfqs-vuqv with SQL in under 60 seconds.
Query Your Local Engine
Install Splitgraph Locally
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 ny-gov/energy-code-annual-report-submissions-beginning-vfqs-vuqv: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 ny-gov/energy-code-annual-report-submissions-beginning-vfqs-vuqvCheckout 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 ny-gov/energy-code-annual-report-submissions-beginning-vfqs-vuqv:latestThis will download all the objects for the latest tag of ny-gov/energy-code-annual-report-submissions-beginning-vfqs-vuqv 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 ny-gov/energy-code-annual-report-submissions-beginning-vfqs-vuqv: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 ny-gov/energy-code-annual-report-submissions-beginning-vfqs-vuqv: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, ny-gov/energy-code-annual-report-submissions-beginning-vfqs-vuqv is just another Postgres schema.