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 industrial_development_agencies_project_data table in this repository, by referencing it like:
"ny-gov/industrial-development-agencies-project-data-9rtk-3fkw:latest"."industrial_development_agencies_project_data"or in a full query, like:
SELECT
":id", -- Socrata column ID
"state_sales_tax_exemption_amount", -- Amount of state sales tax exemptions provided to the project.
"amt_pilot_school_due", -- Amount of Payments in Lieu of Taxes (PILOT) project owner should have paid according to the project agreement to the school district. This field is blank if the project did not receive school property tax exemptions from the IDA, or the authority didn’t enter any information.
"applicant_postal_code", -- Zip code of project applicant
"total_pilot_payments_due", -- Amount of Total Payments in Lieu of Taxes (PILOT) that should have been made by the project owner according to the project agreement. It is the calculated total of the County PILOT Due, Local PILOT Due, and School District PILOT Due.
"part_of_or_related_to_multi_phase_project", -- This is a yes/no field that indicates whether the project is part of or related to a multi-phase project. Multi- phase projects are generally defined as being more than one IDA project with the same company for a specific location. If it is part of a multi-phase project, the project code of the original project is included in a separate field. This information is provided because IDAs may report all job-related data for all combined multi-phase projects as part of only one of the individual components of the project.
"total_pilot_made", -- Amount of Total Payments in Lieu of Taxes (PILOT) made by project owner. It is the calculated total of the County PILOT Made, Local PILOT Made, and School District PILOT Made.
"original_estimate_of_jobs_to_be_retained", -- Estimated number of Full Time Equivalent (FTE) jobs to be retained by the project as a result of the financial assistance provided.
"local_pilot_made", -- Amount of Payments in Lieu of Taxes (PILOT) made by project owner for local property taxes. This field is blank if the project did not receive local property tax exemptions, or the authority didn’t enter any information.
"ida_took_title_to_property", -- Yes/No field to indicate whether the IDA took title to the project property for the purposes of providing tax exemptions
"federal_tax_status_of_bonds", -- Indicates whether the bond is taxable or tax exempt. This field is blank if bonds or notes were not issued by the IDA as part of the financial assistance provided to the project owner.
"project_state", -- State of the project location
"net_exemptions", -- The total amount of financial assistance received by the project owner for the reported year. This is a calculated figure that equals Total Exemptions less Total PILOT made.
"of_ftes_before_ida_status", -- Number of Full Time Equivalent (FTE) jobs that existed for the project before the financial assistance was provided to the project
"estimated_average_annual_salary_of_jobs_to_be_created", -- Estimated average annual salary of the jobs to be created by the project (at current market rates).
"maximum_salary_of_jobs_to_be_created", -- Maximum salary for the jobs to be created by the project.
"project_type", -- Indicates whether the project type is: Bonds/Notes Issuance, Straight Lease, or Tax Exemptions. Bonds/Notes Issuance refers to projects that were financed with IDA issued bonds or notes. These projects may or may not receive additional financial assistance from the IDA. Straight Lease refers to non-bond/note projects that receive real estate tax exemptions and have entered into a Payment in Lieu of Taxes (PILOT) agreement with the IDA. Tax Exemptions refers to non- bond/note project that only receive sales and use tax exemption and/or mortgage recording tax exemption.
"bond_note_amount", -- Original principal amount of bonds or notes issued. This field is blank if bonds or notes were not issued by the IDA as part of the financial assistance provided to the project owner.
"project_address_line_1", -- Address of the project location
"local_pilot_due", -- Amount of Payments in Lieu of Taxes (PILOT) project owner should have paid according to the project agreement for local property taxes. This field is blank if the project did not receive local property tax exemptions, or the authority didn’t enter any information.
"project_code", -- Code assigned to the project by the Industrial Development Agency
"project_name", -- Name of the project receiving benefits from the Industrial Development Agency
"original_project_code", -- Code of the original project (if applicable) to which the multi-phase project is related. This field is blank if the project is not part of a multi-phase project.
"project_postal_code", -- Zip code of the project location
"project_city", -- City of the project location
"school_district_pilot_made", -- Amount of Payments in Lieu of Taxes (PILOT) made by project owner to the school district. This field is blank if the project did not receive school property tax exemptions from the IDA, or the authority didn’t enter any information.
"minimum_salary_of_jobs_to_be_created", -- Minimum salary for the jobs to be created by the project.
"current_of_ftes", -- Number of Full Time Equivalent (FTE) jobs during the current fiscal year. Construction jobs that are created as a result of the project should not be included. This field is blank if the authority didn’t enter any information.
"applicant_city", -- City of project applicant
"project_purpose", -- Indicates whether the purpose of the project is either: Civic Facility; Services; Construction; Agriculture, Forestry and Fishing; Wholesale Trade; Finance, Insurance and Real Estate; Transportation, Communication, Electric, Gas and Sanitary Services; Other Categories; Manufacturing; Clean Energy; Continuing Care Retirement Communities; Retail Trade These categories are derived from the North American Industry Classification System (NAICS) and are for illustrative purposes only and do not constitute an opinion as to whether any particular Project is authorized under Article 18-A of GML.
"total_project_amount", -- Cost of the entire project
"current_year_is_last_year_of_project", -- Yes indicates that the reporting year is the last year of IDA financial assistance for the project. This field is blank by default and left blank if the project will continue to receive financial assistance in subsequent reporting periods
"net_employment_change", -- Change of Full Time Equivalent (FTE) jobs since start of the project to current fiscal year. This figure equals Current # of FTEs minus # of FTEs before IDA status. This field is blank if the authority didn’t enter any information.
"benefited_project_amount", -- The amount that is affected by the IDA benefits. For example, if the total cost to build a new factory is $1 million, and the applicant requests $900,000 in bonds to finance and will contribute $100,000 of its own funds, the benefitted amount is $900,000.
"applicant_not_for_profit_corporation", -- Yes/No field to indicate whether the applicant is a non-for-profit corporation. This field is blank if the authority didn’t enter any information.
"date_project_approved", -- Date when the project was approved by the Industrial Development Agency.
"date_ida_took_title_to_property", -- Date when the IDA took title to property. Field is blank if IDA didn’t take title to property or if authority didn’t enter information.
"planned_end_year", -- Year financial assistance is planned to end. Field is blank if IDA didn’t take title to property or if authority didn’t enter information.
"applicant_name", -- Name of project applicant seeking financial assistance
"applicant_state", -- State of project applicant. Field is left blank if applicant is from outside the USA.
"applicant_country", -- Country of project applicant
"of_fte_construction_jobs_during_the_fiscal_year", -- Number of Full Time Equivalent (FTE) jobs related to project construction activities that were created during the fiscal year. This field is blank if the authority didn’t enter any information.
"fiscal_year_end_date", -- Date of Fiscal Year end for the authority
"local_sales_tax_exemption_amount", -- Amount of local sales tax exemptions provided to the project.
"county_real_property_tax_exemption_amount", -- Amount of county real property taxes for which the project would have been liable if the project occurred, but the IDA was not involved. This field is blank if the project did not receive county real property tax exemptions.
"local_property_tax_exemption_amount", -- Amount of local real property taxes for which the project would have been liable if the project occurred, but the IDA was not involved. This field is blank if the project did not receive local property tax exemptions.
"school_property_tax_exemption_amount", -- Amount of school real property taxes for which the project would have been liable if the project occurred, but the IDA was not involved. This field is blank if the project did not receive school property tax exemptions.
"mortgage_recording_tax_exemption_amount", -- Amount of mortgage recording tax exemption provided. This field is blank if the authority did not report any mortgage recording tax exemptions for the project.
"project_address_line2", -- Additional address information for the project location
"total_exemptions", -- This is the total amount of exemptions received by the project during the fiscal year. It is a calculated total of the real property tax, mortgage recording tax and sales tax exemptions received during the year for which the report is being completed. This field is blank is the authority did not report any tax exemptions for the project.
"total_exemptions_net_of_rptl_section_485_b_exemptions", -- Amount of real property tax exemptions the project received as a result of IDA status. The amount of any real property tax exemptions the project received under 485-b is not included in this amount. This field is blank if the project did not receive school property tax exemptions from the IDA.
"county_pilot_due", -- Amount of Payments in Lieu of Taxes (PILOT) project operator should have paid according to the project agreement for county property taxes. This field is blank if the project did not receive county real property tax exemptions.
"county_pilot_made", -- Amount of Payments in Lieu of Taxes (PILOT) made by project owner for county property taxes. This field is blank if the project did not receive county real property tax exemptions.
"estimated_average_annual_salary_of_jobs_to_be_retained", -- Estimated average annual salary for the jobs to be retained by the project (at current market rates).
"annual_lease_payment", -- Annual payment made to the IDA for the lease of the project property. This field is blank if the property is not being leased to the applicant.
"project_country", -- Country of the project location
"authority_name", -- Name of the Public Authority
"original_estimate_of_jobs_to_be_created" -- Estimated number of Full Time Equivalent (FTE) jobs to be created by the project as a result of the financial assistance provided. Construction jobs that are created as a result of the project should not be included.
FROM
"ny-gov/industrial-development-agencies-project-data-9rtk-3fkw:latest"."industrial_development_agencies_project_data"
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/industrial-development-agencies-project-data-9rtk-3fkw 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/industrial-development-agencies-project-data-9rtk-3fkw: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/industrial-development-agencies-project-data-9rtk-3fkwCheckout 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/industrial-development-agencies-project-data-9rtk-3fkw:latestThis will download all the objects for the latest tag of ny-gov/industrial-development-agencies-project-data-9rtk-3fkw 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/industrial-development-agencies-project-data-9rtk-3fkw: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/industrial-development-agencies-project-data-9rtk-3fkw: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/industrial-development-agencies-project-data-9rtk-3fkw is just another Postgres schema.