7,649
edits
Changes
Jump to navigation
Jump to search
→Summary
==Summary==
See:
*[[Jeemin Sim (Work Log)]]
Install ogr2ogr on mother:
apt install gdal-bin
There are 10 TIF shapefiles, which either have date date embedded or where date data is separately available. Of these, 8 are loaded from KML files with ogr2ogr:
ogr2ogr -f PostgreSQL PG:"dbname=vcdb4" Chicago.kml -nln tifchicago
ogr2ogr -f PostgreSQL PG:"dbname=vcdb4" Columbus.kml -nln tifcolumbus
ogr2ogr -f PostgreSQL PG:"dbname=vcdb4" WashingtonDC.kml -nln tifwashingtondc
ogr2ogr -f PostgreSQL PG:"dbname=vcdb4" Houston.kml -nln tifhouston
ogr2ogr -f PostgreSQL PG:"dbname=vcdb4" Vermont.kml -nln tifvermont
ogr2ogr -f PostgreSQL PG:"dbname=vcdb4" Dublin.kml -nln tifdublin
ogr2ogr -f PostgreSQL PG:"dbname=vcdb4" Atlanta.kml -nln tifatlanta
ogr2ogr -f PostgreSQL PG:"dbname=vcdb4" AlleghenyCounty.kml -nln tifalleghenycounty
Two are loaded with shp2pgsql, and in both cases the original data needs to be imported into ArcGIS and then exported in a standard projection as a first processing step. I used WGS1984:
--in ../StLouis1984
shp2pgsql -I "StLouisWGS1984" tifstlouis | psql -U researcher -W -d vcdb4
--in ../Dallas1984
shp2pgsql -I "DallasWGS1984.shp" tifdallas | psql -U researcher -W -d vcdb4
Sources:
*Chicago, IL, 138 with dates: https://data.cityofchicago.org/Community-Economic-Development/Boundaries-Tax-Increment-Financing-Districts/fz5x-7zak
*Columbus, OH, 68 with dates: http://data-columbus.opendata.arcgis.com/datasets/tax-increment-financing-areas?geometry=-112.588%2C-29.382%2C112.588%2C29.382
*Washington, DC:
**17 without dates: https://opendata.dc.gov/datasets/tax-increment-financing-tif-areas
**WashingtonDCNamesAndDates.txt compiled from: http://lims.dccouncil.us, https://code.dccouncil.us/dc/council/code/sections, https://ggwash.org/view/12907/what-can-dc-learn-from-its-successful-subsidies, https://cfo.dc.gov/sites/default/files/dc/sites/ocfo/publication/attachments/FY%202018%20DC%20CAFR_Full%20Report.pdf, https://code.dccouncil.us/dc/council/code/titles/2/chapters/12/subchapters/IX/
*Houston, TX:
**17 without dates: https://cohgis-mycity.opendata.arcgis.com/datasets/coh-tirz
**HoustonDatesAndData.txt compiled from https://www.houstontx.gov/finance/COHTIRZPerformanceAudit-FINAL(INCLUDINGAPPENDICES-NOMAPS).pdf
*Dallas, TX, 214 with dates (needs projection transformation): http://www.dallascad.org/GISDataProducts.aspx
*Vermont:
*12 without dates: http://geodata.vermont.gov/datasets/cd53b08d40fc4e7dbc2757c36d038af5_7/data
*VermontNamesAndDates.txt compiled from https://accd.vermont.gov/sites/accdnew/files/documents/DED/VEPC/Tiff/TIFDISTRICTSstatus.pdf and https://accd.vermont.gov/community-development/funding-incentives/tif
*Dublin, OH, 46 with dates: https://data-dublinohio.opendata.arcgis.com/datasets/tax-increment-finance-zones
*Altanta, GA, 10 with dates: https://dcp-coaplangis.opendata.arcgis.com/datasets/tax-allocation-district
*AlleghenyCounty (Pittsburg), PA:
**34 without dates: https://data.wprdc.org/dataset/allegheny-county-tif-boundaries
**AlleghenyCountyNamesAndDates.txt compiled from https://www.alleghenycontroller.com/admin/attachments/7880501fe636c1fe90fa572eb72b54292019MarTue13550615536085066512bd43d9caa6e02c990b0a82652dca36830bb3ce75baae6d5313ca822b2dc2FinalEconomicDevelopmentTaxSubsidiesReport.pdf
*St. Louis, MO:
**118 without dates (needs projection transformation): https://www.stlouis-mo.gov/data/tax-increment-financing-districts.cfm
**StLouisNamesAndDates.txt compiled from https://www.stlouis-mo.gov/government/departments/sldc/documents/upload/Summary-of-TIF-Redevelopment-Projects-Updated-12-31-18.pdf
===Notes from the Agglomeration Project===
====Chicago====
Starting the process with Chicago. Do the following:
*Go to: https://data.cityofchicago.org/Community-Economic-Development/Boundaries-Tax-Increment-Financing-Districts/fz5x-7zak
*Save the KML as E:\projects\agglomeration\TIF\Chicago.KML
*Load the KML into the dbase using ogr2ogr [https://gdal.org/programs/ogr2ogr.html], note the the nln option creates a new layer (table)
*Note that you have to hit refresh on the dbase (or at least the table list) in DataGrip to get the new table to show up
*Also, you'll need to fix the date types in post-processing. Note that null dates are going to show up as year 0, which doesn't actually exist and is incorrect (try SELECT to_date('0', 'YYYY')) and again with -1. See https://github.com/sequelize/sequelize/issues/3762 and blame the pope.
ogr2ogr -f PostgreSQL PG:"dbname=vcdb3" Chicago.kml -nln tifchicago
The table tifchicago corresponds to the XML below, except that it has some extra fields. The index field seems to be autogenerated, and tessellate, extrude and visibility are always -1,0, and -1 respectively. All other fields are blank, including description, timestamp, begin, and end (note that you can query end with "end" in postgres):
SELECT ogc_fid, name, description, timestamp, begin, "end", altitudemode, tessellate, extrude, visibility, draworder, icon FROM tifchicago;
The KML is an XML file with meta data (including an approval date, an expiration date, and a name, as well as some other values, some of which could be derived from the geometry) and then a set of points that describe the outer ring of a polygon:
<Placemark>
<styleUrl>#defaultStyle</styleUrl>
<name>Northwest Industrial Corridor</name>
<ExtendedData>
<Data name="approval_d"><value>12/2/1998</value></Data>
<Data name="comm_area"><value>19,20,23,25,26,27</value></Data>
<Data name="expiration"><value>12/2/2021</value></Data>
<Data name="ind"><value>Industrial</value></Data>
<Data name="name_trim"><value>Northwest Industrial Corridor</value></Data>
<Data name="objectid"><value>0</value></Data>
<Data name="objectid_1"><value>107</value></Data>
<Data name="ref"><value>T- 64</value></Data>
<Data name="repealed_d"><value></value></Data>
<Data name="sbif"><value>Y</value></Data>
<Data name="shape_area"><value>51402231.0322</value></Data>
<Data name="shape_leng"><value>80417.4828932</value></Data>
<Data name="show"><value>1</value></Data>
<Data name="type"><value>Existing</value></Data>
<Data name="use"><value>Industrial</value></Data>
<Data name="wards"><value>27,28,30,31,37</value></Data>
</ExtendedData>
<MultiGeometry>
<Polygon>
<outerBoundaryIs>
<LinearRing>
<coordinates>
-87.74541914577178,41.92534327389125
-87.74541599224814,41.92516215074396
</coordinates>
</LinearRing>
</outerBoundaryIs>
</Polygon>
</MultiGeometry>
</Placemark>
The ind variable might be useful if they are sufficiently systematic across jurisdictions. '''use''' was a messy version of '''ind'''. The '''sbif''' apprears to be a Small Business Improvement Fund grant specific to Chicago [https://somercor.com/sbif/]
SELECT sbif, count(*) FROM TifChicagoClean GROUP BY sbif;
sbif count
Y 92
N 45
SELECT DISTINCT ind FROM TifChicagoClean;
ind
Mixed-Use
Residential
Mixed-use
Industrial
Commercial
Transportation
====One by One====
Chicago -- credit Jonathan Levy, created Jul 17, 2013, updated Mar 19, 2019 [https://data.cityofchicago.org/Community-Economic-Development/Boundaries-Tax-Increment-Financing-Districts/fz5x-7zak], has 138 TIFs with dates, approved from 1989-09-13 to 2018-10-31. Expiration ranges from 2018-07-13 to 2052-12-31.
ogr2ogr -f PostgreSQL PG:"dbname=vcdb3" Chicago.kml -nln tifchicago
Columbus, OH. Last updated 8/8/2017. Downloaded full dataset [http://data-columbus.opendata.arcgis.com/datasets/tax-increment-financing-areas?geometry=-112.588%2C-29.382%2C112.588%2C29.382], which should contain 69 records as a KML. URL in each record is out of date but just points to https://www.columbus.gov/development/economic-development/. Records '''have PASSAGE dates''' but no end dates. They also have DTE_STATUS but it isn't clear that this really means anything (something to do with filling form DTE24?).
ogr2ogr -f PostgreSQL PG:"dbname=vcdb3" Columbus.kml -nln tifcolumbus
Washington, DC. Last updated 6/25/2019. Downloaded full dataset [https://opendata.dc.gov/datasets/tax-increment-financing-tif-areas], which should contain 17 records. There are '''no dates''' and a quick search didn't find any. There's useful information here: https://cfo.dc.gov/sites/default/files/dc/sites/ocfo/publication/attachments/tx_tif_chapter_2020j.pdf
It should also be noted that firms located in the DC Ballpark TIF area are specifically excluded from being Qualified High Technology Companies[https://otr.cfo.dc.gov/sites/default/files/dc/sites/otr/publication/attachments/48605_FR-399_22416.pdf]. Checked the old version, and it didn't have dates either... so the labelling on [[TIF Project]] appears incorrect.
Call: 202 727 1140 -- Aisa Reed, senior data analyst.
202 727 4364 Nate Cruz, deputy director of Office of Economic Development Finance
202 478 9152 Nancy Fox, Senior Policy Analyst, Office of Economic Development Finance
Note that we might be able to infer them, one by one, from financial statements or by looking at their creating acts: https://cfo.dc.gov/node/230912
ogr2ogr -f PostgreSQL PG:"dbname=vcdb3" WashingtonDC.kml -nln tifwashingtondc
Some date data taken from: https://cfo.dc.gov/sites/default/files/dc/sites/ocfo/publication/attachments/FY%202018%20DC%20CAFR_Full%20Report.pdf
Others can be taken directly from the legislation either http://lims.dccouncil.us or https://code.dccouncil.us/dc/council/code/sections (better through Google). In 2 cases, I also took data from a news article [https://ggwash.org/view/12907/what-can-dc-learn-from-its-successful-subsidies].
See also, DC's TIF legislation: https://code.dccouncil.us/dc/council/code/titles/2/chapters/12/subchapters/IX/
Houston - no longer exists... But we have the shape files from the previous round of work. http://data.houstontx.gov/dataset/city-tax-increment-reinvestment-zones-tirz now leads to a dead link for the zip, but it does say that the file was created June 30, 2017, 2:27 PM (UTC-04:00) and last updated July 20, 2017, 2:22 PM (UTC-04:00). Again, though, '''no dates'''!
Can go one by one through the creation docs found at https://www.houstontx.gov/ecodev/tirz_info.html This site lists 27 TIRZ, whereas there are only 23 in the old data. Harris County says that it participates in just 5 Houston TIRZs [https://hcoed.harriscountytx.gov/tirz.aspx].
In z:/vcdb3/Houston:
shp2pgsql -I "TIRZ.shp" tifhouston | psql -U researcher -W -d vcdb3
drop table tifhouston;
In z:/vcdb3/HarrisCounty
shp2pgsql -I "TIRZ.shp" tifharriscounty | psql -U researcher -W -d vcdb3
drop table tifharriscounty;
See https://www.bostongis.com/pgsql2shp_shp2pgsql_quickguide.bqg for a cheatsheet for shp2pgsql.
Found City of Houston KML data here: https://cohgis-mycity.opendata.arcgis.com/datasets/coh-tirz It doesn't have dates but does have 27 rows and was last updated 7/10/2019.
ogr2ogr -f PostgreSQL PG:"dbname=vcdb3" Houston.kml -nln tifhouston
One TIF had a null name, which was changed to UNKNOWN. By process of elimination, this was Village Enclaves (Terminated).
Found the dates in a review: https://www.houstontx.gov/finance/COHTIRZPerformanceAudit-FINAL(INCLUDINGAPPENDICES-NOMAPS).pdf
This was processed and extracted to E:\projects\agglomeration\TIF\HoustonDatesAndData.txt. Note that the total increment and project costs were $830,822k and $1,732,558k, respectively.
Also, the Texas Comptroller's office has a nice chapter on TIFs: https://www.cdfa.net/cdfa/cdfaweb.nsf/ordredirect.html?open&id=tifintexas.html
Dallas - downloaded 'latest' data [http://www.dallascad.org/GISDataProducts.aspx]. But no meta data is available. Shockingly, the data looks good and up to date. In contains legalstart and legalend fields for all 214 rows!
In z:/vcdb3/Dallas
shp2pgsql -I "LocalTaxDistrict.shp" tifdallas | psql -U researcher -W -d vcdb3
Vermont -- 12 records, last updated two years ago (4/7/2017), http://geodata.vermont.gov/datasets/cd53b08d40fc4e7dbc2757c36d038af5_7/data no dates available but one state contact (Megan Sullivan Executive Director, Vermont Economic Progress Council (802) 798-2221 Megan.Sullivan@vermont.gov) and an individual contact for each one. Dates are readily available from: https://accd.vermont.gov/sites/accdnew/files/documents/DED/VEPC/Tiff/TIFDISTRICTSstatus.pdf Other, more detailed info, is also available [https://accd.vermont.gov/community-development/funding-incentives/tif]. Note that Colchester was dissolved, having never become active and Milton has finished its debt service. TIFs get 10 yrs from approval to incur debt and then 20 yrs from debt to retain increment, which ends when debt service ends.
ogr2ogr -f PostgreSQL PG:"dbname=vcdb3" Vermont.kml -nln tifvermont
Dublin, OH -- 46 records, last updated 5/8/2018, seems up to date. Has dates!
ogr2ogr -f PostgreSQL PG:"dbname=vcdb3" Dublin.kml -nln tifdublin
Atlanta, GA -- Seems like 6+4 projects [https://www.atlantaga.gov/government/departments/city-planning/office-of-zoning-development/plans-and-studies/tax-allocation-district-tad]. Found here: https://dcp-coaplangis.opendata.arcgis.com/datasets/tax-allocation-district All 10, last updated 8/29/2018. Has creation and end dates.
ogr2ogr -f PostgreSQL PG:"dbname=vcdb3" Atlanta.kml -nln tifatlanta
AlleghenyCounty (Pittsburg), PA -- Available from https://data.wprdc.org/dataset/allegheny-county-tif-boundaries but dates are "None" through out. Looks up to date (says August 20, 2019, 5:00 PM), and has 34 records. Same data available from http://openac-alcogis.opendata.arcgis.com/datasets/allegheny-county-tif-boundaries and https://catalog.data.gov/dataset/allegheny-county-tif-boundaries-a1715
ogr2ogr -f PostgreSQL PG:"dbname=vcdb3" AlleghenyCounty.kml -nln tifalleghenycounty
A report online [https://www.alleghenycontroller.com/admin/attachments/7880501fe636c1fe90fa572eb72b54292019MarTue13550615536085066512bd43d9caa6e02c990b0a82652dca36830bb3ce75baae6d5313ca822b2dc2FinalEconomicDevelopmentTaxSubsidiesReport.pdf], saved to the TIF folder, has end dates for 28 of 34 TIFs, which was used to infer start dates (T-20 from ). Note that the following TIF boundaries do not have dates:
CENTER TRIANGLE - LAZARUS
CENTER TRIANGLE - MELLON CLIENT SERVICE CENTER
MCCLAREN ROAD TIF
NORTH SHORE
PENN LIBERTY PLAZA
SCHENLEY CENTER
SEWICKLEY
SOUTH SIDE WORKS
WASHINGTON PARK
Also the following TIFs do not have boundaries:
Hazelwood‐Almono
Summerset at Frick Park
Robinson Mall Peripheral is lumped in with Robinson Mall in the boundary data.
====Looking for add ons====
State-by-State-TIF-Spreadsheeet.xls is in the TIF folder. It was found on https://www.bettertogetherstl.com/economic-development-source-materials?rq=TIF and details the state level inception of TIF programs.
St. Louis, has shape files: https://www.stlouis-mo.gov/data/tax-increment-financing-districts.cfm, also available as GEOJSON, but no date info. File name includes TIF_DISTRICTS_20190830, which implies it is up-to-date. St. Loius County has a KML file [https://worldmap.harvard.edu/data/geonode:slcounty_tif_aqt] with a PASSED field. It may or may not coincide... and might not be up to date.
There are also recent KMLs without dates here: https://hub.arcgis.com/datasets/8918f94ffeca407fa48db2d66bc74c52/data
And an official map, without data available seperately here: https://gisportal.stlouis-mo.gov/portal/apps/webappviewer/index.html?id=5cf974631f65487f868c5bfcfaa3e9f9 This says to contact Dale Ruthsatz (314) 657-3732 for help. Might also be able to get dates out of https://dor.mo.gov/business/tif/.
Found the data on the dates here: https://www.stlouis-mo.gov/government/departments/sldc/documents/upload/Summary-of-TIF-Redevelopment-Projects-Updated-12-31-18.pdf Also, the duration is a max of 23 years [stlouis-mo.gov/government/departments/sldc/economic-development/tax-increment-financing.cfm]
ogr2ogr -f PostgreSQL PG:"dbname=vcdb3" StLouisCounty.kml -nln tifstlouiscounty
--There are 46 records and we'd have to work to pull the passed field out of the description
DROP TABLE tifstlouiscounty;
shp2pgsql -I "TIF_DISTRICTS_20190830.shp" tifstlouis | psql -U researcher -W -d vcdb3
--118 records
But, like Dallas, this data is in a non-standard projection (NAD_1983_StatePlane_Missouri_East_FIPS_2401_Feet).
DROP TABLE tifstlouis;
shp2pgsql -I "StLouisWGS1984" tifstlouis | psql -U researcher -W -d vcdb3
These were put together with the dates using some regexes:
^(.*) Retired.*$
^(.*) Active.*$
^(.*) Terminated.*$
^(.*) Never Approved.*$
^(.*) On Hold.*$
.* (\d\d?\-\w\w\w-\d\d).*
==Old Work==
