def build_acs_crosswalk(self):
from ambry.geo.util import find_geo_containment
def generate_geometries():
blocks = self.partitions.find(table='sws_boundaries')
lr = self.init_log_rate(3000)
# Note, ogc_fid is the primary key. The id column is created by the shapefile.
for i,block in enumerate(blocks.query("SELECT AsText(geometry) AS wkt, id FROM sws_boundaries")):
lr('Load rtree')
if self.run_args.test and i > 200:
break
yield block['id'],block['id'], block['wkt']
def generate_blockgroups():
"""Generate centroids of the 2012 ACS blockgroups"""
block_groups = self.library.dep('bg2012').partition
for row in block_groups.query("""SELECT
gvid,
X(Transform(Centroid(geometry), 4326)) AS lon,
Y(Transform(Centroid(geometry), 4326)) as lat,
MbrMinX(geometry) AS x_min,
MbrMinY(geometry) AS y_min,
MbrMaxX(geometry) AS x_max,
MbrMaxY(geometry) AS y_max
FROM blockgroups
"""):
if row['lon'] and row['lat']:
yield (row['x_min'], row['y_min'], row['x_max'], row['y_max']), row['gvid']
def mark_contains():
p = self.partitions.find_or_new(table='acs_cross')
p.clean()
with p.inserter() as ins:
while True:
(p,point_obj,geometry, poly_obj) = yield # Get a value back from find_geo_containment
d = {
'gvid': point_obj,
'sws_boundaries_id': poly_obj
}
ins.insert(d)
self.log("Linking ACS tracts to boundaries")
find_geo_containment(generate_geometries(), generate_blockgroups(), mark_contains(), method = 'intersects')
def build_block_cross(self):
"""Build the bus_block_cross crosswalk file to assign businessed to blocks. """
from ambry.geo.util import find_geo_containment
def generate_geometries():
"""The Containing geometries are the neighborhoods. """
for row in self.partitions.find(table = 'neighborhoods').query(
"SELECT AsText(geometry) as wkt, objectid_1 as id FROM neighborhoods"):
yield row['id'], row['id'], row['wkt']
def generate_points():
"""The points we are going to find the containment of are the centroids of the census blocks"""
blocks = self.library.dep('blocks').partition
lr = self.init_log_rate(3000)
# Note, ogc_fid is the primary key. The id column is created by the shapefile.
for i,row in enumerate(blocks.query(
"SELECT X(Centroid(geometry)) AS lon, Y(Centroid(geometry)) AS lat, geoid FROM blocks")):
lr('Load rtree')
if self.run_args.test and i > 200:
break
yield (row['lon'], row['lat']), row['geoid']
def mark_contains():
p = self.partitions.find_or_new(table='nhood_block_cross')
p.clean()
lr = self.init_log_rate(3000)
with p.inserter() as ins:
while True:
(p,point_obj,geometry, poly_obj) = yield # Get a value back from find_geo_containment
ins.insert(dict(neighborhoods_id = poly_obj, geoid = point_obj ))
lr('Marking point containment')
find_geo_containment(generate_geometries(), generate_points(), mark_contains())
def build_block_cross(self):
"""Build the bus_block_cross crosswalk file to assign businesses to blocks. """
from ambry.geo.util import find_geo_containment
lr = self.init_log_rate(3000)
def generate_geometries():
blocks = self.library.dep('blocks').partition
# Note, ogc_fid is the primary key. The id column is created by the shapefile.
for i,block in enumerate(blocks.query("SELECT AsText(geometry) AS wkt, geoid FROM blocks")):
lr('Load rtree')
yield i, block['geoid'] , block['wkt']
def generate_points():
p = self.partitions.find(table = 'dstk_addresses')
for row in p.rows:
if row['lon'] and row['lat']:
yield (row['lon'], row['lat']), row['businesses_id']
def mark_contains():
p = self.partitions.find_or_new(table='bus_block_cross')
p.clean()
with p.inserter() as ins:
while True:
(p,point_obj,geometry, poly_obj) = yield # Get a value back from find_geo_containment
ins.insert(
dict(businesses_id = point_obj,
block_geoid = poly_obj, # New name
geoid = poly_obj # Old name, for development
))
lr('Marking point containment')
find_geo_containment(generate_geometries(), generate_points(), mark_contains())
