ny-gov/industrial-development-agencies-project-data-9rtk-3fkw
Icon for Socrata external plugin

Query the Data Delivery Network

Query the DDN

The 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
    "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.
    "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.
    "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.
    "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_sales_tax_exemption_amount", -- Amount of local sales tax exemptions provided to the project.
    "applicant_state", -- State of project applicant. Field is left blank if applicant is from outside the USA.
    "applicant_name", -- Name of project applicant seeking financial assistance
    "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.
    "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.
    "date_project_approved", -- Date when the project was approved by the Industrial Development Agency.
    "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.
    "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.
    "total_project_amount", -- Cost of the entire project
    "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.
    "project_postal_code", -- Zip code of the project location
    "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_name", -- Name of the project receiving benefits from the Industrial Development Agency
    "project_code", -- Code assigned to the project by the Industrial Development Agency
    "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_line2", -- Additional address information for the project location
    "project_address_line_1", -- Address of the project location
    "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.
    "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.
    "minimum_salary_of_jobs_to_be_created", -- Minimum salary for the jobs to be created by the project.
    "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.
    "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_city", -- City of the project location
    "project_state", -- State of the project location
    "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.
    "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
    "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.
    "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.
    "applicant_postal_code", -- Zip code of project applicant
    "state_sales_tax_exemption_amount", -- Amount of state sales tax exemptions provided to the project.
    "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.
    "authority_name", -- Name of the Public Authority
    "project_country", -- Country of the project location
    "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.
    "applicant_city", -- City of project applicant
    "fiscal_year_end_date", -- Date of Fiscal Year end for the authority
    "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.
    "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.
    "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
    "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).
    "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.
    "maximum_salary_of_jobs_to_be_created", -- Maximum salary for the jobs to be created by 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).
    "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
    "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.
    "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.
    "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.
    "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.
    "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.
    "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.
    "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.
    "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.
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.

This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.ny.gov. When you queryny-gov/industrial-development-agencies-project-data-9rtk-3fkw:latest on the DDN, we "mount" the repository using the socrata mount handler. The mount handler proxies your SQL query to the upstream data source, translating it from SQL to the relevant language (in this case SoQL).

We also cache query responses on the DDN, but we run the DDN on multiple nodes so a CACHE_HIT is only guaranteed for subsequent queries that land on the same node.

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 (like this repository), 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, where the author has pushed Splitgraph Images to the repository, you can "clone" and/or "checkout" the data using sgr cloneand sgr checkout.

Mounting Data

This repository is an external repository. It's not hosted by Splitgraph. It is hosted by data.ny.gov, and Splitgraph indexes it. This means it is not an actual Splitgraph image, so you cannot use sgr clone to get the data. Instead, you can use the socrata adapter with the sgr mount command. Then, if you want, you can import the data and turn it into a Splitgraph image that others can clone.

First, install Splitgraph if you haven't already.

Mount the table with sgr mount

sgr mount socrata \
  "ny-gov/industrial-development-agencies-project-data-9rtk-3fkw" \
  --handler-options '{
    "domain": "data.ny.gov",
    "tables": {
        "industrial_development_agencies_project_data": "9rtk-3fkw"
    }
}'

That's it! Now you can query the data in the mounted table like any other Postgres table.

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.