def build_geo_cross(self):
from ambry.geo.util import find_geo_containment, find_containment
def gen_bound():
boundaries = self.partitions.find(table='places')
# Note, ogc_fid is the primary key. The id column is created by the shapefile.
for i,boundary in enumerate(boundaries.query("SELECT id, AsText(geometry) AS wkt FROM places")):
lr('Load rtree')
yield i, boundary['wkt'] , boundary['id']
def gen_points(geography):
# HACK! This isn't quite right. We should be using the census-created interior point,
# which is guaranteed to be inside the shape. Centroids can be outside the shape, although that rarely
# happens for blockgroups.
q = """
SELECT *,
AsText(Transform(geometry, 4326)) AS geometry,
X(Transform(Centroid(geometry), 4326)) AS lon,
Y(Transform(Centroid(geometry), 4326)) as lat,
Area(geometry) as area
FROM {}""".format(geography)
for row in self.library.dep(geography).partition.query(q):
if row['lon'] and row['lat']:
yield (row['lon'], row['lat']), row['gvid']
lr = self.init_log_rate(200)
p = self.partitions.find_or_new(table='places_blockgroups')
p.clean()
with p.inserter() as ins:
for point, point_o, cntr_geo, cntr_o in find_containment(gen_bound(),gen_points('blockgroups')):
ins.insert(dict(places_id = cntr_o, gvid = point_o))
lr('Assigning to blockgroups')
p = self.partitions.find_or_new(table='places_tracts')
p.clean()
with p.inserter() as ins:
for point, point_o, cntr_geo, cntr_o in find_containment(gen_bound(),gen_points('tracts')):
ins.insert(dict(places_id = cntr_o, gvid = point_o))
lr('Assigning to tracts')
def containment(self):
from ambry.geo.util import find_containment
lr = self.init_log_rate(3000)
def gen_bound():
places = self.library.dep('places').partition
lr = self.init_log_rate(3000)
# Note, ogc_fid is the primary key. The id column is created by the shapefile.
for i,boundary in enumerate(places.query(
"SELECT AsText(geometry) AS wkt, code, scode, name FROM places WHERE type = 'community' ")):
lr('Load rtree')
yield i, boundary['wkt'] , ( boundary['scode'], boundary['code'] , boundary['name'] )
def gen_points():
parcels = self.library.dep('parcels').partition
lr = self.init_log_rate(3000)
for i,parcel in enumerate(parcels.query(
"""SELECT X(Transform(Centroid(geometry), 4326)) AS lon,
Y(Transform(Centroid(geometry), 4326)) AS lat, apn FROM parcels
WHERE situs_juri = 'SD' """)):
lr('Generate points')
yield (parcel['lon'], parcel['lat']), (parcel['apn'], parcel['lat'], parcel['lon'])
import csv
lr = self.init_log_rate(300)
with open('parcel_communities.csv', 'w') as f:
w = csv.writer(f)
w.writerow(['apn', 'lat','lon', 'source_code', 'community_code','community_name'])
for point, point_o, cntr_geo, cntr_o in find_containment(gen_bound(),gen_points()):
lr('Write row')
w.writerow([point_o[0], point_o[1], point_o[2] ,cntr_o[0], cntr_o[1], cntr_o[2]])
def build_block_cross(self):
"""Build the facilities_blockgroups crosswalk file to assign facilities to blockgroups. """
from ambry.geo.util import find_geo_containment, find_containment
from geoid import civick
lr = self.init_log_rate(3000)
def gen_bound():
boundaries = self.library.dep('blockgroups').partition
# Note, ogc_fid is the primary key. The id column is created by the shapefile.
for i, boundary in enumerate(
boundaries.query(
"SELECT AsText(geometry) AS wkt, gvid FROM blockgroups"
)):
lr('Load rtree')
yield i, boundary['wkt'], boundary['gvid']
def gen_points():
for row in self.partitions.find(table='facilities_addresses').rows:
if row['longitude'] and row['latitude']:
yield (row['longitude'],
row['latitude']), row['facilities_id']
p = self.partitions.find_or_new(table='facilities_geoids')
p.clean()
with p.inserter() as ins:
for point, point_o, cntr_geo, cntr_o in find_containment(
gen_bound(), gen_points()):
blockgroup_gvid = civick.Blockgroup.parse(cntr_o)
tract_gvid = blockgroup_gvid.convert(civick.Tract)
county_gvid = blockgroup_gvid.convert(civick.County)
ins.insert(
dict(facilities_id=point_o,
blockgroup_gvid=str(blockgroup_gvid),
tract_gvid=str(tract_gvid),
county_gvid=str(county_gvid)))
lr('Marking point containment')
def build_agencies(self):
"""Build the facilities_blockgroups crosswalk file to assign facilities to blockgroups. """
from ambry.geo.util import find_geo_containment, find_containment
lr = self.init_log_rate(3000)
def gen_bound():
boundaries = self.library.dep('blockgroups').partition
# Note, ogc_fid is the primary key. The id column is created by the shapefile.
for i,boundary in enumerate(boundaries.query(
"SELECT AsText(geometry) AS wkt, gvid FROM blockgroups")):
lr('Load rtree')
yield i, boundary['wkt'] , boundary['gvid']
def gen_points():
for o in self.generate_agencies():
if o['geocoded']:
yield (o['geocoded']['longitude'], o['geocoded']['latitude']), o
p = self.partitions.find_or_new(table='locations')
p.clean()
with p.inserter() as ins:
for point, point_o, cntr_geo, cntr_o in find_containment(gen_bound(),gen_points()):
row = point_o['row']
row['gvid'] = cntr_o
ins.insert(row)
lr('Marking point containment')