def gdal_dataset(self, extent=None, size=None): if extent is not None and size is not None: xmin, ymin, xmax, ymax = extent width, height = size items = ( RasterMosaicItem.filter( func.st_intersects( func.st_transform( func.st_makeenvelope(xmin, ymin, xmax, ymax, self.srs.id), 4326 ), RasterMosaicItem.footprint, ), RasterMosaicItem.resource_id == self.id, ) .order_by(RasterMosaicItem.position) .all() ) fnames = [] for item in items: fname = env.raster_mosaic.workdir_filename(item.fileobj) fnames.append(fname) if len(fnames) > 0: ds = gdal.BuildVRT( "", fnames, options=gdal.BuildVRTOptions( xRes=(xmax - xmin) / width, yRes=(ymax - ymin) / height, ) ) return ds
def synthese_data(users, datasets): with db.session.begin_nested(): source = TSources(name_source='Fixture', desc_source='Synthese data from fixture') db.session.add(source) now = datetime.datetime.now() geom_4326 = from_shape(Point(3.63492965698242, 44.3999389306734), srid=4326) with db.session.begin_nested(): taxon = Taxref.query.filter_by(cd_nom=713776).one() s = Synthese(id_source=source.id_source, unique_id_sinp=func.uuid_generate_v4(), dataset=datasets['own_dataset'], digitiser=users['self_user'], nom_cite='Ashmeadopria Kieffer', cd_nom=taxon.cd_nom, cd_hab=3, the_geom_4326=geom_4326, the_geom_point=geom_4326, the_geom_local=func.st_transform(geom_4326, 2154), date_min=now, date_max=now) db.session.add(s) data = [s] return data
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 _makevals(self, feature): values = dict() for f in self.fields: if f.keyname in feature.fields.keys(): values[f.column_name] = feature.fields[f.keyname] if feature.geom is not None: values[self.column_geom] = func.st_transform( ga.elements.WKBElement(bytearray(feature.geom.wkb), srid=self.srs_id), self.geometry_srid) return values
def get_areas(): params = request.args.to_dict() area_type_code = params.get("area_type_code", "") data = (DB.session.query( LAreas.area_name, func.st_asgeojson( func.st_transform(func.st_simplify(LAreas.geom, 10), 4326), ), ).join(BibAreaType, BibAreaType.id_type == LAreas.id_type).filter( BibAreaType.type_code == area_type_code).all()) features = [] for d in data: geojson = json.loads(d[1]) geojson["properties"] = d[0] features.append(geojson) return jsonify(FeatureCollection(features))
def unit_search(request): q = request.params['term'] srs = int(request.params['srs']) if 'srs' in request.params else 4326 dbsession = DBSession() fields = ( Unit, func.st_xmin( func.st_transform(func.st_setsrid(UnitPolygon.geom, 4326), srs)).label('left'), func.st_xmax( func.st_transform(func.st_setsrid(UnitPolygon.geom, 4326), srs)).label('right'), func.st_ymin( func.st_transform(func.st_setsrid(UnitPolygon.geom, 4326), srs)).label('bottom'), func.st_ymax( func.st_transform(func.st_setsrid(UnitPolygon.geom, 4326), srs)).label('top'), func.st_x(func.st_transform(func.st_setsrid(UnitPoint.geom, 4326), srs)).label('x'), func.st_y(func.st_transform(func.st_setsrid(UnitPoint.geom, 4326), srs)).label('y'), ) result = dbsession.query(*fields) \ .outerjoin((UnitPolygon, UnitPolygon.unit_id == Unit.id)) \ .outerjoin((UnitPoint, UnitPoint.unit_id == Unit.id)) \ .filter(Unit.name.ilike('%' + q + '%')) \ .order_by(Unit.id_level4, Unit.name).limit(10) rows = [] for r in result: itm = r.Unit.as_dict() itm['value'] = r.Unit.name if r.left and r.top: itm['extent'] = (r.left, r.bottom, r.right, r.top) if r.x and r.y: itm['x'] = r.x itm['y'] = r.y rows.append(itm) return rows
class Constats(DB.Model): __tablename__ = "t_constats" __table_args__ = {"schema": "constats_loups"} id_constat = DB.Column(DB.Integer, primary_key=True, autoincrement=True) date_attaque = DB.Column(DB.Date) date_constat = DB.Column(DB.Date) nom_agent1 = DB.Column(DB.String) nom_agent2 = DB.Column(DB.String) proprietaire = DB.Column(DB.String) type_animaux = DB.Column(DB.Integer, ForeignKey("constats_loups.bib_type_animaux.id")) nb_victimes_mort = DB.Column(DB.Integer) nb_victimes_blesse = DB.Column(DB.Integer) nb_disparus = DB.Column(DB.Integer) nb_indemnises = DB.Column(DB.Integer) statut = DB.Column(DB.Integer, ForeignKey("constats_loups.bib_statut.id")) nb_jour_agent = DB.Column(DB.Float) id_secteur = DB.Column(DB.Integer, ForeignKey(LAreas.id_area)) id_commune = DB.Column(DB.Integer, ForeignKey(LAreas.id_area)) id_departement = DB.Column(DB.Integer, ForeignKey(LAreas.id_area)) dans_coeur = DB.Column(DB.Boolean) dans_aa = DB.Column(DB.Boolean) id_role = DB.Column(DB.Integer, ForeignKey(User.id_role)) the_geom_point = DB.Column(Geometry("GEOMETRY", 2154)) declaratif = DB.Column(DB.Boolean) comment = DB.Column(DB.String) statut_rel = DB.relationship("bib_statut", lazy="joined") type_animaux_rel = DB.relationship("bib_type_animaux", lazy="joined") digitizer = DB.relationship(User, lazy="joined") secteur = DB.relationship(LAreas, lazy="select", foreign_keys=[id_secteur]) commune = DB.relationship( LAreas, lazy="select", foreign_keys=[id_commune], ) departement = DB.relationship(LAreas, lazy="select", foreign_keys=[id_departement]) geom_4326 = column_property(func.st_transform(the_geom_point, 4326))
def unit_search(request): q = request.params['term'] srs = int(request.params['srs']) if 'srs' in request.params else 4326 dbsession = DBSession() fields = ( Unit, func.st_xmin(func.st_transform(func.st_setsrid(UnitPolygon.geom, 4326), srs)).label('left'), func.st_xmax(func.st_transform(func.st_setsrid(UnitPolygon.geom, 4326), srs)).label('right'), func.st_ymin(func.st_transform(func.st_setsrid(UnitPolygon.geom, 4326), srs)).label('bottom'), func.st_ymax(func.st_transform(func.st_setsrid(UnitPolygon.geom, 4326), srs)).label('top'), func.st_x(func.st_transform(func.st_setsrid(UnitPoint.geom, 4326), srs)).label('x'), func.st_y(func.st_transform(func.st_setsrid(UnitPoint.geom, 4326), srs)).label('y'), ) result = dbsession.query(*fields) \ .outerjoin((UnitPolygon, UnitPolygon.unit_id == Unit.id)) \ .outerjoin((UnitPoint, UnitPoint.unit_id == Unit.id)) \ .filter(Unit.name.ilike('%' + q + '%')) \ .order_by(Unit.id_level4, Unit.name).limit(10) rows = [] for r in result: itm = r.Unit.as_dict() itm['value'] = r.Unit.name if r.left and r.top: itm['extent'] = (r.left, r.bottom, r.right, r.top) if r.x and r.y: itm['x'] = r.x itm['y'] = r.y rows.append(itm) return rows
def __call__(self): tableinfo = TableInfo.from_layer(self.layer) tableinfo.setup_metadata(self.layer._tablename) table = tableinfo.table columns = [ table.columns.id, ] where = [] srsid = self.layer.srs_id if self._srs is None else self._srs.id geomcol = table.columns.geom geomexpr = func.st_transform(geomcol, srsid) if self._clip_by_box is not None: if _clipbybox2d_exists(): clip = func.st_setsrid( func.st_makeenvelope(*self._clip_by_box.bounds), self._clip_by_box.srid) geomexpr = func.st_clipbybox2d(geomexpr, clip) else: clip = func.st_setsrid( func.st_geomfromtext(self._clip_by_box.wkt), self._clip_by_box.srid) geomexpr = func.st_intersection(geomexpr, clip) if self._simplify is not None: geomexpr = func.st_simplifypreservetopology( geomexpr, self._simplify) if self._geom: if self._single_part: class geom(ColumnElement): def __init__(self, base): self.base = base @compiles(geom) def compile(expr, compiler, **kw): return "(%s).geom" % str(compiler.process(expr.base)) columns.append( func.st_asewkb(geom(func.st_dump(geomexpr))).label('geom')) else: columns.append(func.st_asewkb(geomexpr).label('geom')) if self._geom_len: columns.append( func.st_length( func.geography(func.st_transform(geomexpr, 4326))).label('geom_len')) 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'), )) selected_fields = [] for f in tableinfo.fields: if not self._fields or f.keyname in self._fields: columns.append(table.columns[f.key].label(f.keyname)) selected_fields.append(f) if self._filter_by: for k, v in six.iteritems(self._filter_by): if k == 'id': where.append(table.columns.id == v) else: where.append(table.columns[tableinfo[k].key] == v) if self._filter: token = [] for k, o, v in self._filter: supported_operators = ( "eq", "ge", "gt", "ilike", "in", "le", "like", "lt", "ne", "notin", "startswith", ) if o not in supported_operators: raise ValueError( "Invalid operator '%s'. Only %r are supported." % (o, supported_operators)) if v and o in ['in', 'notin']: v = v.split(',') if o in [ "ilike", "in", "like", "notin", "startswith", ]: o += "_op" op = getattr(db.sql.operators, o) if k == "id": token.append(op(table.columns.id, v)) else: token.append(op(table.columns[tableinfo[k].key], v)) where.append(db.and_(*token)) if self._filter_sql: token = [] for _filter_sql_item in self._filter_sql: if len(_filter_sql_item) == 3: table_column, op, val = _filter_sql_item if table_column == 'id': token.append(op(table.columns.id, val)) else: token.append( op(table.columns[tableinfo[table_column].key], val)) elif len(_filter_sql_item) == 4: table_column, op, val1, val2 = _filter_sql_item token.append( op(table.columns[tableinfo[table_column].key], val1, val2)) where.append(db.and_(*token)) if self._like: token = [] for f in tableinfo.fields: token.append( cast(table.columns[f.key], db.Unicode).ilike("%" + self._like + "%")) where.append(db.or_(*token)) if self._intersects: intgeom = func.st_setsrid( func.st_geomfromtext(self._intersects.wkt), self._intersects.srid) where.append( func.st_intersects( geomcol, func.st_transform(intgeom, self.layer.srs_id))) order_criterion = [] if self._order_by: for order, colname in self._order_by: order_criterion.append( dict(asc=db.asc, desc=db.desc)[order]( table.columns[tableinfo[colname].key])) order_criterion.append(table.columns.id) class QueryFeatureSet(FeatureSet): fields = selected_fields layer = self.layer _geom = self._geom _geom_len = self._geom_len _box = self._box _limit = self._limit _offset = self._offset def __iter__(self): query = sql.select( columns, whereclause=db.and_(*where), limit=self._limit, offset=self._offset, order_by=order_criterion, ) rows = DBSession.connection().execute(query) for row in rows: fdict = dict( (f.keyname, row[f.keyname]) for f in selected_fields) if self._geom: geom = geom_from_wkb(row['geom'].tobytes( ) if six.PY3 else six.binary_type(row['geom'])) else: geom = None calculated = dict() if self._geom_len: calculated['geom_len'] = row['geom_len'] yield Feature( layer=self.layer, id=row.id, fields=fdict, geom=geom, calculations=calculated, box=box(row.box_left, row.box_bottom, row.box_right, row.box_top) if self._box else None) @property def total_count(self): query = sql.select([ func.count(table.columns.id), ], whereclause=db.and_(*where)) res = DBSession.connection().execute(query) for row in res: return row[0] return QueryFeatureSet()
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()
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")