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 largescale_renewable_projects_reported_by_nyserda table in this repository, by referencing it like:
"ny-gov/largescale-renewable-projects-reported-by-nyserda-dprp-55ye:latest"."largescale_renewable_projects_reported_by_nyserda"or in a full query, like:
SELECT
":id", -- Socrata column ID
"type_of_existing", -- Vintage Generation Facility (VGS). VGFs are projects that are either upgraded, returned to service, relocated, or repowered, and must meet the specified requirements
"index_rec_strike_price", -- The Index REC bid price or the Index REC Conversion Offer Price of Tier 1 awarded projects in US dollars. Blank cells represent data that was not required or is not currently available.
"transmission_capacity_hvdc_", -- Capacity of a project's new HVDC transmission line. Applicable to Tier 4 projects only. Blank cells represent data that was not required or is not currently available.
"year_of_commercial_operation", -- Date declared as the Commercial Operation date of facility by NYISO or local utility. Blank cells represent data that was not required or is not currently available.
"bid_quantity_mwh", -- The annual production of the project in Megawatt hours that NYSERDA and the Counterparty agree on constitutes performance in an Agreement. The Bid Quantity may not constitute the entire output of a project. Blank cells represent data that was not required or is not currently available.
"max_annual_contract_quantity", -- The maximum annual contractual obligation of the project in Megawatt hours. Blank cells represent data that was not required or is not currently available.
"p50_generation_calculated_by_nyserda_mwh_yr_", -- A NYSERDA calculation based on a 10.5 meter/second average Hub Height (m/s avgHH) wind speed. Weibull parameters of A = 11.3 and K = 2.2 were used to create an annual wind frequency distribution table. The resulting frequency distribution was then applied in the NREL 15 MW turbine power curve assuming 22% losses. Blank cells represent data there were not required or is not currently available.
"eligibility", -- Either Maintenance, Non-Tier 1, Tier 1, Tier 2, Tier 4, OREC, or N/A. Maintenance represents existing baseline resources. Non-Tier 1 are facilities with a commercial operation date before 1/1/2015, and Tier 1 are facilities with a commercial operation date after 1/1/2015. Tier 2 facilities are from wind and non-state-owned run-of-river hydroelectric generating facilities located within New York State that entered commercial operation prior to January 1, 2015. OREC represents projects in the offshore wind program. Tier 4 represents renewable energy and transmission projects.
"energy_storage_energy_capacity_mwh", -- Usable installed energy storage capacity measured in alternating current (AC) power. It is equal to the total capacity measured during a complete discharge from a 100% usable state of charge, performed in accordance with the storage manufacturer’s specifications, on the commercial operation date. Blank cells represent data that was not required or is not currently available.. This category does not include pumped storage.
"article_10_94c_case_number", -- Name of Article 10 or 94C Case Number. Additional Information is located at http://www3.dps.ny.gov/W/PSCWeb.nsf/All/1392EC6DD904BBC285257F4E005BE810?OpenDocument and https://ores.ny.gov/permit-applications. Blank cells represent data that were not required, applicable or are not currently available
"p10_annual_orec_exceedance", -- An amount of electrical energy (in MWh), such that the estimated probability in any given year that generation from the Selected Project delivered to the Delivery Point would exceed that amount is 10 percent. Applicable to Offshore Wind projects only. Blank cells represent data that was not required or is not currently available.
"project_name", -- Name of project. Project names with an asterisk have the same project name as projects awarded under different solicitations
"solicitation_name", -- Request for Proposal (RFP) number, or Maintenance Agreement number under Renewable Portfolio Standard (RPS) or Tier 2 Maintenance under the Renewable Energy Standard (RES)
"fixed_rec_price", -- The Fixed REC bid price of Tier 1 and Non-Tier 1 awarded projects in US dollars. Blank cells represent data that was not required or is not currently available.
"county_province", -- Name of US county or Canadian Province for project. Blank cells represent data that was not required or is not currently available.
"nyiso_zone", -- Indicates the New York Control Area Load Zone into which the Bid Facility will interconnect or deliver. Blank cells represent data that was not required or is not currently available.
"federal_permitting_mechanism", -- The dates specific to the schedule of the Environmental Review and Permitting of Offshore Wind and Tier 4 Projects.
"energy_storage_power_capacity_mwac", -- Rating of the electric power to be delivered from an energy storage project in alternating current (AC) power. Blank cells represent data that was not required or is not currently available.
"zip_code", -- ZIP code of project. Blank cells represent data that was not required or is not currently available.
"index_orec_strike_price", -- The Index OREC bid price of Off-shore wind awarded projects in US dollars, Blank cells represent data that was not required or is not currently available.
"generation_type", -- Indicates the type of eligible generation, either Existing or New Generation. Blank cells represent data that was not required or is not currently available.
"permit_process", -- The applicable federal, state, or local permitting requirements that the Bid Facility is subject to. Blank cells represent data that was not required or is not currently available.
"georeference", -- Open Data/Socrata-generated geocoding information based on supplied address components.
"counterparty", -- Name of the Seller in NYSERDA’s agreement. A counterparty is the other party to a NYSERDA agreement. Blank cells represent data that was not required or is not currently available.
"article_vii", -- Name of Article VII pertaining to Offshore Wind Project or Tier 4 Project.
"renewable_technology", -- The type of renewable energy technology for the project currently under contract or installed; either Land Based Wind, Offshore Wind, Hydroelectric, Biomass, Biogas-LFG, Biogas-ADG, Fuel Cell, Solar, Geothermal, Maintenance Biomass, or Maintenance Hydroelectric.
"developer_name", -- Name of Developer responsible for developing the project. Blank cells represent data that was not required or is not currently available.
"project_met_economic_benefits_threshold", -- For Non-Tier 1 or Tier I Projects, indicates the verification status of the Incremental Economic Benefits included in the projects Agreement. Incremental Economic Benefits are verified following three years of operation and must meet a threshold of 85% of claim accepted by NYSERDA. Yes - indicates verification complete and project met threshold. No – verification complete and project did not meet threshold. Blank cells represent – verification not due yet/ not yet complete; no economic benefit claim; or project cancelled/terminated. For more information, please refer to each specific RFP’s Section on Incremental Economic Benefits to New York State.
"incremental_economic_benefits_claimed", -- Indicates if a Non-Tier 1 or Tier 1 Project claimed Incremental Economic Benefits as part of its Bid Proposal, and these claims were accepted by NYSERDA as part of its award. Blank cells represent data that was not required or is not currently available. For more information, please refer to each specific RFP’s Section on Incremental Economic Benefits to New York State.
"new_renewable_capacity_mw", -- For a new facility, the New Renewable Capacity equals the nameplate capacity of the installed equipment. For a repowered facility, End of Useful Life Bid Capacity is reported. For an upgraded facility, only the renewable capacity resulting from the upgrade of a facility is provided. For a single facility that was awarded multiple contracts, each for a percentage of the facility’s output under multiple RFPs, the total New Renewable Capacity for the facility is listed once to avoid over-counting. Therefore, the same facility appearing in multiple instances may show a New Renewable Capacity of zero. Blank cells represent projects with expired contracts with NYSERDA.
"redc", -- Regional Economic Development Council of project. In 2011, 10 Regional Councils were established and charged with developing long-term strategic plans for economic growth in their regions. Blank cells indicate the project is not located in New York State.
"ptid", -- Indicates the NYISO Generator Point Identifier. A Point Identifier is a resource-specific numerical identifier used by the NYISO’s software systems to identify Generators and other Suppliers and only applies to Operating Projects. Blank cells represent data that was not required or is not currently available.
"contract_duration", -- Number of years of performance under the NYSERDA Agreement.
"interconnection_queue_number", -- This is the list for the NYISO Interconnection Queue and indicates the number on the list of transmission and generation projects seeking to join the grid. Additional information is located at: https://www.nyiso.com/interconnections. Tier 4 projects include both Resources and Transmission numbers.
"second_article_10_or_94c_case_number", -- Name of second Article 10 or 94C Case Number, if applicable. Additional Information is located at http://www3.dps.ny.gov/W/PSCWeb.nsf/All/1392EC6DD904BBC285257F4E005BE810?OpenDocument and https://ores.ny.gov/permit-applications. Blank cells represent data that was not required or is not currently available.
"project_status", -- The phase/status of which the project is in as of the Data Through Date; either Cancelled, Completed, Under Development, or Operational. A status of Cancelled means that NYSERDA's award or contract with the counterparty was cancelled or terminated. A status of Completed means the project has fulfilled their contractual obligation to NYSERDA. A status of Under Development means that the project has not yet entered operation. A status of Operational means that the project has entered operation.
"year_of_delivery_start_date", -- Date NYSERDA’s payments started or are expected to start. For projects Under Development, the dates are reflective of the current status of NYSERDA agreements and are subject to change based on project development progress. Blank cells represent data that was not required or is not currently available.
"data_through_date", -- The date the dataset was refreshed
"bid_capacity_mw", -- The NYSERDA-contracted portion of the New Renewable Capacity in Megawatts. Blank cells represent data that were not applicable, required or are not currently available.
"state_province" -- Name of US state or Canadian Province for project. Blank cells represent data that was not required or is not currently available.
FROM
"ny-gov/largescale-renewable-projects-reported-by-nyserda-dprp-55ye:latest"."largescale_renewable_projects_reported_by_nyserda"
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/largescale-renewable-projects-reported-by-nyserda-dprp-55ye 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 ny-gov/largescale-renewable-projects-reported-by-nyserda-dprp-55ye: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/largescale-renewable-projects-reported-by-nyserda-dprp-55yeCheckout 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/largescale-renewable-projects-reported-by-nyserda-dprp-55ye:latestThis will download all the objects for the latest tag of ny-gov/largescale-renewable-projects-reported-by-nyserda-dprp-55ye 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/largescale-renewable-projects-reported-by-nyserda-dprp-55ye: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/largescale-renewable-projects-reported-by-nyserda-dprp-55ye: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/largescale-renewable-projects-reported-by-nyserda-dprp-55ye is just another Postgres schema.