NYC Geodatabase (nyc_gdb) Revised ZBP Tables
Frank Donnelly, Geopspatial Data Librarian
Baruch College CUNY
July 12, 2017

*** Revisions to ZBP Tables ***

There were two series of significant changes in how the US Census ZIP Code Business Patterns data was processed for inclusion on the NYC Geodatabase. The ZBP data for 2015 as it's published in nyc_gdb_july2017 represents the current, correct version for processing the data. If any users wish to go back in time and use the earlier ZBP data (from 2010 through 2014) they should use the revised tables, and not the tables stored in the earlier versions of the database. The revised tables are saved as tab-delimited text files that can be imported into the database.

* Changes in July 2014, impacts ZBP data from 2010 and 2011 *

There were significant changes in how the US Census ZIP Code Business Patterns data was processed and represented in nyc_gdb_july2014. The ZBP data from the Census represents businesses by USPS ZIP Code, while the geography in the nyc_gdb represents Census ZCTAs. In early versions of the database the data in the ZBP biz tables (b_zctas_YEARbiz_emp and b_zctas_YEARbiz_ind) only included records for ZIP Codes that had a matching ZCTA number; as a result data for ZIP Codes that lacked geographic area (ZIPs for clusters of PO Boxes and for large organizations) was entirely missing. 

To correct this and provide complete coverage of businesses in the city, the 2012 ZBP data for all ZIP Codes was aggregated to the ZCTA level, and the two ZBP tables in the database now represent records for ZCTAs and not ZIP Codes. So when data from the zcta biz "b" tables are joined to ZCTA "a" geography for mapping, the data correctly represents all businesses within that ZCTA. A new table, b_zips_to_zcta, was included to show how ZIPs and ZCTAs in the city are correlated and how ZIPs were aggregated to the ZCTA level. Some identifiers in both the employment and industry tables (GEOID, GEOID2,and GEOLABEL) were dropped and a new identifier ZCTA5 was designated as the primary key. New flag or notes fields were added to the employment table to indicate whether data is missing for certain establishments due to disclosure regulations.

This ZIP to ZCTA aggregation process was also performed on ZBP data from 2011 and 2010, and users who wish to have corrected data for these years can import these revised tables into their database and drop the old ones. This is strongly recommended, as this data is more accurate and complete for representing data at the ZCTA level. 

The b_zips_to_zcta table was created from a master list provided by the Missouri Census Data Center at http://mcdc.missouri.edu/allabout/zipcodes_2010supplement.shtml. It is provided for general reference and allows for the aggregation of ZIP Code data to the 2010 ZCTA level. 

For more information on the difference between ZIPs and ZCTAs see
http://www.census.gov/geo/reference/zctas.html.

* Changes in July 2017, impacts ZBP data from 2010 to 2014 *

For the nyc_gdb_july2017 iteration of the geodatabase, updated source data was used to relate USPS ZIP Codes to ZCTAs; the Missouri Census Data Center published a new crosswalk table (which was provided by the UDS Mapper project) in Nov 2015 which includes a number of updates and corrections. This new table is more accurate and provides a number of fixes; in the NYC area 5 ZIP Codes were dropped, 1 was added, and 19 ZIP Codes were reassigned to different ZCTAs. The two most significant changes were the reallocation of ZIP 11249 from ZCTA 11201 to ZCTA 11211 (this allocation is for a new ZIP Code in Brooklyn this was originally miscoded and assigned to Queens) and the reallocation of ZIP 10118 from ZCTA 10016 to ZCTA 10001 (represents the Empire State Building, which was originally allocated to an adjacent ZCTA and not the one where it is actually located). Most of the reassignments were located in Manhattan and the change in the number of businesses was minor.

The 2015 ZBP data that's published in the July 2017 nyc_gdb was generated using this new source, and the b_zips_to_zcta table was updated to reflect this. For the sake of consistency, ZBP data from 2010 to 2014 was recreated using the new source and are provided as tab-delimited text files that can be imported into the database (for users who wish to use the historical data).