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
