Note that PortCoGeoID and other tables are built in the BuildBaseTables.sql script.
Change to Load script:
*Retrieve and load ZCTA Gazetteer from the [https://www.census.gov/geographies/reference-files/time-series/geo/gazetteer-files.html U.S. Census]
*A small but meaningful proportion of US venture firms have zip codes but not addresses. I created the table firmbogeoplus to add longitude and latitudes for these firms in Load.sql (right after firmbogeo).
A small number of US PortCos have zipcodes but not places (which are used in the Rankings and elsewhere). To address this, I loaded the 2010 ZCTA to place lookup in Load.sql (passing the result through TigerGeog) to get the placename. This is now incorporated into PortCoGeoid in BuildBaseTables.sql.
2010 Census data (2020 isn't available but the mappings are fairly static):
*https://www.census.gov/geographies/reference-files/time-series/geo/relationship-files.2010.html
*https://www2.census.gov/geo/pdfs/maps-data/data/rel/explanation_zcta_place_rel_10.pdf
*https://www2.census.gov/geo/docs/maps-data/data/rel/zcta_place_rel_10.txt
I also load up the 2010 ANSI codes for places (i.e., statefp, statecode, placename, placefp, etc.):
* Source: https://www.census.gov/library/reference/code-lists/ansi.html
* Data: national_places.txt -> national_places_processed.txt (regexes)
* Pop the last word off the placename!
Note that the postprocessing of these two tables is done at the end of Load.sql. The final table is '''zctaplaceinfo''' which takes zcta, statecode and provides back placename and geoid (of the place).
===Load the Additions===
The base tables are built using BuildBaseTables.sql. These provide common foundations for:
*PortCo Geography
*Other PortCo tables, including industry, id, cpi adjustments, geoids.**Exit, Alive/dead and patents & SBIR/STTR grants grant (note that these need updating)information is also included.*Firm variables (note that the full build is only done for firms that make growth investments).*Round Line Joiner (RLJoiner) tables.*PortCo People, including gender, dr., titles, serials.
*Fund People: gender and dr.
*The Master tables: