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())