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)