ny-gov/industrial-development-agencies-project-data-9rtk-3fkw

  • ida
  • pilots
  • public authorities

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 procotol. 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
    "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.
    "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 current fiscal year. Construction jobs that are created as a result of the project are not included. This field is blank if the authority didn’t enter any information.
    "estimated_average_annual_salary_of_jobs_to_be_retained", -- Estimated average annual salary of the jobs to be retained by the project (at current market rates).
    "maximum_salary_of_jobs_to_be_created", -- Maximum salary of the jobs to be created by the project.
    "minimum_salary_of_jobs_to_be_created", -- Minimum salary of 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).
    "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 are not included.
    "net_exemptions", -- The total amount of financial assistance received by the project 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 operator. 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 operator according to the project agreement. It is the calculated total of the County PILOT Due, Local PILOT Due, and School District PILOT Due.
    "school_district_pilot_made", -- Amount of Payments in Lieu of Taxes (PILOT) made by project operator 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.
    "amt_pilot_school_due", -- Amount of Payments in Lieu of Taxes (PILOT) project operator 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.
    "local_pilot_due", -- Amount of Payments in Lieu of Taxes (PILOT) project operator 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.
    "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.
    "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.
    "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.
    "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.
    "state_sales_tax_exemption_amount", -- Amount of state sales tax exemptions provided to the project.
    "applicant_state", -- State of project applicant's business address
    "applicant_city", -- City of project applicant's business address
    "applicant_name", -- Name of project applicant for 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.
    "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
    "date_project_approved", -- Date when the project was approved by the Industrial Development Agency.
    "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.
    "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.
    "benefited_project_amount", -- The benefitted amount is 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.
    "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.
    "project_purpose", -- Indicates whether the purpose of the project is either:  Agriculture, Forestry and Fishing; Civic Facility;  Construction: Continuing Care Retirement Communities; Finance, Insurance and Real Estate;  Manufacturing; Retail Trade; Services; Transportation, Communication, Electric, Gas and Sanitary Services;  Wholesale Trade; or Other categories.  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
    "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.
    "project_address_line2", -- Additional address information for the project
    "project_address_line_1", -- Address of the project
    "original_project_code", -- Code of the original project (if any) to which the project is related. This field is blank if the project is not part of an existing project with the same company.
    "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.
    "project_code", -- Code assigned to the project by the Industrial Development Agency
    "fiscal_year_end_date", -- Date of fiscal year end for the authority
    "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 if the authority did not report any tax exemptions for the project.
    "total_project_amount", -- Cost of the entire project
    "project_name", -- Name of the project receiving Agency support
    "authority_name", -- Name of the Public Authority
    "local_pilot_made", -- Amount of Payments in Lieu of Taxes (PILOT) made by project operator 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.
    "county_pilot_made", -- Amount of Payments in Lieu of Taxes (PILOT) made by project operator for county property taxes. This field is blank if the project did not receive county real 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_country", -- Country of project applicant's business address
    "applicant_postal_code", -- Zip code of project applicant's business address
    "project_city", -- City of the project
    "current_year_is_last_year_of_project", -- Yes indicates that the reporting year is the last year of IDA financial assistance to the project. This field is blank by default and left blank if the project will continue to receive financial assistance in subsequent reporting periods.
    "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.
    "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.
    "project_country", -- Country of the project
    "project_state", -- State of the project
    "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 identified as 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.
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)"
 

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.

Related Documentation: