def data(request):
dbsession = DBSession()
geom = request.params['geom'] if 'geom' in request.params else 'polygon'
srs = int(request.params['srs']) if 'srs' in request.params else 4326
root = int(request.params['root']) if 'root' in request.params else None
depth = int(request.params['depth']) if 'depth' in request.params else 1
level = int(request.params['level']) if 'level' in request.params else None
filter = []
# отбор по иерархии
p_coalesce = []
t_child = []
for l in range(depth):
t_tab = aliased(Unit)
p_coalesce.insert(0, t_tab.id)
t_child.append(t_tab)
q_child = dbsession.query(distinct(func.coalesce(*p_coalesce)).label('child_id')) \
.filter(t_child[0].parent_id == root)
for l in range(depth):
if l == 0: pass
else: q_child = q_child.join((t_child[l], t_child[l - 1].child))
child_id = [r.child_id for r in q_child.all()]
# геометрия
if geom == 'polygon':
t_geom = UnitPolygon
elif geom == 'point':
t_geom = UnitPoint
e_geom = func.st_astext(func.st_transform(func.st_setsrid(t_geom.geom, 4326), srs))
q_unit = dbsession.query(Unit, e_geom.label('geom')).options(joinedload(Unit.protocol_o), joinedload(Unit.protocol_i)).filter(Unit.id.in_(child_id)) \
.outerjoin((t_geom, t_geom.unit_id == Unit.id))
if level:
q_unit = q_unit.filter(Unit.level == level)
features = []
for record in q_unit.all():
properties = dict(protocol_o=record.Unit.protocol_o.as_dict() if record.Unit.protocol_o else None,
protocol_i=record.Unit.protocol_i.as_dict() if record.Unit.protocol_i else None,
unit=record.Unit.as_dict())
features.append(Feature(id=record.Unit.id, geometry=loads_wkt(record.geom) if record.geom else None, properties=properties))
fcoll = dumps_geojson(FeatureCollection(features))
if 'callback' in request.params:
return Response('%s(%s)' % (request.params['callback'], fcoll), content_type="text/javascript")
else:
return Response(fcoll, content_type="application/json")
def _process(self, *args, **kwargs):
with Connector.get_connection() as connection:
sel = select([
distinct(cast(
meta.occurrence.c.location,
String
)).label('location'),
func.st_astext(
meta.occurrence.c.location
).label('location_wkt'),
])
df = gpd.read_postgis(sel, connection, geom_col='location')
return df
def __call__(self):
tab = self.layer._sa_table(True)
idcol = tab.columns[self.layer.column_id]
columns = [idcol.label('id')]
where = []
geomcol = tab.columns[self.layer.column_geom]
srs = self.layer.srs if self._srs is None else self._srs
if srs.id != self.layer.geometry_srid:
geomexpr = func.st_transform(geomcol, srs.id)
else:
geomexpr = geomcol
if self._geom:
if self._geom_format == 'WKB':
geomexpr = func.st_asbinary(geomexpr, 'NDR')
else:
geomexpr = func.st_astext(geomexpr)
columns.append(geomexpr.label('geom'))
fieldmap = []
for idx, fld in enumerate(self.layer.fields, start=1):
if self._fields is None or fld.keyname in self._fields:
clabel = 'f%d' % idx
columns.append(
getattr(tab.columns, fld.column_name).label(clabel))
fieldmap.append((fld.keyname, clabel))
if self._filter_by:
for k, v in self._filter_by.items():
if k == 'id':
where.append(idcol == v)
else:
field = self.layer.field_by_keyname(k)
where.append(tab.columns[field.column_name] == v)
if self._filter:
token = []
for k, o, v in self._filter:
supported_operators = (
'eq',
'ne',
'isnull',
'ge',
'gt',
'le',
'lt',
'like',
'ilike',
)
if o not in supported_operators:
raise ValueError(
"Invalid operator '%s'. Only %r are supported." %
(o, supported_operators))
if o == 'like':
o = 'like_op'
elif o == 'ilike':
o = 'ilike_op'
elif o == "isnull":
if v == 'yes':
o = 'is_'
elif v == 'no':
o = 'isnot'
else:
raise ValueError(
"Invalid value '%s' for operator '%s'." % (v, o))
v = db.sql.null()
op = getattr(db.sql.operators, o)
if k == 'id':
column = idcol
else:
field = self.layer.field_by_keyname(k)
column = tab.columns[field.column_name]
token.append(op(column, v))
where.append(db.and_(True, *token))
if self._like:
token = []
for fld in self.layer.fields:
token.append(
db.sql.cast(tab.columns[fld.column_name],
db.Unicode).ilike('%' + self._like + '%'))
where.append(db.or_(*token))
if self._intersects:
reproject = self._intersects.srid is not None \
and self._intersects.srid != self.layer.geometry_srid
int_srs = SRS.filter_by(id=self._intersects.srid).one() \
if reproject else self.layer.srs
int_geom = func.st_geomfromtext(self._intersects.wkt)
if int_srs.is_geographic:
# Prevent tolerance condition error
bound_geom = func.st_makeenvelope(-180, -89.9, 180, 89.9)
int_geom = func.st_intersection(bound_geom, int_geom)
int_geom = func.st_setsrid(int_geom, int_srs.id)
if reproject:
int_geom = func.st_transform(int_geom,
self.layer.geometry_srid)
where.append(func.st_intersects(geomcol, int_geom))
if self._box:
columns.extend((
func.st_xmin(geomexpr).label('box_left'),
func.st_ymin(geomexpr).label('box_bottom'),
func.st_xmax(geomexpr).label('box_right'),
func.st_ymax(geomexpr).label('box_top'),
))
gt = self.layer.geometry_type
where.append(func.geometrytype(geomcol) == gt)
order_criterion = []
if self._order_by:
for order, k in self._order_by:
field = self.layer.field_by_keyname(k)
order_criterion.append(
dict(asc=db.asc,
desc=db.desc)[order](tab.columns[field.column_name]))
order_criterion.append(idcol)
class QueryFeatureSet(FeatureSet):
layer = self.layer
_geom = self._geom
_geom_format = self._geom_format
_box = self._box
_fields = self._fields
_limit = self._limit
_offset = self._offset
def __iter__(self):
query = sql.select(*columns) \
.where(db.and_(True, *where)) \
.limit(self._limit) \
.offset(self._offset) \
.order_by(*order_criterion)
conn = self.layer.connection.get_connection()
try:
result = conn.execute(query)
for row in result.mappings():
fdict = dict((k, row[l]) for k, l in fieldmap)
if self._geom:
if self._geom_format == 'WKB':
geom_data = row['geom'].tobytes()
geom = Geometry.from_wkb(geom_data,
validate=False)
else:
geom = Geometry.from_wkt(row['geom'],
validate=False)
else:
geom = None
yield Feature(
layer=self.layer,
id=row['id'],
fields=fdict,
geom=geom,
box=box(row['box_left'], row['box_bottom'],
row['box_right'], row['box_top'])
if self._box else None)
except SQLAlchemyError as exc:
raise ExternalDatabaseError(sa_error=exc)
finally:
conn.close()
@property
def total_count(self):
conn = self.layer.connection.get_connection()
try:
query = sql.select(func.count(idcol)) \
.where(db.and_(True, *where))
result = conn.execute(query)
return result.scalar()
except SQLAlchemyError as exc:
raise ExternalDatabaseError(sa_error=exc)
finally:
conn.close()
return QueryFeatureSet()
import piecewise.config
from sqlalchemy import MetaData, Table, Column, create_engine, func, select
from sqlalchemy.sql.expression import label
import json
aggregator = piecewise.config.read_config(
json.load(open("piecewise_config.json")))
spatial_bin = filter(lambda x: x.label == 'spatial_join', aggregator.bins)[0]
engine = create_engine(aggregator.database_uri)
metadata = MetaData()
metadata.bind = engine
geom = Column(spatial_bin.geometry_column)
spatial_table = Table(spatial_bin.table, metadata,
Column(spatial_bin.geometry_column))
import sys
with engine.begin() as conn, open(sys.argv[1], 'w') as out:
centroid = func.st_astext(func.st_centroid(func.st_collect(geom)))
query = select([func.st_x(centroid),
func.st_y(centroid)]).select_from(spatial_table)
(x, y) = conn.execute(query).fetchone()
out.write("var center = [{lat}, {lon}];".format(lat=y, lon=x))
def data(request):
dbsession = DBSession()
geom = request.params['geom'] if 'geom' in request.params else 'polygon'
srs = int(request.params['srs']) if 'srs' in request.params else 4326
root = int(request.params['root']) if 'root' in request.params else None
depth = int(request.params['depth']) if 'depth' in request.params else 1
level = int(request.params['level']) if 'level' in request.params else None
filter = []
# отбор по иерархии
p_coalesce = []
t_child = []
for l in range(depth):
t_tab = aliased(Unit)
p_coalesce.insert(0, t_tab.id)
t_child.append(t_tab)
q_child = dbsession.query(distinct(func.coalesce(*p_coalesce)).label('child_id')) \
.filter(t_child[0].parent_id == root)
for l in range(depth):
if l == 0: pass
else: q_child = q_child.join((t_child[l], t_child[l - 1].child))
child_id = [r.child_id for r in q_child.all()]
# геометрия
if geom == 'polygon':
t_geom = UnitPolygon
elif geom == 'point':
t_geom = UnitPoint
e_geom = func.st_astext(
func.st_transform(func.st_setsrid(t_geom.geom, 4326), srs))
q_unit = dbsession.query(Unit, e_geom.label('geom')).options(joinedload(Unit.protocol_o), joinedload(Unit.protocol_i)).filter(Unit.id.in_(child_id)) \
.outerjoin((t_geom, t_geom.unit_id == Unit.id))
if level:
q_unit = q_unit.filter(Unit.level == level)
features = []
for record in q_unit.all():
properties = dict(protocol_o=record.Unit.protocol_o.as_dict()
if record.Unit.protocol_o else None,
protocol_i=record.Unit.protocol_i.as_dict()
if record.Unit.protocol_i else None,
unit=record.Unit.as_dict())
features.append(
Feature(id=record.Unit.id,
geometry=loads_wkt(record.geom) if record.geom else None,
properties=properties))
fcoll = dumps_geojson(FeatureCollection(features))
if 'callback' in request.params:
return Response('%s(%s)' % (request.params['callback'], fcoll),
content_type="text/javascript")
else:
return Response(fcoll, content_type="application/json")
import piecewise.config
from sqlalchemy import MetaData, Table, Column, create_engine, func, select
from sqlalchemy.sql.expression import label
import json
aggregator = piecewise.config.read_config(json.load(open("piecewise_config.json")))
spatial_bin = filter(lambda x: x.label == 'spatial_join', aggregator.bins)[0]
engine = create_engine(aggregator.database_uri)
metadata = MetaData()
metadata.bind = engine
geom = Column(spatial_bin.geometry_column)
spatial_table = Table(spatial_bin.table, metadata, Column(spatial_bin.geometry_column))
import sys
with engine.begin() as conn, open(sys.argv[1], 'w') as out:
centroid = func.st_astext(func.st_centroid(func.st_collect(geom)))
query = select([func.st_x(centroid), func.st_y(centroid)]).select_from(spatial_table)
(x, y) = conn.execute(query).fetchone()
out.write("var center = [{lat}, {lon}];".format(lat=y, lon=x))
