7,612
edits
Changes
Jump to navigation
Jump to search
→Add the geocoding
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===
*Industry, Firm type, Firm stage, Title, statecode, and other lookup tables.
*PortCoSBIR and PortCoPatent -- These are now out of date and don't appear to have build notes.
===Join in the exit data===
Bring in and clean up the IPO and MA (private target) data, match it to the PortCos, and reconcile the conflicts. This creates two core tables, one key table, and two PortCo results tables:
*IpoCleanNoDups
*MACleanNoDups
*ExitKeys (IPO and MA only, not PortCo)
*PortCoExit
*PortCoAliveDead
'''PortCoAliveDead''' follows the academic convention of marking a startup as alive if it has begun receiving investment and hasn't exited and if its last investment occurred less than five years ago.
==Base Tables==
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:
