def test_comparator_case_insensitivity(self): lake_id = self._create_one_lake() s = select([func.ST_Buffer(Lake.__table__.c.geom, 2)]) r1 = session.execute(s).scalar() assert isinstance(r1, WKBElement) lake = session.query(Lake).get(lake_id) assert isinstance(lake.geom, WKBElement) r2 = session.execute(lake.geom.ST_Buffer(2)).scalar() assert isinstance(r2, WKBElement) r3 = session.execute(lake.geom.st_buffer(2)).scalar() assert isinstance(r3, WKBElement) r4 = session.execute(lake.geom.St_BuFfEr(2)).scalar() assert isinstance(r4, WKBElement) r5 = session.query(Lake.geom.ST_Buffer(2)).scalar() assert isinstance(r5, WKBElement) r6 = session.query(Lake.geom.st_buffer(2)).scalar() assert isinstance(r6, WKBElement) r7 = session.query(Lake.geom.St_BuFfEr(2)).scalar() assert isinstance(r7, WKBElement) assert (r1.data == r2.data == r3.data == r4.data == r5.data == r6.data == r7.data)
def get_surrounding_area(type_code, area_code, buffer=10000): """ :param type_code: :param area_code: :return: """ area = ( DB.session.query(LAreas.id_area, LAreas.geom).filter( LAreas.area_code == area_code, BibAreasTypes.type_code == type_code ) ).first() selected_type_codes = DB.session.query(LAreasTypeSelection.id_type).all() select = [] for s in selected_type_codes: select.append(s[0]) q_gen_stats = ( DB.session.query( BibAreasTypes.type_name, BibAreasTypes.type_desc, BibAreasTypes.type_code, MVTerritoryGeneralStats.area_code, MVTerritoryGeneralStats.area_name, MVTerritoryGeneralStats.id_area, MVTerritoryGeneralStats.count_dataset, MVTerritoryGeneralStats.count_observer, MVTerritoryGeneralStats.count_date, MVTerritoryGeneralStats.count_taxa, MVTerritoryGeneralStats.last_obs, MVTerritoryGeneralStats.count_threatened, MVTerritoryGeneralStats.count_occtax, ) .join( BibAreasTypes, BibAreasTypes.type_code == MVTerritoryGeneralStats.type_code ) .filter( and_( MVTerritoryGeneralStats.geom_local.ST_Intersects( func.ST_Buffer(area.geom, buffer) ), BibAreasTypes.id_type.in_(select), MVTerritoryGeneralStats.id_area != area.id_area, ) ) ) results = q_gen_stats.all() data = [] for r in results: data.append(r._asdict()) return jsonify(data)
def get_grid_datas(id_area, buffer, grid): """Get one enabled municipality by insee code --- tags: - Reférentiel géo parameters: - name: insee in: path type: string required: true default: none properties: area_name: type: string description: Municipality name area_code: type: string description: Municipality insee code geometry: type: geometry responses: 200: description: A municipality """ try: qarea = LAreas.query.filter(LAreas.id_area == id_area) area = qarea.one() qgrid = MVTerritoryGeneralStats.query.filter( MVTerritoryGeneralStats.type_code == grid ).filter( func.ST_Intersects( MVTerritoryGeneralStats.geom_local, func.ST_Buffer(area.geom, buffer), ) ) datas = qgrid.all() features = [] for d in datas: features.append(d.as_geofeature("geom_4326", "id_area")) return FeatureCollection(features) except Exception as e: current_app.logger.error("<get_grid_datas> ERROR:", e) return {"Error": str(e)}, 400
def test_ST_Buffer(self): lake_id = self._create_one_lake() s = select([func.ST_Buffer(Lake.__table__.c.geom, 2, type_=geometry_type)]) r1 = session.execute(s).scalar() assert isinstance(r1, WKBElement) lake = session.query(Lake).get(lake_id) r2 = session.execute(lake.geom.ST_Buffer(2, type_=geometry_type)).scalar() assert isinstance(r2, WKBElement) r3 = session.query(Lake.geom.ST_Buffer(2, type_=geometry_type)).scalar() assert isinstance(r3, WKBElement) assert r1.data == r2.data == r3.data r4 = session.query(Lake).filter( func.ST_Within(WKTElement('POINT(0 0)', srid=4326), Lake.geom.ST_Buffer(2, type_=geometry_type))).one() assert isinstance(r4, Lake) assert r4.id == lake_id
def report_geojson(request): division_code = request.matchdict.get('divisioncode') try: resolution = float(request.params.get('resolution')) except: raise HTTPBadRequest(detail='invalid value for parameter "resolution"') hazard_type = request.matchdict.get('hazardtype', None) ''' Here we want to address the "small islands" issue. We first check how big the polygons are compared to map resolution. ''' area = DBSession.query(func.ST_Area( func.ST_Transform(AdministrativeDivision.geom, 3857))) \ .filter(AdministrativeDivision.code == division_code) \ .scalar() if area < math.pow(resolution, 2) * 1000: # Simplify a bit first to prevent buffer to be too expensive # Here '1000000' is an empiric value. It works for Maldives without # generating empty geometries first_simplify = func.ST_Simplify( func.ST_Transform(AdministrativeDivision.geom, 3857), area / 1000000) # make the polygons a bit bigger geom = func.ST_Buffer(first_simplify, resolution * 2) else: geom = func.ST_Transform(AdministrativeDivision.geom, 3857) simplify = func.ST_Simplify(geom, resolution / 2) _filter = or_(AdministrativeDivision.code == division_code, AdministrativeDivision.parent_code == division_code) if hazard_type is not None: divisions = DBSession.query(AdministrativeDivision) \ .add_columns(simplify, HazardLevel.mnemonic, HazardLevel.title) \ .outerjoin(AdministrativeDivision.hazardcategories) \ .join(HazardCategory) \ .join(HazardType)\ .join(HazardLevel) \ .filter(and_(_filter, HazardType.mnemonic == hazard_type)) else: divisions = DBSession.query(AdministrativeDivision) \ .add_columns(simplify, literal_column("'None'"), literal_column("'blah'")) \ .filter(_filter) return [{ 'type': 'Feature', 'geometry': to_shape(geom_simplified), 'properties': { 'name': division.name, 'code': division.code, 'url': request.route_url('report_overview', division=division), 'hazardLevelMnemonic': hazardlevel_mnemonic, 'hazardLevelTitle': hazardlevel_title } } for division, geom_simplified, hazardlevel_mnemonic, hazardlevel_title in divisions]