def to_dict(self): data = { 'municipio_geom_6362_json': func.ST_AsGeoJSON( func.ST_Transform(self.municipio_geom_6362)), 'municipio_geom_4326_json': func.ST_AsGeoJSON( func.ST_Transform(self.municipio_geom_4326)) } return data
def to_dict(self): data = { 'entidad_geom_6362_json': func.ST_AsGeoJSON( func.ST_Transform(self.entidad_geom_6362)), 'entidad_geom_4326_json': func.ST_AsGeoJSON( func.ST_Transform(self.entidad_geom_4326)) } return data
def to_dict(self): data = { 'colonia_geom_6362_json': func.ST_AsGeoJSON( func.ST_Transform(self.colonia_geom_6362)), 'colonia_geom_4326_json': func.ST_AsGeoJSON( func.ST_Transform(self.colonia_geom_4326)) } return data
def to_dict(self): data = { 'localidad_urbana_geom_6362_json': func.ST_AsGeoJSON( func.ST_Transform(self.localidad_urbana_geom_6362)), 'localidad_urbana_geom_4326_json': func.ST_AsGeoJSON( func.ST_Transform(self.localidad_urbana_geom_4326)) } return data
def as_geojson(self): print(self.geom) print(db.session.scalar(func.ST_AsGeoJSON(self.geom))) waypoint = json.loads(db.session.scalar(func.ST_AsGeoJSON(self.geom))) waypoint['properties'] = dict( name=self.name, description=self.description, ) return waypoint
def _route_query(self): return self.db.query( Route.id, func.ST_AsGeoJSON(Route.origin).label('origin'), Route.origin_name, func.ST_AsGeoJSON(Route.destination).label('destination'), Route.destination_name, func.ST_AsGeoJSON(Route.polyline).label('polyline'), Route.bounds, Route.created )
def _value(self): if self.raw_data: return self.raw_data[0] if type(self.data) is geoalchemy2.elements.WKBElement: if self.srid == -1: return self.session.scalar(func.ST_AsGeoJSON(self.data)) else: return self.session.scalar( func.ST_AsGeoJSON( func.ST_Transform(self.data, self.web_srid))) else: return ''
def get_bbox(): """ Get bbbox of observations .. :quickref: Synthese; Parameters ----------- id_dataset: int: (query parameter) Returns ------- bbox: `geojson`: the bounding box in geojson """ params = request.args query = DB.session.query(func.ST_AsGeoJSON(func.ST_Extent(Synthese.the_geom_4326))) if "id_dataset" in params: query = query.filter(Synthese.id_dataset == params["id_dataset"]) data = query.one() if data and data[0]: return json.loads(data[0]) return None
def getGeom(): if request.method == 'GET': left = request.args.get('left') top = request.args.get('top') right = request.args.get('right') bottom = request.args.get('bottom') layerName = request.args.get('layerName') or '' if request.method == 'POST': left = request.form.get('left') top = request.form.get('top') right = request.form.get('right') bottom = request.form.get('bottom') layerName = request.form.get('layerName') or '' tablename = 'g_cloudlayer_meta_' + layerName cls = get_table_by_name(tablename) tBoundsText = 'POLYGON( ( %s %s ,%s %s ,%s %s ,%s %s ,%s %s ) )' % ( left, top, right, top, right, bottom, left, bottom, left, top) if cls is None: return 'cant find table:%s [%s]' % (tablename, tBoundsText) tmptext = func.ST_GeomFromText(tBoundsText, 0) geodata = db.session.query(func.ST_AsGeoJSON(cls.geomdata)).filter( cls.geomdata.ST_Intersects(tmptext)).all() #.limit(2) return return_feature_collection(geodata)
def formato_mapa(view, value): # Mostrar mapa maior na view de detalhes if 'details' in request.path: width = 400 height = 400 zoom = 17 # Mostrar mapa menor na view de listagem else: width = 100 height = 70 zoom = 15 # Passando parâmetros para renderização do widget do mapa params = html_params( **{ "data-role": "leaflet", "data-width": width, "data-height": height, "data-geometry-type": to_shape(value).geom_type, "data-zoom": zoom }) # Desabilitar edição do mapa na view de listagem # Deixar zoom habilitado na view de detalhes if 'details' not in request.path: params += u' disabled' if value.srid is -1: value.srid = 4326 geojson = view.session.query(view.model).with_entities( func.ST_AsGeoJSON(value)).scalar() return Markup('<textarea %s>%s</textarea>' % (params, geojson))
def get_incidents(in_polygon, crime_type): """ Get the last 15 days worth of incidents of a certain crime type that fall within the given polygon """ # calculate 15 days ago bound = datetime.now() - timedelta(days=15) # get rows a json features geojson = ( db.session.query(func.ST_AsGeoJSON(CrimeIncidentSimple.c.geom).label("json")) .filter( and_( func.ST_Contains(in_polygon, CrimeIncidentSimple.c.geom), CrimeIncidentSimple.c.dispatch_date_time >= bound, CrimeIncidentSimple.c.crime_type == crime_type, ) ) .all() ) # return geojson as feature collection return { "type": "FeatureCollection", "features": [json.loads(f.json) for f in geojson], }
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 index(cls, geom=None): # The attributes that we want to pass along as-is as_is_attr_names = [ 'dataset_name', 'human_name', 'date_added', 'attribution', 'description', 'update_freq', 'view_url', 'source_url', 'num_shapes', 'contributor_name', 'contributor_email', 'contributor_organization' ] as_is_attrs = [getattr(cls, name) for name in as_is_attr_names] # We need to apply some processing to the bounding box bbox = func.ST_AsGeoJSON(cls.bbox) attr_names = as_is_attr_names + ['bbox'] attrs = as_is_attrs + [bbox] result = session.query(*attrs).filter(cls.is_ingested) listing = [dict(zip(attr_names, row)) for row in result] for dataset in listing: dataset['date_added'] = str(dataset['date_added']) if geom: listing = cls.add_intersections_to_index(listing, geom) return listing
def _get_datasets_geojson(self, where_clause): return self._engine.execute( select([ func.jsonb_build_object( 'type', 'FeatureCollection', 'features', func.jsonb_agg( func.jsonb_build_object( # TODO: move ID to outer id field? 'type', 'Feature', 'geometry', func.ST_AsGeoJSON( func.ST_Transform( DATASET_SPATIAL.c.footprint, self._target_srid(), )).cast(postgres.JSONB), 'properties', func.jsonb_build_object( 'id', DATASET_SPATIAL.c.id, # TODO: dataset label? 'region_code', DATASET_SPATIAL.c.region_code.cast(String), 'creation_time', DATASET_SPATIAL.c.creation_time, 'center_time', DATASET_SPATIAL.c.center_time, ), ) ) ).label('datasets_geojson') ]).where(where_clause) ).fetchone()['datasets_geojson']
def default(self, obj): if isinstance(obj.__class__, DeclarativeMeta): # A SQLAlchemy class fields = {} for field in [ x for x in dir(obj) if not x.startswith("_") and x != "metadata" ]: data = obj.__getattribute__(field) # If column is geometry, serialize it as geojson if (field in obj.__table__.c and hasattr(obj.__table__.c[field].type, "name") and obj.__table__.c[field].type.name == "geometry"): fields[field] = json.loads( db_session.scalar(func.ST_AsGeoJSON(data))) else: try: json.dumps( data ) # this will fail on non-encodable values, like other classes fields[field] = data except TypeError: fields[field] = None # A json-encodable dict return fields return json.JSONEncoder.default(self, obj)
def link(unverified_link_id): link_id, link_uuid = verify_link_id(unverified_link_id) link_url_id = uuid_to_urlsafe_id(link_uuid) # Query aliases aliases = list(r[0] for r in db.session.query(LinkAlias.name).filter( LinkAlias.link_id == link_id)) # Query geometry geom = json.loads( db.session.query(func.ST_AsGeoJSON( Link.geom)).filter(Link.id == link_id).one()[0]) response = dict( type='Feature', id=link_url_id, geometry=geom, properties=dict( observationsUrl=url_for('.observations', unverified_link_id=link_url_id, _external=True), aliases=aliases, ), ) return jsonify(response)
def point_elevation(geometry, format_out, dataset): """ Performs PostGIS query to enrich a point geometry. :param geometry: Input point to be enriched with elevation :type geometry: shapely.geometry.Point :param format_out: Specifies output format. One of ['geojson', 'point'] :type format_out: string :param dataset: Elevation dataset to use for querying :type dataset: string :raises InvalidUsage: internal HTTP 500 error with more detailed description. :returns: 3D Point as GeoJSON or WKT :rtype: string """ Model = _getModel(dataset) input_crs = _get_crs(dataset) if geometry.geom_type == "Point": query_point2d = db.session \ .query(ST_Transform(func.ST_SetSRID(func.St_PointFromText(geometry.wkt), 4326), input_crs).label('geom')) \ .subquery() \ .alias('points2d') query_getelev = db.session \ .query(ST_Transform(query_point2d.c.geom, 4326).label('geom'), ST_Value(Model.rast, query_point2d.c.geom, False).label('z')) \ .filter(ST_Intersects(Model.rast, query_point2d.c.geom)) \ .subquery().alias('getelevation') if format_out == 'geojson': query_final = db.session \ .query(func.ST_AsGeoJSON(ST_SnapToGrid(func.ST_MakePoint(ST_X(query_getelev.c.geom), ST_Y(query_getelev.c.geom), query_getelev.c.z.cast(Integer)), coord_precision))) else: query_final = db.session \ .query(func.ST_AsText(ST_SnapToGrid(func.ST_MakePoint(ST_X(query_getelev.c.geom), ST_Y(query_getelev.c.geom), query_getelev.c.z.cast(Integer)), coord_precision))) else: raise InvalidUsage( 400, 4002, "Needs to be a Point, not {}!".format(geometry.geom_type)) try: result = query_final.one() return result[0] except NoResultFound: raise InvalidUsage( 404, 4002, f'{tuple(geometry.coords)[0]} has no elevation value in {dataset}')
def point_elevation(geometry, format_out, dataset): """ Performs PostGIS query to enrich a point geometry. :param geometry: Input point to be enriched with elevation :type geometry: shapely.geometry.Point :param format_out: Specifies output format. One of ['geojson', 'point'] :type format_out: string :param dataset: Elevation dataset to use for querying :type dataset: string :raises InvalidUsage: internal HTTP 500 error with more detailed description. :returns: 3D Point as GeoJSON or WKT :rtype: string """ Model = _getModel(dataset) if geometry.geom_type == "Point": query_point2d = db.session \ .query(func.ST_SetSRID(func.St_PointFromText(geometry.wkt), 4326).label('geom')) \ .subquery() \ .alias('points2d') query_getelev = db.session \ .query(query_point2d.c.geom, ST_Value(Model.rast, query_point2d.c.geom).label('z')) \ .filter(ST_Intersects(Model.rast, query_point2d.c.geom)) \ .subquery().alias('getelevation') if format_out == 'geojson': query_final = db.session \ .query(func.ST_AsGeoJSON(ST_SnapToGrid(func.ST_MakePoint(ST_X(query_getelev.c.geom), ST_Y(query_getelev.c.geom), query_getelev.c.z), coord_precision))) else: query_final = db.session \ .query(func.ST_AsText(ST_SnapToGrid(func.ST_MakePoint(ST_X(query_getelev.c.geom), ST_Y(query_getelev.c.geom), query_getelev.c.z), coord_precision))) else: raise InvalidUsage( 400, 4002, "Needs to be a Point, not {}!".format(geometry.geom_type)) try: return query_final[0][0] except: raise InvalidUsage( 404, 4002, 'The requested geometry is outside the bounds of {}'.format( dataset))
def test_run_spatial_function(session): """Example how to use spatial functions. Works on postgis and spatialite""" factories.ConnectionNodeFactory() from geoalchemy2 import func q = session.query(func.ST_AsGeoJSON(models.ConnectionNode.the_geom)) q.first()
def geojson_chunks(self): chunks = [] for chunk in self.polygon_chunk(size=place_chunk_size): clip = func.ST_Intersection(Place.geom, envelope(chunk)) geojson = (session.query(func.ST_AsGeoJSON( clip, 4)).filter(Place.place_id == self.place_id).scalar()) chunks.append(geojson) return chunks
def polygons(place_identifier): place = get_place(place_identifier) chunk_size = utils.calc_chunk_size(place.area_in_sq_km) place_geojson = (database.session.query(func.ST_AsGeoJSON( Place.geom, 4)).filter(Place.place_id == place.place_id).scalar()) # print(place_geojson) for chunk in place.chunk_n(chunk_size): print(', '.join('{:.3f}'.format(i) for i in chunk)) (ymin, ymax, xmin, xmax) = chunk clip = func.ST_Intersection( Place.geom, func.ST_MakeEnvelope(xmin, ymin, xmax, ymax)) chunk_geojson = (database.session.query(func.ST_AsGeoJSON( clip, 4)).filter(Place.place_id == place.place_id).scalar()) print(chunk_geojson)
def get_community_surrounding(cls, longitude, latitude): """Get a Community with boundaries surrounding a given point specified by longitude and latitude. Returns the Community id, name and boundaries. """ return db.session.query( Community, func.ST_AsGeoJSON(Community.boundaries)).filter( func.ST_Contains( Community.boundaries, WKTElement("POINT({} {})".format(longitude, latitude), 4326))).first()
def get_json(self, limit=None): """ Get JSON with all data :param limit: Max results :return: JSON with data formatted """ if limit is None: query = self.session.query( Estado.gid, Estado.name, Estado.descriptio, Estado.timestamp, Estado.icon, func.ST_AsGeoJSON(Estado.geom).label('geometry') ) else: query = self.session.query( Estado.gid, Estado.name, Estado.descriptio, Estado.timestamp, Estado.icon, func.ST_AsGeoJSON(Estado.geom).label('geometry') ).limit(limit) saida = list() for row in query: saida.append({ 'type': 'Feature', 'id': row.gid, 'geometry': json.loads(row.geometry), 'properties': { 'name': row.name, 'description': row.descriptio, 'timestamp': row.timestamp, 'icon': row.icon } }) return { 'type': 'FeatureCollection', 'features': saida }
def get(self): """Returns the data for all the revisions in the database.""" current_revision = (self.sess.query(func.max(ProblemsActivity.id)). \ first())[0] previous_revision = int(self.get_query_argument('rev', default=0)) if previous_revision == 0: query = self.sess.query( DetailedProblem, func.ST_AsGeoJSON(DetailedProblem.location)) problems = dict( current_activity_revision=current_revision, data=generate_data(query) ) json_string = json.dumps(problems, ensure_ascii=False) self.write(json_string) elif previous_revision == current_revision: self.write(dict(current_activity_revision=current_revision)) elif previous_revision < current_revision: removed = revision(self, previous_revision, "REMOVED") update = revision(self, previous_revision, "UPDATED", removed) added = revision(self, previous_revision, "ADDED", removed, update) vote = revision(self, previous_revision, "VOTE", removed, update, added) query = self.sess.query( DetailedProblem, func.ST_AsGeoJSON(DetailedProblem.location)). \ filter(DetailedProblem.id.in_(added + update)) small_query = self.sess.query(DetailedProblem.id, DetailedProblem.number_of_votes).filter( DetailedProblem.id.in_(vote)) problems = dict( current_activity_revision=current_revision, previous_activity_revision=previous_revision, data=generate_data(query) + removed_data(removed) + vote_data( small_query) ) json_string = json.dumps(problems, ensure_ascii=False) self.write(json_string) elif previous_revision > current_revision: self.send_error(400, message='Your revision is greater than current')
def coords(self): try: geojson = json.loads( db.session.scalar(func.ST_AsGeoJSON(self.location)) ) assert geojson.get('type') == 'Point' except (TypeError, AssertionError): return {'longitude': None, 'latitude': None} return dict(zip(('longitude', 'latitude'), geojson.get('coordinates')))
class DimEntidad(Base): __tablename__ = "dim_entidad" __table_args__ = {'extend_existing': True} entidad_geom_6362 = Column( Geometry(geometry_type='MULTIPOLYGON',srid = 6362), nullable = True) entidad_geom_4326 = Column( Geometry(geometry_type='MULTIPOLYGON',srid = 4326), nullable = True) entidad_geom_6362_json = column_property( func.ST_AsGeoJSON(func.ST_Transform(entidad_geom_6362, 6362))) entidad_geom_4326_json = column_property( func.ST_AsGeoJSON(func.ST_Transform(entidad_geom_4326, 4326))) def to_dict(self): data = { 'entidad_geom_6362_json': func.ST_AsGeoJSON( func.ST_Transform(self.entidad_geom_6362)), 'entidad_geom_4326_json': func.ST_AsGeoJSON( func.ST_Transform(self.entidad_geom_4326)) } return data
class DimLocalidadRural(Base): __tablename__ = "dim_localidad_rural" __table_args__ = {'extend_existing': True} localidad_rural_geom_6362 = Column( Geometry(geometry_type='MULTIPOLYGON',srid = 6362), nullable = True) localidad_rural_geom_4326 = Column( Geometry(geometry_type='MULTIPOLYGON',srid = 4326), nullable = True) localidad_rural_geom_6362_json = column_property( func.ST_AsGeoJSON(func.ST_Transform(localidad_rural_geom_6362, 6362))) localidad_rural_geom_4326_json = column_property( func.ST_AsGeoJSON(func.ST_Transform(localidad_rural_geom_4326, 4326))) def to_dict(self): data = { 'localidad_rural_geom_6362_json': func.ST_AsGeoJSON( func.ST_Transform(self.localidad_rural_geom_6362)), 'localidad_rural_geom_4326_json': func.ST_AsGeoJSON( func.ST_Transform(self.localidad_rural_geom_4326)) } return data
def get(self, problem_id=None): """Returns the data for all the problems in the database. If problem id is specified **/api/v1/problems/3**, returns the data for the specified problem. """ problem = self.sess.query( DetailedProblem, func.ST_AsGeoJSON(DetailedProblem.location)).filter( DetailedProblem.id == problem_id) data = generate_data(problem)[0] self.write(data)
def geom_formatter(view, value): params = html_params( **{ "data-role": "leaflet", "disabled": "disabled", "data-width": 100, "data-height": 70, "data-geometry-type": to_shape(value).geom_type, "data-zoom": 15, }) if value.srid is -1: value.srid = 4326 geojson = view.model.query.with_entities(func.ST_AsGeoJSON(value)).scalar() return Markup('<textarea %s>%s</textarea>' % (params, geojson))
def serialize(self): query = db.session.query(func.ST_AsGeoJSON(self.geometry)).first()[0] geojson = { 'geometry': json.loads(query), 'type': 'Feature', 'properties': { 'Nome': self.name, 'Descrição': self.description, 'Autor': self.author }, } return geojson