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()