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