Delineating Spatial Agglomerations - Revision history

Ed at 15:23, 10 August 2022

2022-08-10T15:23:07Z

Ed at 22:55, 20 July 2022

2022-07-20T22:55:49Z

Ed at 22:55, 20 July 2022

2022-07-20T22:55:01Z

Ed: /* Data Processing Steps */

2022-07-20T22:52:46Z

Data Processing Steps

Ed: /* Data Processing Steps */

2022-07-20T22:52:19Z

Data Processing Steps

Ed: /* Data Processing Steps */

2022-07-20T22:51:43Z

Data Processing Steps

Ed: /* Overview */

2022-07-20T22:51:09Z

Overview

Ed: /* Overview */

2022-07-20T22:50:50Z

Overview

Ed at 22:50, 20 July 2022

2022-07-20T22:50:15Z

Ed at 22:48, 20 July 2022

2022-07-20T22:48:06Z

@@ Line 1: / Line 1: @@
 This page provides code and dataset development material for:
   Egan, Edward J. and James A. Brander (2022), "New Method for Identifying and Delineating Spatial Agglomerations with Application to Clusters of Venture-Backed Startups.", Journal of Economic Geography, Manuscript: JOEG-2020-449.R2, forthcoming.
 == Overview ==

@@ Line 10: / Line 10: @@
 All of our data assembly, and much of our data processing and analysis, is done in a [https://www.postgresql.org/ PostgreSQL] [https://postgis.net/ PostGIS] database. See our [[Research Computing Infrastructure]] page for more information.
-However, we rely on [[https://www.python.org/ python]] scripts to retrieve addresses from Google Maps, as well as compute the [https://en.wikipedia.org/wiki/Hierarchical_clustering Hierarchical Cluster Analysis (HCA)] itself, and estimate a cubic to determine the HCA-regression method agglomeration count for an [https://en.wikipedia.org/wiki/Metropolitan_statistical_area MSA]. We also use two [https://www.stata.com/ Stata] scripts: one to compute the HCA-regressions, and another to estimate the paper's summary statistics and regression specifications. Finally, we use QGIS to construct the map images based on queries to our database. These images use a [https://maps.google.com Google Maps] base layer.
+However, we rely on [https://www.python.org/ python] scripts to retrieve addresses from Google Maps, as well as compute the [https://en.wikipedia.org/wiki/Hierarchical_clustering Hierarchical Cluster Analysis (HCA)] itself, and estimate a cubic to determine the HCA-regression method agglomeration count for an [https://en.wikipedia.org/wiki/Metropolitan_statistical_area MSA]. We also use two [https://www.stata.com/ Stata] scripts: one to compute the HCA-regressions, and another to estimate the paper's summary statistics and regression specifications. Finally, we use QGIS to construct the map images based on queries to our database. These images use a [https://maps.google.com Google Maps] base layer.
 == Data Processing Steps ==
-The script [[:File:Agglomeration_CBSA.sql.pdf|Agglomeration_CBSA.sql]] provides the processing steps within the PostgreSQL database. We first load the startup data, add in the longitudes and latitudes, and combine them with the [[https://en.wikipedia.org/wiki/Core-based_statistical_area CBSA]] boundaries. Startups in our data our keyed by a triple (coname, statecode, datefirstinv) as two different companies can have the same names in different states, or within the same state at two different times.
+The script [[:File:Agglomeration_CBSA.sql.pdf|Agglomeration_CBSA.sql]] provides the processing steps within the PostgreSQL database. We first load the startup data, add in the longitudes and latitudes, and combine them with the [https://en.wikipedia.org/wiki/Core-based_statistical_area CBSA] boundaries. Startups in our data our keyed by a triple (coname, statecode, datefirstinv) as two different companies can have the same names in different states, or within the same state at two different times.
 [[File:AgglomerationProcess_v2.png|center|thumb|768px|Data Processing Steps]]

← Older revision		Revision as of 22:55, 20 July 2022
Line 1:		Line 1:
−	This page provides code and ~~data set~~ development material for:	+	This page provides code and dataset development material for:
	Egan, Edward J. and James A. Brander (2022), "New Method for Identifying and Delineating Spatial Agglomerations with Application to Clusters of Venture-Backed Startups.", Journal of Economic Geography, Manuscript: JOEG-2020-449.R2, forthcoming.		Egan, Edward J. and James A. Brander (2022), "New Method for Identifying and Delineating Spatial Agglomerations with Application to Clusters of Venture-Backed Startups.", Journal of Economic Geography, Manuscript: JOEG-2020-449.R2, forthcoming.

@@ Line 13: / Line 13: @@
 == Data Processing Steps ==
 [[File:AgglomerationProcess_v2.png|center|thumb|768px|Data Processing Steps]]
 A python script, [[:File:HCA_py.pdf|HCA.py]], consumes data on each startup and its location for each MSA-year. It performs the HCA and returns a file with layer and cluster numbers for each startup and MSA-year. This script builds upon:

@@ Line 14: / Line 14: @@
 == Data Processing Steps ==
-[[File:AgglomerationProcess_v2.png|center|thumb|512px|Data Processing Steps]]
+[[File:AgglomerationProcess_v2.png|center|thumb|768px|Data Processing Steps]]
 The script [[:File:Agglomeration_CBSA.sql.pdf|Agglomeration_CBSA.sql]] provides the processing steps within the PostgreSQL database. We first load the startup data, add in the longitudes and latitudes, and combine them with the [[https://en.wikipedia.org/wiki/Core-based_statistical_area CBSA]] boundaries. Startups in our data our keyed by a triple (coname, statecode, datefirstinv) as two different companies can have the same names in different states, or within the same state at two different times.

@@ Line 24: / Line 24: @@
 * [https://docs.scipy.org/doc/scipy/reference/generated/scipy.spatial.distance.squareform.html scipy.spatial.distance.squareform]
-The HCA.py script uses several functions from another python module, [[:File:schedule_py.pdf|schedule.py]], which encodes agglomeration schedules produced by the AgglomerativeClustering package. The standard encoding [[https://scikit-learn.org/stable/auto_examples/cluster/plot_agglomerative_dendrogram.html#sphx-glr-auto-examples-cluster-plot-agglomerative-dendrogram-py records the agglomeration schedule as complete paths]], indicating which clusters are merged together at each step. The layer-cluster encoding provided in schedule.py instead efficiently records the agglomeration schedule as a series of layers. It also relies on only a single read of the source data, so it is fast.
+The HCA.py script uses several functions from another python module, [[:File:schedule_py.pdf|schedule.py]], which encodes agglomeration schedules produced by the AgglomerativeClustering package. The standard encoding [https://scikit-learn.org/stable/auto_examples/cluster/plot_agglomerative_dendrogram.html#sphx-glr-auto-examples-cluster-plot-agglomerative-dendrogram-py records the agglomeration schedule as complete paths], indicating which clusters are merged together at each step. The layer-cluster encoding provided in schedule.py instead efficiently records the agglomeration schedule as a series of layers. It also relies on only a single read of the source data, so it is fast.
 The code snippets provided in [[:File:hierarchy-InsertSnippets_py.pdf|hierarchy_InsertSnippets.py]] modify the standard library provided in the [https://docs.scipy.org/doc/scipy/reference/cluster.hierarchy.html scipy.cluster.hierarchy package]. This code allows users to pre-calculate distances between locations (latitude-longitude pairs) using highly-accurate [https://postgis.net/docs/ST_Distance.html PostGIS spatial functions] in PostgreSQL. Furthermore, the code caches the results so, provided the distances fit into (high-speed) memory, it also allows users to increase the maximum feasible scale by around an order of magnitude. The code in hierarchy-InsertSnippets.py contains two snippets. The first snippet should be inserted at line 188 in the standard library. Then line 732 of the standard library should be commented out (i.e., #y = distance.pdist(y, metric)), and the second snippet should be inserted at line 734. A full copy of the amended [[:File:hierarchy_py.pdf|hierarchy.py]] is also available.

@@ Line 8: / Line 8: @@
 [https://www2.census.gov/geo/tiger/TIGER2020/CBSA/tl_2020_us_cbsa.zip Shapefiles from the 2020 U.S. Census TIGER/Line data series] provide the boundaries and names of the MSAs, and a python script (Geocode.py) in conjunction with a [https://developers.google.com/maps/documentation/distance-matrix Google Maps API], provides longitudes and latitudes for startups. We restrict the accuracy of Google’s results to four decimal places, which is [http://wiki.gis.com/wiki/index.php/Decimal_degrees approximately 10m of precision].
-All of our data assembly, and much of our data processing and analysis, is done in a [[https://www.postgresql.org/ PostgreSQL]] [https://postgis.net/ PostGIS] database. See our [[Research Computing Infrastructure]] page for more information.
+All of our data assembly, and much of our data processing and analysis, is done in a [https://www.postgresql.org/ PostgreSQL] [https://postgis.net/ PostGIS] database. See our [[Research Computing Infrastructure]] page for more information.
 However, we rely on [[https://www.python.org/ python]] scripts to retrieve addresses from Google Maps, as well as compute the [https://en.wikipedia.org/wiki/Hierarchical_clustering Hierarchical Cluster Analysis (HCA)] itself, and estimate a cubic to determine the HCA-regression method agglomeration count for an [https://en.wikipedia.org/wiki/Metropolitan_statistical_area MSA]. We also use two [https://www.stata.com/ Stata] scripts: one to compute the HCA-regressions, and another to estimate the paper's summary statistics and regression specifications. Finally, we use QGIS to construct the map images based on queries to our database. These images use a [https://maps.google.com Google Maps] base layer.

@@ Line 4: / Line 4: @@
 == Overview ==
-[[File:AgglomerationDataSourcesAndSinks_v2.png|right|thumb|512px|Data Sources and Sinks]] The dataset construction begins with startup data from Thomson Reuters’ [[VentureXpert]].  This data is retrieved using [[SDC Platinum (Wiki)|SDC Platinum]] and comprises information on startup investment amounts and dates, stage descriptions, industries, and addresses. This data is combined with data on mergers and acquisitions from the Securities Data Commission M&A and Global New Issues databases, also available through SDC Platinum, to determine startup exit events. See [[VCDB2020]].
+[[File:AgglomerationDataSourcesAndSinks_v2.png|right|thumb|512px|Data Sources and Sinks]] The dataset construction begins with startup data from Thomson Reuters’ [[VentureXpert]].  This data is retrieved using [[SDC Platinum (Wiki)|SDC Platinum]] and comprises information on startup investment amounts and dates, stage descriptions, industries, and addresses. This data is combined with data on mergers and acquisitions from the Securities Data Commission M&A and Global New Issues databases, also available through SDC Platinum, to determine startup exit events. See [[VCDB20]].
 [https://www2.census.gov/geo/tiger/TIGER2020/CBSA/tl_2020_us_cbsa.zip Shapefiles from the 2020 U.S. Census TIGER/Line data series] provide the boundaries and names of the MSAs, and a python script (Geocode.py) in conjunction with a [https://developers.google.com/maps/documentation/distance-matrix Google Maps API], provides longitudes and latitudes for startups. We restrict the accuracy of Google’s results to four decimal places, which is [http://wiki.gis.com/wiki/index.php/Decimal_degrees approximately 10m of precision].

@@ Line 6: / Line 6: @@
 [[File:AgglomerationDataSourcesAndSinks_v2.png|right|thumb|512px|Data Sources and Sinks]] The dataset construction begins with startup data from Thomson Reuters’ [[VentureXpert]].  This data is retrieved using [[SDC Platinum (Wiki)|SDC Platinum]] and comprises information on startup investment amounts and dates, stage descriptions, industries, and addresses. This data is combined with data on mergers and acquisitions from the Securities Data Commission M&A and Global New Issues databases, also available through SDC Platinum, to determine startup exit events. See [[VCDB2020]].
-[[https://www2.census.gov/geo/tiger/TIGER2020/CBSA/tl_2020_us_cbsa.zip Shapefiles from the 2020 U.S. Census TIGER/Line data series]] provide the boundaries and names of the MSAs, and a python script (Geocode.py) in conjunction with a [[https://developers.google.com/maps/documentation/distance-matrix Google Maps API]], provides longitudes and latitudes for startups. We restrict the accuracy of Google’s results to four decimal places, which is [[http://wiki.gis.com/wiki/index.php/Decimal_degrees approximately 10m of precision]].
+[https://www2.census.gov/geo/tiger/TIGER2020/CBSA/tl_2020_us_cbsa.zip Shapefiles from the 2020 U.S. Census TIGER/Line data series] provide the boundaries and names of the MSAs, and a python script (Geocode.py) in conjunction with a [https://developers.google.com/maps/documentation/distance-matrix Google Maps API], provides longitudes and latitudes for startups. We restrict the accuracy of Google’s results to four decimal places, which is [http://wiki.gis.com/wiki/index.php/Decimal_degrees approximately 10m of precision].
-All of our data assembly, and much of our data processing and analysis, is done in a [[https://www.postgresql.org/ PostgreSQL]] [[https://postgis.net/ PostGIS]] database. See our [[Research Computing Infrastructure]] page for more information.
+All of our data assembly, and much of our data processing and analysis, is done in a [[https://www.postgresql.org/ PostgreSQL]] [https://postgis.net/ PostGIS] database. See our [[Research Computing Infrastructure]] page for more information.
-However, we rely on [[https://www.python.org/ python]] scripts to retrieve addresses from Google Maps, as well as compute the [[https://en.wikipedia.org/wiki/Hierarchical_clustering Hierarchical Cluster Analysis (HCA)]] itself, and estimate a cubic to determine the HCA-regression method agglomeration count for an [[https://en.wikipedia.org/wiki/Metropolitan_statistical_area MSA]]. We also use two [[https://www.stata.com/ Stata]] scripts: one to compute the HCA-regressions, and another to estimate the paper's summary statistics and regression specifications. Finally, we use QGIS to construct the map images based on queries to our database. These images use a [[https://maps.google.com Google Maps]] base layer.
+However, we rely on [[https://www.python.org/ python]] scripts to retrieve addresses from Google Maps, as well as compute the [https://en.wikipedia.org/wiki/Hierarchical_clustering Hierarchical Cluster Analysis (HCA)] itself, and estimate a cubic to determine the HCA-regression method agglomeration count for an [https://en.wikipedia.org/wiki/Metropolitan_statistical_area MSA]. We also use two [https://www.stata.com/ Stata] scripts: one to compute the HCA-regressions, and another to estimate the paper's summary statistics and regression specifications. Finally, we use QGIS to construct the map images based on queries to our database. These images use a [https://maps.google.com Google Maps] base layer.
 == Data Processing Steps ==
@@ Line 20: / Line 20: @@
 A python script, [[:File:HCA_py.pdf|HCA.py]], consumes data on each startup and its location for each MSA-year. It performs the HCA and returns a file with layer and cluster numbers for each startup and MSA-year. This script builds upon:
-* [[https://scikit-learn.org/stable/modules/generated/sklearn.cluster.AgglomerativeClustering.html sklearn.cluster.AgglomerativeClustering]]
+* [https://scikit-learn.org/stable/modules/generated/sklearn.cluster.AgglomerativeClustering.html sklearn.cluster.AgglomerativeClustering]
-* [[https://docs.scipy.org/doc/scipy/reference/generated/scipy.cluster.hierarchy.dendrogram.html scipy.cluster.hierarchy.dendrogram]]
+* [https://docs.scipy.org/doc/scipy/reference/generated/scipy.cluster.hierarchy.dendrogram.html scipy.cluster.hierarchy.dendrogram]
-* [[https://docs.scipy.org/doc/scipy/reference/generated/scipy.spatial.distance.squareform.html scipy.spatial.distance.squareform]]
+* [https://docs.scipy.org/doc/scipy/reference/generated/scipy.spatial.distance.squareform.html scipy.spatial.distance.squareform]
 The HCA.py script uses several functions from another python module, [[:File:schedule_py.pdf|schedule.py]], which encodes agglomeration schedules produced by the AgglomerativeClustering package. The standard encoding [[https://scikit-learn.org/stable/auto_examples/cluster/plot_agglomerative_dendrogram.html#sphx-glr-auto-examples-cluster-plot-agglomerative-dendrogram-py records the agglomeration schedule as complete paths]], indicating which clusters are merged together at each step. The layer-cluster encoding provided in schedule.py instead efficiently records the agglomeration schedule as a series of layers. It also relies on only a single read of the source data, so it is fast.
-The code snippets provided in [[:File:hierarchy-InsertSnippets_py.pdf|hierarchy_InsertSnippets.py]] modify the standard library provided in the [[https://docs.scipy.org/doc/scipy/reference/cluster.hierarchy.html scipy.cluster.hierarchy package]]. This code allows users to pre-calculate distances between locations (latitude-longitude pairs) using highly-accurate [[https://postgis.net/docs/ST_Distance.html PostGIS spatial functions]] in PostgreSQL. Furthermore, the code caches the results so, provided the distances fit into (high-speed) memory, it also allows users to increase the maximum feasible scale by around an order of magnitude. The code in hierarchy-InsertSnippets.py contains two snippets. The first snippet should be inserted at line 188 in the standard library. Then line 732 of the standard library should be commented out (i.e., #y = distance.pdist(y, metric)), and the second snippet should be inserted at line 734. A full copy of the amended [[:File:hierarchy_py.pdf|hierarchy.py]] is also available.
+The code snippets provided in [[:File:hierarchy-InsertSnippets_py.pdf|hierarchy_InsertSnippets.py]] modify the standard library provided in the [https://docs.scipy.org/doc/scipy/reference/cluster.hierarchy.html scipy.cluster.hierarchy package]. This code allows users to pre-calculate distances between locations (latitude-longitude pairs) using highly-accurate [https://postgis.net/docs/ST_Distance.html PostGIS spatial functions] in PostgreSQL. Furthermore, the code caches the results so, provided the distances fit into (high-speed) memory, it also allows users to increase the maximum feasible scale by around an order of magnitude. The code in hierarchy-InsertSnippets.py contains two snippets. The first snippet should be inserted at line 188 in the standard library. Then line 732 of the standard library should be commented out (i.e., #y = distance.pdist(y, metric)), and the second snippet should be inserted at line 734. A full copy of the amended [[:File:hierarchy_py.pdf|hierarchy.py]] is also available.
-The results of the HCA.py script are loaded back to the database, which produces a dataset for analysis in Stata. The script [[:File:AgglomerationMaxR2.do.pdf|AgglomerationMaxR2.do]] loads this dataset and performs the HCA-Regressions. The results are passed to a python script, [[:File:Cubic_py.pdf|cubic.py]], which selects the appropriate number of agglomerations for each MSA. The results from both AgglomerationMaxR2.do and Cubic.py are then loaded back into the database, which produces a final dataset and set of tables providing data for the maps. The analysis of the final dataset uses the Stata script [[:File:AgglomerationAnalysis.do.pdf|AgglomerationAnalysis.do]], and the maps are made using custom queries in [[http://www.qgis.org QGIS]].
+The results of the HCA.py script are loaded back to the database, which produces a dataset for analysis in Stata. The script [[:File:AgglomerationMaxR2.do.pdf|AgglomerationMaxR2.do]] loads this dataset and performs the HCA-Regressions. The results are passed to a python script, [[:File:Cubic_py.pdf|cubic.py]], which selects the appropriate number of agglomerations for each MSA. The results from both AgglomerationMaxR2.do and Cubic.py are then loaded back into the database, which produces a final dataset and set of tables providing data for the maps. The analysis of the final dataset uses the Stata script [[:File:AgglomerationAnalysis.do.pdf|AgglomerationAnalysis.do]], and the maps are made using custom queries in [http://www.qgis.org QGIS].
 == Code ==
@@ Line 46: / Line 46: @@
 === Cubic.py ===
-[[:File:Cubic_py.pdf|Cubic.py]] solves the cubic estimation to select an agglomeration code for each MSA. It uses [[https://scikit-learn.org/stable/modules/linear_model.html sklearn.linear_model]].
+[[:File:Cubic_py.pdf|Cubic.py]] solves the cubic estimation to select an agglomeration code for each MSA. It uses [https://scikit-learn.org/stable/modules/linear_model.html sklearn.linear_model].
 <pdf>File:Cubic_py.pdf</pdf>
@@ Line 58: / Line 58: @@
 === Hierarchy.py ===
-[[:File:Hierarchy_py.pdf|Hierarchy.py]] is my modified version of Damian Eads' hierarchy.py, which is available as a part of the [[https://docs.scipy.org/doc/scipy/reference/cluster.hierarchy.html scipy.cluster package]].
+[[:File:Hierarchy_py.pdf|Hierarchy.py]] is my modified version of Damian Eads' hierarchy.py, which is available as a part of the [https://docs.scipy.org/doc/scipy/reference/cluster.hierarchy.html scipy.cluster package].
 <pdf>File:Hierarchy_py.pdf</pdf>
 === Hierarchy-InsertSnippets.py ===
-[[:File:Hierarchy-InsertSnippets_py.pdf|Hierarchy-InsertSnippets.py]] contains just the snippets needed to modify [[https://docs.scipy.org/doc/scipy/reference/cluster.hierarchy.html hierarchy.py]]
+[[:File:Hierarchy-InsertSnippets_py.pdf|Hierarchy-InsertSnippets.py]] contains just the snippets needed to modify [https://docs.scipy.org/doc/scipy/reference/cluster.hierarchy.html hierarchy.py]
 <pdf>File:Hierarchy-InsertSnippets_py.pdf</pdf>
 === Schedule.py===
-[[:File:Schedule_py.pdf|Schedule.py]] provides various schedule manipulation methods and the layer-cluster encoding scheme that works with https://docs.scipy.org/doc/scipy/reference/cluster.hierarchy.html scipy.cluster.hierarchy]].
+[[:File:Schedule_py.pdf|Schedule.py]] provides various schedule manipulation methods and the layer-cluster encoding scheme that works with [https://docs.scipy.org/doc/scipy/reference/cluster.hierarchy.html scipy.cluster.hierarchy].
 <pdf>File:Schedule_py.pdf</pdf>

@@ Line 33: / Line 33: @@
 === Agglomeration_CBSA.sql ===
 <pdf>File:Agglomeration_CBSA.sql.pdf</pdf>
-=== File:AgglomerationAnalysis.do ===
+=== AgglomerationAnalysis.do ===
 <pdf>File:AgglomerationAnalysis.do.pdf</pdf>
 === Agglomeration_CBSA.sql===
 <pdf>File:AgglomerationMaxR2.do.pdf</pdf>
 === Cubic.py ===
 <pdf>File:Cubic_py.pdf</pdf>
 === Geocode.py ===
 <pdf>File:Geocode_py.pdf</pdf>
 === HCA.py ===
 <pdf>File:HCA_py.pdf</pdf>
 === Hierarchy.py ===
 <pdf>File:Hierarchy_py.pdf</pdf>
 === Hierarchy-InsertSnippets.py ===
 <pdf>File:Hierarchy-InsertSnippets_py.pdf</pdf>
 === Schedule.py===
 <pdf>File:Schedule_py.pdf</pdf>