def query_generation_assets():
"""Look for on and off grid generation assets"""
res = db_session.query(
GenerationAssets.name,
func.ST_AsText(
func.ST_Transform(
func.ST_GeomFromWKB(GenerationAssets.geom, srid=3857),
4326)).label("geom"),
GenerationAssets.capacity_kw,
GenerationAssets.asset_type,
GenerationAssets.technology_type,
)
features = []
for r in res:
if r.geom is not None:
gjson = Feature(
geometry=Point(loadswkt(r.geom).coords[0]),
properties={
"name": r.name,
"capacity_kw": r.capacity_kw,
"technology_type": r.technology_type,
"asset_type": r.asset_type,
},
)
features.append(gjson)
return FeatureCollection(features)
def generate_for_neighbour_polygons():
polygons = Polygon.query.all()
for polygon in polygons:
print(polygon.id)
neighbours = db.session.query(Polygon).filter(
(Polygon.id != polygon.id)
& (func.ST_Intersects(Polygon.geo, polygon.geo))).all()
neighbours_ids = [
x.id for x in neighbours if 'POINT' not in db.session.scalar(
functions.ST_AsText(
functions.ST_Intersection(polygon.geo, x.geo)))
]
polygon_cross_locations = Location.query.filter(
(Location.polygon_id == polygon.id)
& (Location.is_cross_location == True)).all()
neighbours_cross_locations = Location.query.filter(
(Location.polygon_id.in_(neighbours_ids))
& (Location.is_cross_location == True)).all()
if len(polygon_cross_locations) + len(neighbours_cross_locations) == 1:
continue
size = len(polygon_cross_locations) + len(neighbours_cross_locations)
osrm_table = _get_osrm_table_response_s2d(polygon_cross_locations,
neighbours_cross_locations)
if not osrm_table:
return False
for i in range(len(polygon_cross_locations)):
nearest_idx = _find_nearest(i, osrm_table)
row = MatrixRow.query.filter(
(MatrixRow.from_location_id == polygon_cross_locations[i].id)
& (MatrixRow.to_location_id ==
neighbours_cross_locations[nearest_idx].id)).first()
if not row:
row = MatrixRow(
from_location_id=polygon_cross_locations[i].id,
to_location_id=neighbours_cross_locations[nearest_idx].id,
distance=osrm_table['distances'][i][nearest_idx],
duration=osrm_table['durations'][i][nearest_idx])
db.session.add(row)
db.session.commit()
return True
def query_osm_power_stations():
res = db_session.query(
func.ST_AsText(
func.ST_Transform(func.ST_AsEWKB(PowerStations.geom),
4326)).label("geom"),
PowerStations.tags,
)
features = []
for r in res:
if r.geom is not None:
features.append(
Feature(geometry=Point(loadswkt(r.geom).coords[0]),
properties=r.tags))
return FeatureCollection(features)
def extract_query_objects(region_id, aquifer_id, variable):
session = get_session_obj()
aquifer_obj = session.query(gf2.ST_AsText(Aquifer.geometry),
Aquifer.aquifer_name).filter(
Aquifer.region_id == region_id,
Aquifer.id == aquifer_id).first()
bbox = wkt.loads(aquifer_obj[0]).bounds
print(bbox)
wells_query = session.query(Well).filter(Well.aquifer_id == aquifer_id)
wells_query_df = pd.read_sql(wells_query.statement, session.bind)
well_ids = [int(well_id) for well_id in wells_query_df.id.values]
# well_dict = {well.id: well.gse for well in wells_query_df.itertuples()}
m_query = session.query(Measurement).filter(
Measurement.well_id.in_(well_ids), Measurement.variable_id == variable)
measurements_df = pd.read_sql(m_query.statement, session.bind)
# measurements_df['gse'] = measurements_df['well_id'].map(well_dict)
measurements_df['date'] = pd.to_datetime(measurements_df.ts_time,
infer_datetime_format=True)
session.close()
return (bbox, wells_query_df, measurements_df, aquifer_obj)
def gps_text(self):
return functions.ST_AsText(self.position)
def calcDistanceKm(geom1, geom2):
coord1 = ST_AsTuple(session.query(functions.ST_AsText(geom1)).one())
coord2 = ST_AsTuple(session.query(functions.ST_AsText(geom2)).one())
return haversine(coord1, coord2)
def get_XML(geometry, srid, topicid, extracttime, lang, translations):
"""Gets the XML extract of the federal data feature service for a given topic
and validates it against the schema.
"""
# baseurl of the server of the swiss confederation
server = 'https://api3.geo.admin.ch'
# rest service call
url = '/rest/services/api/MapServer/identify'
# layers = 'all:ch.bazl.sicherheitszonenplan.oereb'
# bbox = 'mapExtent=671164.31244,253770,690364.31244,259530'
# geometry of the feature to call the feature server for
feature = geometry
# geomtype = 'geometryType=esriGeometryEnvelope'
# wktfeature = DBSession.scalar(geometry.ST_AsText())
bbox = get_bbox_from_geometry(
DBSession.scalar(functions.ST_AsText(geometry.ST_Envelope())))
# geometrytype used for feature service call
geomtype = 'esriGeometryPolygon'
# geomtype = 'esriGeometryEnvelope' - BBOX
# geomtype = 'esriGeometryPoint' - Point
# Size and resolution of the returned image
mapparams = '1920,576,96'
# geometry tolerance for intersection
tolerance = 5
# data format
format = 'interlis'
xml_layers = {
'R103': 'ch.bazl.projektierungszonen-flughafenanlagen.oereb',
'R108': 'ch.bazl.sicherheitszonenplan.oereb',
'R118': 'ch.bazl.kataster-belasteter-standorte-zivilflugplaetze.oereb',
'R119': 'ch.bav.kataster-belasteter-standorte-oev.oereb'
}
geomGeoJSON = loads(DBSession.scalar(geometry.ST_AsGeoJSON()))
coords = geomGeoJSON['coordinates']
if geomGeoJSON['type'] == 'MultiPolygon':
coords = coords[0]
# Stupid ESRI stuff: double quotes are needed to call the feature service, thus we have to hardcode "rings"
esrifeature = '{"rings":' + str(coords) + '}'
# Composing the feature service request
fsurl = server + url
params = {
'geometry': esrifeature,
'geometryType': geomtype,
'layers': 'all:' + xml_layers[topicid],
'mapExtent': str(bbox.values()).strip('[]'),
'imageDisplay': mapparams,
'tolerance': str(tolerance),
'geometryFormat': format,
'lang': lang
}
params = urllib.urlencode(params)
# Call the feature service URL wich sends back an XML Interlis 2.3 file in the OEREB Transfer structure
h = httplib2.Http()
(resp_headers, content) = h.request(fsurl, method="POST", body=params)
# trim all whitespace and newlines
content_lines = content.splitlines()
count = 0
for line in content_lines:
content_lines[count] = line.strip()
count += 1
content = ''.join(content_lines)
# validate XML
# xmldoc = parseString(content).firstChild
xmldoc = parseString(content).getElementsByTagName("TRANSFER")[0]
# extract the datasection from the response
datasection = xmldoc.getElementsByTagName("DATASECTION")[0]
# extract the complete tranfert structure
transferstructure = xmldoc.getElementsByTagName(
"OeREBKRM09trsfr.Transferstruktur")
if len(transferstructure[0].childNodes) > 0:
# Get the competent authority for the legal provisions
vsauthority = {
'shortname':
xmldoc.getElementsByTagName("OeREBKRM09vs.Vorschriften.Amt")
[0].getAttributeNode("TID").value,
'namede':
xmldoc.getElementsByTagName("OeREBKRM09vs.Vorschriften.Amt")
[0].getElementsByTagName("Text")[0].firstChild.data,
'namefr':
xmldoc.getElementsByTagName("OeREBKRM09vs.Vorschriften.Amt")
[0].getElementsByTagName("Text")[1].firstChild.data,
'namefr':
xmldoc.getElementsByTagName("OeREBKRM09vs.Vorschriften.Amt")
[0].getElementsByTagName("Text")[2].firstChild.data,
'url':
xmldoc.getElementsByTagName("OeREBKRM09vs.Vorschriften.Amt")
[0].getElementsByTagName("AmtImWeb")[0].firstChild.data
}
vslegalprovisions = xmldoc.getElementsByTagName(
"OeREBKRM09vs.Vorschriften.Dokument")
# Get the WMS and it's legend
xtfwms = {
'wmsurl':
xmldoc.getElementsByTagName(
"OeREBKRM09trsfr.Transferstruktur.DarstellungsDienst")
[0].getElementsByTagName("VerweisWMS")[0].firstChild.data,
'wmslegend':
xmldoc.getElementsByTagName(
"OeREBKRM09trsfr.Transferstruktur.DarstellungsDienst")
[0].getElementsByTagName("LegendeImWeb")[0].firstChild.data
}
# GET restrictions
xtfrestrictions = xmldoc.getElementsByTagName(
"OeREBKRM09trsfr.Transferstruktur.Eigentumsbeschraenkung")
if xtfrestrictions:
restrictions = []
restriction = {}
for xtfrestriction in xtfrestrictions:
restriction = {
'restrictionid':
xtfrestriction.getAttributeNode("TID").value,
'teneurde':
xtfrestriction.getElementsByTagName("Aussage")
[0].getElementsByTagName("Text")[0].firstChild.data,
'teneurfr':
xtfrestriction.getElementsByTagName("Aussage")
[0].getElementsByTagName("Text")[1].firstChild.data,
'teneurit':
xtfrestriction.getElementsByTagName("Aussage")
[0].getElementsByTagName("Text")[2].firstChild.data,
'topic':
xtfrestriction.getElementsByTagName(
"Thema")[0].firstChild.data,
'legalstate':
xtfrestriction.getElementsByTagName(
"Rechtsstatus")[0].firstChild.data,
'publishedsince':
xtfrestriction.getElementsByTagName(
"publiziertAb")[0].firstChild.data,
'url':
xtfrestriction.getElementsByTagName(
"DarstellungsDienst")[0].getAttributeNode("REF").value,
'authority':
xtfrestriction.getElementsByTagName(
"ZustaendigeStelle")[0].getAttributeNode("REF").value
}
restrictions.append(restriction)
xtfvslinkprovisions = xmldoc.getElementsByTagName(
"OeREBKRM09trsfr.Transferstruktur.HinweisVorschrift")
vslinkprovisions = []
for vslinkprovision in xtfvslinkprovisions:
vslinkprovisions.append({
'origin':
vslinkprovision.getElementsByTagName(
"Eigentumsbeschraenkung")[0].getAttributeNode("REF").value,
'link':
vslinkprovision.getElementsByTagName("Vorschrift")
[0].getAttributeNode("REF").value
})
xtfvslinkreferences = xmldoc.getElementsByTagName(
"OeREBKRM09vs.Vorschriften.HinweisWeitereDokumente")
vslinkreferences = []
for vslinkreference in xtfvslinkreferences:
vslinkreferences.append({
'origin':
vslinkreference.getElementsByTagName(
"Ursprung")[0].getAttributeNode("REF").value,
'link':
vslinkreference.getElementsByTagName("Hinweis")
[0].getAttributeNode("REF").value
})
xtfvslegalprovisions = xmldoc.getElementsByTagName(
"OeREBKRM09vs.Vorschriften.Rechtsvorschrift")
vslegalprovisions = []
for vslegalprovision in xtfvslegalprovisions:
vslegalprovisions.append({
'provisionid':
vslegalprovision.getAttributeNode("TID").value,
'titel':
vslegalprovision.getElementsByTagName(
"Text")[0].firstChild.data,
'legalstate':
vslegalprovision.getElementsByTagName(
"Rechtsstatus")[0].firstChild.data,
'publishedsince':
vslegalprovision.getElementsByTagName(
"publiziertAb")[0].firstChild.data,
'authority':
vslegalprovision.getElementsByTagName(
"ZustaendigeStelle")[0].getAttributeNode("REF").value,
'url':
vslegalprovision.getElementsByTagName("TextImWeb")
[0].firstChild.data
})
xtfvsdocuments = xmldoc.getElementsByTagName(
"OeREBKRM09vs.Vorschriften.Dokument")
vsdocuments = []
for vsdocument in xtfvsdocuments:
vsdocuments.append({
'provisionid':
vsdocument.getAttributeNode("TID").value,
'titel':
vsdocument.getElementsByTagName("Text")[0].firstChild.data,
'legalstate':
vsdocument.getElementsByTagName(
"Rechtsstatus")[0].firstChild.data,
'publishedsince':
vsdocument.getElementsByTagName(
"publiziertAb")[0].firstChild.data,
'authority':
vsdocument.getElementsByTagName("ZustaendigeStelle")
[0].getAttributeNode("REF").value,
'url':
vsdocument.getElementsByTagName("TextImWeb")[0].firstChild.data
})
xtflegalprovisions = xmldoc.getElementsByTagName(
"OeREBKRM09trsfr.Transferstruktur.HinweisVorschrift")
feature = []
for xtflegalprovision in xtflegalprovisions:
feature.append({
'restrictionid':
xtflegalprovision.getElementsByTagName(
"Eigentumsbeschraenkung")[0].getAttributeNode("REF").value,
'provision':
xtflegalprovision.getElementsByTagName("Vorschrift")
[0].getAttributeNode("REF").value
})
xtfreferences = xmldoc.getElementsByTagName(
"OeREBKRM09vs.Vorschriften.HinweisWeitereDokumente")
xtfgeoms = xmldoc.getElementsByTagName(
"OeREBKRM09trsfr.Transferstruktur.Geometrie")
geometries = []
for xtfgeom in xtfgeoms:
if xtfgeom.getElementsByTagName("Flaeche"):
if xtfgeom.getElementsByTagName("SURFACE"):
surfaces = xtfgeom.getElementsByTagName("SURFACE")
if xtfgeom.getElementsByTagName("BOUNDARY"):
boundaries = xtfgeom.getElementsByTagName("BOUNDARY")
if xtfgeom.getElementsByTagName("POLYLINE"):
polylines = xtfgeom.getElementsByTagName(
"POLYLINE")
multipolygon = []
for polyline in polylines:
coordlist = []
for coords in polyline.childNodes:
coordlist.append(
(float(
coords.getElementsByTagName("C1")
[0].firstChild.data),
float(
coords.getElementsByTagName("C2")
[0].firstChild.data)))
# del coordlist[-1]
polygon = splPolygon(coordlist)
if len(polylines) > 1:
multipolygon.append(polygon)
geom = splMultiPolygon(multipolygon)
else:
geom = polygon
elif xtfgeom.getElementsByTagName(
"Punkt") and not xtfgeom.getElementsByTagName("Flaeche"):
point = xtfgeom.getElementsByTagName("Punkt")[0]
coordlist = []
for coords in point.childNodes:
coordlist.append((float(
coords.getElementsByTagName("C1")[0].firstChild.data),
float(
coords.getElementsByTagName("C2")
[0].firstChild.data)))
geom = splPoint(coordlist)
else:
geom = None
geometries.append({
'tid':
xtfgeom.getAttributeNode("TID").value,
'restrictionid':
xtfgeom.getElementsByTagName(
"Eigentumsbeschraenkung")[0].getAttributeNode("REF").value,
'competentAuthority':
xtfgeom.getElementsByTagName(
"ZustaendigeStelle")[0].getAttributeNode("REF").value,
'legalstate':
xtfgeom.getElementsByTagName(
"Rechtsstatus")[0].firstChild.data,
'publishedsince':
xtfgeom.getElementsByTagName("publiziertAb")
[0].firstChild.data,
# 'metadata': xtfgeom.getElementsByTagName("MetadatenGeobasisdaten")[0].firstChild.data,
'geom':
geom.wkt
})
for geometry in geometries:
if topicid in [u'R103', '103']:
xml_model = CHAirportProjectZonesPDF()
xml_model.theme = translations[
'CHAirportProjectZonesThemeLabel'] # u'Zones réservées des installations aéroportuaires'
xml_model.teneur = translations[
'CHAirportProjectZonesContentLabel'] # u'Limitation de la hauteur des bâtiments et autres obstacles'
elif topicid in [u'R108', '108']:
xml_model = CHAirportSecurityZonesPDF()
xml_model.theme = translations[
'CHAirportSecurityZonesThemeLabel'] # u'Plan de la zone de sécurité des aéroports'
xml_model.teneur = translations[
'CHAirportSecurityZonesContentLabel'] # u'Limitation de la hauteur des bâtiments et autres obstacles'
elif topicid in [u'R118', '118']:
xml_model = CHPollutedSitesCivilAirportsPDF()
xml_model.theme = translations[
'CHPollutedSitesCivilAirportsThemeLabel'] # u'Cadastre des sites pollués - domaine des transports publics'
xml_model.teneur = translations[
'CHPollutedSitesCivilAirportsContentLabel'] # u'Sites pollués'
elif topicid in [u'R119', '119']:
xml_model = CHPollutedSitesPublicTransportsPDF()
xml_model.theme = translations[
'CHPollutedSitesPublicTransportsThemeLabel'] # u'Cadastre des sites pollués - domaine des transports publics'
xml_model.teneur = translations[
'CHPollutedSitesPublicTransportsContentLabel'] # u'Sites pollués'
xml_model.codegenre = None
if geometry['legalstate'] == u'inKraft':
xml_model.statutjuridique = translations[
'legalstateLabelvalid'] # u'En vigueur'
else:
xml_model.statutjuridique = translations[
'legalstateLabelmodification'] # u'En cours d\'approbation'
if geometry['publishedsince']:
xml_model.datepublication = geometry['publishedsince']
else:
xml_model.datepublication = None
# It is very important to set the SRID if it's not the default EPSG:4326 !!
xml_model.idobj = str(extracttime) + '_' + str(
geometry['restrictionid'])
xml_model.geom = WKTElement(geometry['geom'], srid)
DBSession.add(xml_model)
DBSession.flush()
return
def get_feature_info(id, srid, translations):
"""The function gets the geometry of a parcel by it's ID and does an overlay
with other administrative layers to get the basic parcelInfo and attribute
information of the parcel : municipality, local names, and so on
hint:
for debbuging the query use str(query) in the console/browser window
to visualize geom.wkt use session.scalar(geom.wkt)
"""
try:
SRS = srid
except:
SRS = 2056
parcelInfo = {}
parcelInfo['featureid'] = None
Y = None
X = None
if id:
parcelInfo['featureid'] = id
# elif request.params.get('X') and request.params.get('Y') :
# X = int(request.params.get('X'))
# Y = int(request.params.get('Y'))
else:
raise Exception(translations[''])
if parcelInfo['featureid'] is not None:
queryresult = DBSession.query(Property).filter_by(
id=parcelInfo['featureid']).first()
# We should check unicity of the property id and raise an exception if there are multiple results
elif (X > 0 and Y > 0):
if Y > X:
pointYX = WKTElement('POINT(' + str(Y) + ' ' + str(X) + ')', SRS)
else:
pointYX = WKTElement('POINT(' + str(X) + ' ' + str(Y) + ')', SRS)
queryresult = DBSession.query(Property).filter(
Property.geom.ST_Contains(pointYX)).first()
parcelInfo['featureid'] = queryresult.id
else:
# to define
return HTTPBadRequest(translations['HTTPBadRequestMsg'])
parcelInfo['geom'] = queryresult.geom
parcelInfo['area'] = int(
round(DBSession.scalar(queryresult.geom.ST_Area()), 0))
if isinstance(LocalName, (types.ClassType)) is False:
queryresult1 = DBSession.query(LocalName).filter(
LocalName.geom.ST_Intersects(parcelInfo['geom'])).first()
parcelInfo['lieu_dit'] = queryresult1.nomloc # Flurname
queryresult2 = DBSession.query(Town).filter(
Town.geom.ST_Buffer(1).ST_Contains(parcelInfo['geom'])).first()
parcelInfo['nummai'] = queryresult.nummai # Parcel number
parcelInfo['type'] = queryresult.typimm # Parcel type
if 'no_egrid' in queryresult.__table__.columns.keys():
parcelInfo['no_egrid'] = queryresult.no_egrid
else:
parcelInfo['no_egrid'] = translations['noEGRIDtext']
if parcelInfo['type'] is None:
parcelInfo['type'] = translations['UndefinedPropertyType']
if 'numcad' in queryresult2.__table__.columns.keys():
parcelInfo['nomcad'] = queryresult2.cadnom
parcelInfo['numcom'] = queryresult.numcom
parcelInfo['nomcom'] = queryresult2.comnom
parcelInfo['nufeco'] = queryresult2.nufeco
parcelInfo['centerX'] = DBSession.scalar(
functions.ST_X(queryresult.geom.ST_Centroid()))
parcelInfo['centerY'] = DBSession.scalar(
functions.ST_Y(queryresult.geom.ST_Centroid()))
parcelInfo['BBOX'] = get_bbox_from_geometry(
DBSession.scalar(functions.ST_AsText(queryresult.geom.ST_Envelope())))
# the get_print_format function is not needed any longer as the paper size has been fixed to A4 by the cantons
# but we keep the code because the decision will be revoked
# parcelInfo['printFormat'] = get_print_format(parcelInfo['BBOX'])
return parcelInfo
'gis_polygon',
meta,
Column('_created', DateTime(timezone=False), server_default=func.now()),
Column('_updated', DateTime(timezone=False), onupdate=func.now()),
Column('id', Integer, primary_key=True, autoincrement=True,
nullable=False),
Column('class_id', Integer),
Column('name', String),
Column('props', JSON),
Column('geom', Geometry('POLYGON', srid=4326)),
)
base_query = sa.select([
gis_polygon.c.id, gis_polygon.c.name, gis_polygon.c._created,
gis_polygon.c._updated, gis_polygon.c.class_id, gis_polygon.c.props,
functions.ST_AsText(gis_polygon.c.geom).label('geom')
])
async def get_all(connection: SAConnection) -> RowProxy:
result = await connection.execute(base_query)
records = await result.fetchall()
return records
async def get_record(connection: SAConnection, record_id: int) -> RowProxy:
query = base_query.where(gis_polygon.c.id == record_id)
result = await connection.execute(query)
records = await result.first()
