class City(object):
az_columns = (AdminZone.id, AdminZone.name, AdminZone.code_department,
func.ST_X(func.ST_Centroid(AdminZone.geometry)),
func.ST_Y(func.ST_Centroid(AdminZone.geometry)))
@staticmethod
def format_city_res(result):
return {
'id': result[0],
'name': result[1],
'code_department': result[2],
'lng': result[3],
'lat': result[4]
}
def __init__(self, request):
self.request = request
def get(self):
id = self.request.matchdict['id']
return {
'results':
self.format_city_res(
DBSession.query(*self.az_columns).filter(
AdminZone.id == id).first())
}
def collection_get(self):
ids = self.request.params['ids'].split(',')
return {
'results': [
self.format_city_res(res) for res in DBSession.query(
*self.az_columns).filter(AdminZone.id.in_(ids)).all()
]
}
def alchemy_expression(self):
dt = self.product
grid_spec = self.product.grid_spec
doc = _jsonb_doc_expression(dt.metadata_type)
projection_offset = _projection_doc_offset(dt.metadata_type)
# Calculate tile refs
geo_ref_points_offset = projection_offset + ['geo_ref_points']
center_point = func.ST_Centroid(
func.ST_Collect(
_gis_point(doc, geo_ref_points_offset + ['ll']),
_gis_point(doc, geo_ref_points_offset + ['ur']),
))
# todo: look at grid_spec crs. Use it for defaults, conversion.
size_x, size_y = (grid_spec.tile_size or (1000.0, 1000.0))
origin_x, origin_y = grid_spec.origin
return func.concat(
func.floor(
(func.ST_X(center_point) - origin_x) / size_x).cast(String),
'_',
func.floor(
(func.ST_Y(center_point) - origin_y) / size_y).cast(String),
)
def update_location_names(self, overwrite: bool = False):
"""Updates location names in the Sparrow database"""
click.echo("Updating location names")
db = self.app.database
s = db.model.sample
# Get unnamed locations
q = (db.session.query(s).with_entities(
s, func.ST_AsGeoJSON(func.ST_Centroid(
s.location))).filter(s.location != None))
if not overwrite:
q = q.filter(s.location_name == None)
i = 0
for (s, json_string) in q:
# Get point coordinate
coord = loads(json_string)["coordinates"]
name = get_location_name(coord)
s.location_name = name
s.location_name_autoset = True
db.session.add(s)
db.session.commit()
print(name)
i += 1
print(f"{i} locations updated")
def get_clustered_locations(location_column,
threshold_radius=1000, filter=None):
"""
SELECT ST_Centroid(
(ST_Dump(
ST_Union(
ST_Buffer(
takeoff_location_wkt::geography, 1000
)::geometry
)
)
).geom) FROM flights WHERE pilot_id=31;
"""
# Cast the takeoff_location_wkt column to Geography
geography = cast(location_column, Geography)
# Add a metric buffer zone around the locations
buffer = cast(geography.ST_Buffer(threshold_radius), Geometry)
# Join the locations into one MultiPolygon
union = buffer.ST_Union()
# Split the MultiPolygon into separate polygons
dump = union.ST_Dump().geom
# Calculate center points of each polygon
locations = func.ST_Centroid(dump)
query = db.session.query(locations.label('location'))
if filter is not None:
query = query.filter(filter)
return [Location.from_wkb(row.location) for row in query]
def generate_grids(config, area=None):
bounding_box = WKTElement(config['BOUNDING_BOX'], srid=4326)
grid_obj = config['GRID_OBJ']
resolution = config['RESOLUTION']
epsg = config['EPSG']
try:
grids = session.query(func.ST_Dump(
func.makegrid_2d(bounding_box, resolution, resolution)).geom.label('geom') # self-defined function in Psql
).subquery()
# using the boundary to crop the area
# if config['AREA'] == 'los_angeles':
# grids = session.query(grids.c.geom) \
# .filter(func.ST_Intersects(LosAngelesCountyBoundary.wkb_geometry, grids.c.geom)).subquery()
results = session.query(
func.row_number().over().label('gid'),
func.ST_Centroid(grids.c.geom).label('centroid'),
func.ST_X(func.ST_Centroid(grids.c.geom)).label('lon'),
func.ST_Y(func.ST_Centroid(grids.c.geom)).label('lat'),
grids.c.geom,
func.ST_X(func.ST_Transform(func.ST_Centroid(grids.c.geom), epsg)).label('lon_proj'),
func.ST_Y(func.ST_Transform(func.ST_Centroid(grids.c.geom), epsg)).label('lat_proj')).all()
obj_results = []
for res in results:
obj_results.append(grid_obj(gid=res[0], centroid=res[1], lon=res[2], lat=res[3],
geom=res[4], lon_proj=res[5], lat_proj=res[6]))
# session.add_all(obj_results)
# session.commit()
return
except Exception as e:
print(e)
exit(-1)
def test_query_to_geopandas_wo_geom(self):
"""
Test converting a sqlalchemy query of points to geopandas df where the geom column is not == 'geom'
"""
# Query the centroids of all the raster tiles and use that as the geometry column in geopandas
qry = self.session.query(
func.ST_Centroid(func.ST_Envelope(ImageData.raster)))
df = query_to_geopandas(qry, self.engine, geom_col='ST_Centroid_1')
# Confirm the type
assert isinstance(df, gpd.GeoDataFrame)
# Confirm value count
assert df['ST_Centroid_1'].count() == 1
def find_by_filter_for_map(cls, sid_cds, sgg_cds, rsdnc_clsftn_cds,
fmly_num_cds, room_num_cds, st_year, ed_yer):
# 서브 쿼리
geojson = db.session.query(func.ST_AsGeoJSON(func.ST_Centroid(LawSidArea.geom)).label('geojson')). \
filter(LawSidArea.sid_cd == cls.sid_cd).limit(1).label('geojson')
results = db.session.query(geojson,
func.sum(cls.hshold_num).label('hshold_sum')). \
filter(or_(cls.sid_cd.in_(sid_cds),
cls.sgg_cd.in_(sgg_cds))). \
filter(cls.rsdnc_clsftn_cd.in_(rsdnc_clsftn_cds)). \
filter(cls.fmly_num_cd.in_(fmly_num_cds)). \
filter(cls.room_num_cd.in_(room_num_cds)). \
filter(and_(cls.srvy_year >= st_year,
cls.srvy_year <= ed_yer)). \
group_by(cls.sid_cd)
return results
def find_by_filter_for_map(cls, out_sid_cds, out_sgg_cds, out_emd_cds, in_sid_cd, in_sgg_cd, in_emd_cd,
mv_reasn_cds, aplcnt_ages, aplcnt_sex_cds, fmly_nums, st_yyyymm, ed_yyyymm):
# 서브 쿼리
geojson = db.session.query(func.ST_AsGeoJSON(func.ST_Centroid(LawSidArea.geom)).label('geojson')). \
filter(LawSidArea.sid_cd == cls.in_sid_cd).limit(1).label('geojson')
results = db.session.query(geojson,
func.count(cls.fmly_num).label('hshold_cnt'),
func.sum(cls.fmly_num).label('fmly_sum')). \
filter(or_(cls.out_sid_cd.in_(out_sid_cds),
cls.out_sgg_cd.in_(out_sgg_cds),
cls.out_emd_cd.in_(out_emd_cds))). \
filter(cls.aplcnt_age.in_(aplcnt_ages)). \
filter(cls.aplcnt_sex_cd.in_(aplcnt_sex_cds)). \
filter(cls.mv_reasn_cd.in_(mv_reasn_cds)). \
filter(cls.fmly_num.in_(fmly_nums)). \
filter(or_(cls.in_sid_cd == in_sid_cd,
cls.in_sgg_cd == in_sgg_cd,
cls.in_emd_cd == in_emd_cd)). \
filter(and_(cls.in_yyyymm >= st_yyyymm,
cls.in_yyyymm <= ed_yyyymm)). \
group_by(cls.in_sid_cd)
return results
def get_page_data(id, model, area_type):
"""
Returns a Response object for the given user input
"""
# parse args from http request
classifier = request.args.get("classifier")
date = request.args.get("date")
date = datetime.strptime(date, "%m/%d/%Y %H:%M %p")
hour = int(date.strftime("%H"))
hour = (
db.session.query(CrimeHourClass.classification)
.filter(between(hour, CrimeHourClass.lower_bound, CrimeHourClass.upper_bound))
.scalar()
)
month = int(date.strftime("%m"))
day_of_week = int(date.strftime("%w"))
area_type = request.args.get("area-type")
uri = request.args.get("district" if area_type == "d" else "neighborhood")
area = uri.split("/")
area = int(area[len(area) - 1])
# get all general object lists
districts, neighborhoods, classifiers = get_list_objs()
# get the centroid of the geometry for the selected area
g = db.session.query(func.ST_Centroid(model.geom)).filter_by(id=id).scalar()
if g is not None:
# retrieve model from database
crime_model = (
db.session.query(CrimeModel)
.filter_by(area_type=area_type.upper(), classifier=classifier)
.first()
)
# bytes -> python object
crime_model = pickle.loads(crime_model.model)
# do a prediction for this area and datetime using the model
prediction = crime_model.predict([[area, month, day_of_week, hour]])[0]
# parse geojson for map
geojson = (
db.session.query(func.ST_AsGeoJSON(model.geom).label("json"))
.filter_by(id=id)
.scalar()
)
geojson = json.loads(geojson)
# find incidents for map
points = get_incidents(
db.session.query(model.geom).filter_by(id=id).scalar(), prediction
)
# get centroid points for map
g = db.session.query(func.ST_X(g).label("x"), func.ST_Y(g).label("y")).first()
x, y = g.x, g.y
# render response
return render_template(
"index.html",
selected=uri,
area_type=area_type,
prediction=prediction.lower().capitalize(),
calendar=date.strftime("%m/%d/%Y %H:%M %p"),
x=x,
y=y,
geojson=geojson,
districts=districts,
neighborhoods=neighborhoods,
classifiers=classifiers,
selected_classifier=classifier,
points=points,
)
else:
# general 404 response
return make_response(f"<h2>ID {id} not found.", 404)
