7,613
edits
Changes
Jump to navigation
Jump to search
no edit summary
{{Project
|Has project output=Data,Tool,How-to
|Has sponsor=Kauffman Incubator Project
|Has title=vcdb4
|Has owner=Ed Egan,
and others.
The build should be done as quickly but cleanly as possible, as it is needed right away but also will likely need to be updated in January of 2020 to reflect 2019's year -end. ===Notes=== There appears to be an error in the M&A data processing, which needs exploration. Compare: SELECT * FROM portcoexit WHERE coname ILIKE 'Augmentix%' OR coname ILIKE 'CheMatch%' OR coname ILIKE 'Hispanic Television%' OR coname ILIKE 'IDev T%' OR coname ILIKE 'Merrick%' OR coname ILIKE 'RosettaMed%' OR coname ILIKE 'SAT Corp%' OR coname ILIKE 'Visualase%'; SELECT * FROM mas WHERE targetname ILIKE 'Augmentix%' OR targetname ILIKE 'CheMatch%' OR targetname ILIKE 'Hispanic Television%' OR targetname ILIKE 'IDev T%' OR targetname ILIKE 'Merrick%' OR targetname ILIKE 'RosettaMed%' OR targetname ILIKE 'SAT Corp%' OR targetname ILIKE 'Visualase%';
==SDC Platinum Requests==
The next phase is to standardize the names and match the portcos to the IPOs and MAs. Then clean up all of the core tables. That takes us to the end of the old LoadingScriptV1.sql. We then need to do the equivalent of updating GeoLoad.sql. This time, we are going to use the old data, as before, but try updating any portco or firm that doesn't have at least 4dp (11.1m resolution) or perhaps 5dp (1.11m resolution) accuracy [http://wiki.gis.com/wiki/index.php/Decimal_degrees]. We have to fix the PortCo keys first, but we can only run 2,500 queries through Google each day for free per API key, so this should be a batch job.
==Enable PostGIS Extensions==
Before we can load up geometries and other PostGIS types, and do PostGIS operations, we need to:
create extension postgis;
===Updating GeoCoding===
Likewise, we have to key off of firmname for all firm tables. It isn't that we don't have other identifying information in firmbase (all records have founding date) but we only have firmname as a foreign key in the fund table and branch office table.
===Additions===
====Population====
For now:
*The population table (see Population.sql)
*The statepop2017 table (from States2017PopulationEstimate.txt, with DC (2018), GU (2017), and PR (2017) added.
We might want to join in the [[American Community Survey (ACS) Data]] too.
====Tiger Places====
We will use the old Tiger Places, but it is worth exploring how to rebuild this from scratch. First, visit https://www.census.gov/geographies/mapping-files/time-series/geo/tiger-line-file.html. Then download each state's place file. Load them and merge them. See [[PostGIS_Installation#Bulk_Download_TIGER_Shapefiles]] for more information. It is also worth reading the [[Tiger Geocoder]] and [[Jeemin Sim (Work Log)]].
====CPI====
We loaded the old CPI file, updating it with real numbers for 2018 and an estimate for 2019 based on extrapolating the last four months from the first eight, and then computing an annual average. The inflator is normalized to 2017 dollars. Data was taken from: https://www.bls.gov/cpi/tables/supplemental-files/historical-cpi-u-201908.pdf
====Other things to do====
We also might need to add:
*Accelerator data? (See CohortPortCoMatch.sql for doing the portco matching) -- this might be in its own dbase
*GSP (see Adding GDP.sql)
*Industry, Firm type, Firm stage and other lookups (see MatchingEntreps.sql)
*Titlelookup (see MatchingEntreps.sql)
*PortCoSBIR and PortCoPatents (see MatchingEntreps.sql)
==Building Datasets==
We need to collect code from:
*Ranking.sql
*MatchingEntreps.sql, which builds:
**Women
**Some of matching VC-Entreps? (much might now be redundant)
*RevisedDBaseCode.sql, which builds:
**Matching VC-Entreps
**Some base tables and the super tables: PortCoSuper, FirmSuper, etc.
*Agglomeration.sql (the latest version is in E:\projects\agglomeration), which builds:
**Agglomeration!
**Note: Needs updating to reflect latest decisions
===BuildBaseTables===
To this end, the base tables, which are used by multiple different projects, are built in BuildBaseTables.sql
Build notes:
*Common code from MatchingEntrepsV3.sql was moved to BuildBaseTables.sql
*Almost all of the women's code now appears to be in the new base
*The super tables still need building
*The code for the Matching VC-Entreps project is now almost entirely in RevisedDBaseCode.sql
==Agglomeration==
This build supports the [[Urban Start-up Agglomeration and Venture Capital Investment]] paper. It uses the sql script: AgglomerationVcdb4.sql, which is in E:\projects\agglomeration. The notes are here: [[Urban_Start-up_Agglomeration_and_Venture_Capital_Investment#vcdb4]]
==Ranking==
A new Ranking.sql file was created in E:\projects\vcdb4 and run.
There are imperfect matches between cities in VentureXpert and places in Tiger. In the past, this hasn't mattered as we'd considered such a small sub set of cities (and had gotten lucky on coverage). Geocoding covers 97.2607% (47.760 out of 49,136) of companybasecore, 48,815 (99.35%) of which have a non-null city. This drops to 96.42% of firms with datefirstinv >= 2010. Using just geocoded would therefore cost us a small number of firms, but these may not be randomly distributed. What we need, then is a complete lookup table...
