VCDB20 - Revision history

Ed at 20:39, 31 May 2024

2024-05-31T20:39:52Z

2021-07-05T20:44:46Z

Add the geocoding

2021-07-05T20:18:37Z

Add the geocoding

2021-06-22T23:51:59Z

Add the geocoding

2021-06-20T17:28:33Z

Add the geocoding

2021-06-20T17:10:17Z

Add the geocoding

2021-05-23T18:38:06Z

Add the geocoding

2021-05-23T18:36:54Z

Load the Additions

2021-05-23T18:24:30Z

Add the geocoding

2021-05-23T18:16:37Z

Load the Additions

@@ Line 4: / Line 4: @@
 |Has owner=Ed Egan
 }}
-<onlyinclude>The [[VCDB20]] project documents a build of my VCDB -- '''V'''enture '''C'''apital '''D'''ata'''B'''ase -- covering until the end of 2020. Each VCDB includes investments, funds, startups, executives, exits, locations, and more, derived from data from [[VentureXpert]]. This project updates [[vcdb4]], which covered (almost) to the of Q3 2019, and replaces [[VCDB20H1]] and [[VCDB20Q3], which were partial builds. See also: [[SDC Normalizer]].</onlyinclude>
+<onlyinclude>The [[VCDB20]] project documents a build of my VCDB -- '''V'''enture '''C'''apital '''D'''ata'''B'''ase -- covering until the end of 2020. Each VCDB includes investments, funds, startups, executives, exits, locations, and more, derived from data from [[VentureXpert]]. This project updates [[vcdb4]], which covered (almost) to the of Q3 2019, and replaces [[VCDB20H1]] and [[VCDB20Q3]], which were partial builds. See also: [[SDC Normalizer]].</onlyinclude>
 ==Data design==

@@ Line 102: / Line 102: @@
 * Data: national_places.txt -> national_places_processed.txt (regexes)
 * Pop the last word off the placename!
 ===Load the Additions===

@@ Line 98: / Line 98: @@
 *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, statecpde, placename, placefp, etc.):
+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)

@@ Line 97: / Line 97: @@
 *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
 ===Load the Additions===

@@ Line 95: / Line 95: @@
 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/docs/maps-data/data/rel/zcta_place_rel_10.txt

@@ Line 90: / Line 90: @@
 *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).
 ===Load the Additions===

@@ Line 87: / Line 87: @@
 Note that PortCoGeoID and other tables are built in the BuildBaseTables.sql script.
-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 BuildBaseTables.sql.
+Change to Load script:
 ===Load the Additions===

@@ Line 97: / Line 97: @@
 *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===

← Older revision		Revision as of 18:24, 23 May 2021
Line 86:		Line 86:

	Note that PortCoGeoID and other tables are built in the BuildBaseTables.sql script.		Note that PortCoGeoID and other tables are built in the BuildBaseTables.sql script.
		+
		+	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 BuildBaseTables.sql.

	===Load the Additions===		===Load the Additions===