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]
