7,649
edits
Changes
Jump to navigation
Jump to search
Urban Start-up Agglomeration and Venture Capital Investment (view source)
Revision as of 16:20, 19 September 2019
, 16:20, 19 September 2019→Density Maps
It's useful to lay each year's chosen hulls on top of each other over time (from say 1995 to present, or using the layer with the max number of hulls if the year never achieves the chosen hull count). However, to do this we should expand the hulls, because all hulls have points at their corners and most hulls have points only at their corners. I propose using ST_Expand to increase X and Y distances separately. The method would be to take ((Centroid's X-ST_XMin)+(ST_Xmax-Centroid's X)/2) as the X expansion distance and likewise for Y.
Correction - we definately don't want ST_Expand as it creates a bounding box. We want ST_Buffer, but how big a buffer? Half of the maximum width is easy. Also ST_Length(ST_LongestLine( (SELECT geom FROM mylayer WHERE gid=1), transparency doesn't stack within a layer... See https://gis.stackexchange.com/questions/91537/how-to-vary-the-transparency-of-symbols-within-a-single-layer-in-arcmap (SELECT geom FROM mylayer WHERE gid=1))
Also, transparency doesn't stack within a layer... See https://gis.stackexchange.com/questions/91537/how-to-vary-the-transparency-of-symbols-within-a-single-layer-in-arcmap Rather than half of the maximum width, we could use the average distance from a corner to another corner (i.e., pretty much between locations) divided by two. Use ST_DumpPoints(geometry geom) to recover the corners of the convex hulls. We should also put some other places on the map (long,lat):
*Mercury Fund [https://www.google.com/maps/place/Mercury+Fund/@29.7331018,-95.4306675,17z/data=!3m1!4b1!4m5!3m4!1s0x8640c101591b2263:0xbed99e4e5ffd9521!8m2!3d29.7331018!4d-95.4284788] at (-95.4306675,29.7331018)
*The Galleria [https://www.google.com/maps/place/The+Galleria/@29.7411474,-95.4627705,14.83z/data=!4m12!1m6!3m5!1s0x8640c101591b2263:0xbed99e4e5ffd9521!2sMercury+Fund!8m2!3d29.7331018!4d-95.4284788!3m4!1s0x8640c16eeef3bdd1:0x7e03fa20d63c5caf!8m2!3d29.7392047!4d-95.4644644] at (-95.4627705,29.7411474).
*The center of Downtown [https://www.google.com/maps/place/Downtown+Houston,+Houston,+TX/@29.7575263,-95.3755815,14z/data=!3m1!4b1!4m5!3m4!1s0x8640bf246c71b97b:0xdcf0529c0565fc84!8m2!3d29.7559698!4d-95.3573194] at (-95.3755815,29.7575263)
*Rice University [https://www.google.com/maps/place/Rice+University/@29.7173941,-95.4040199,17z/data=!3m1!4b1!4m5!3m4!1s0x8640c064b7f18e1f:0xe2cd9cf065c43eb5!8m2!3d29.7173941!4d-95.4018312] at (-95.4040199,29.7173941)
*The Energy Corridor [https://www.google.com/maps/place/Energy+Corridor,+Houston,+TX/@29.8698068,-95.7131468,11.25z/data=!4m5!3m4!1s0x8640deca82ae11fd:0x347965110f3c961!8m2!3d29.7795048!4d-95.6289338] at (-95.7131468,29.8698068)
*Houston Community College - Spring Branch Campus [https://www.google.com/maps/place/Houston+Community+College+-+Spring+Branch+Campus/@29.7841191,-95.5631846,17z/data=!4m13!1m7!3m6!1s0x0:0x0!2zMjnCsDQ3JzA5LjYiTiA5NcKwMzMnNTAuNCJX!3b1!8m2!3d29.786!4d-95.564!3m4!1s0x8640c4b538cbade9:0x2919686cd5d7fac!8m2!3d29.78695!4d-95.5614142] at (-95.5631846,29.7841191)
*Westchase Neighborhood [https://www.google.com/maps/place/Westchase,+Houston,+TX/@29.72609,-95.5832518,14z/data=!3m1!4b1!4m5!3m4!1s0x8640dcd3d109acff:0xb304de945995b231!8m2!3d29.7277555!4d-95.5716617] at (-95.5832518,29.72609)
Visually, it is easy to layer the years, using opacity to build up an effect over time. In the data, it is more difficult. Each year could have thickness one and then we could count the number using ST_intersects while creating the new hulls using ST_Intersection (returns null when no interection). If there are more than one intersections with the highest intersects count, then we could take the largest one as the ultimate one. The centroid of the ultimate intersection would be the heart of a city's startup scene.
I also added the roads from the Tiger Line Shapefile for Harris County[https://catalog.data.gov/dataset/tiger-line-shapefile-2013-county-harris-county-tx-all-roads-county-based-shapefile]:
tl_2013_48201_roads
One problem with this method is that there are partial intersections. We could use ST_Difference to return the part that doesn't intersect... a bigger problem is that we are restricted to pairs of geometries. Using a cross product we could test all rows against all other rows. But then we'd need to aggregate the intersections... One method is to use a recursive CTE [https://gis.stackexchange.com/questions/269875/aggregate-version-of-st-intersection]. ST_Union is truly an aggregate function but not what we want in this context.
