def _value(self): if self.raw_data: return self.raw_data[0] if type(self.data) is geoalchemy2.elements.WKBElement: if self.srid is -1: self.data = self.session.scalar(func.ST_AsGeoJson(self.data)) else: self.data = self.session.scalar( func.ST_AsGeoJson( func.ST_Transform(self.data, self.web_srid))) return super(GeoJSONField, self)._value()
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 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: geojson = current_app.extensions['sqlalchemy'].db.session.scalar( func.ST_AsGeoJson(value)) else: geojson = current_app.extensions['sqlalchemy'].db.session.scalar( func.ST_AsGeoJson(value.ST_Transform(4326))) return Markup('<textarea %s>%s</textarea>' % (params, geojson))
def get_plots_bounding_box_as_json(self): positions = [] if self.role.name == Role._ADMIN_ROLE: plots = Plot().queryObject().all() else: plots = self.plots for plot in plots: positions.append(plot.geom) return self.session.scalar(func.ST_AsGeoJson(func.ST_Envelope( func.ST_Union(array(positions))))) if len(positions) > 0\ else None
def get_bounding_box_as_json(self): positions = [] for position in self.positions: positions.append(position.geom) # We return the max number of positions plus one, so it can detect # there are more and not just the barrier number if len(positions) == (max_positions + 1): break return self.session.scalar(func.ST_AsGeoJson(func.ST_Envelope( func.ST_MakeLine(array(positions))))) if len(positions) > 0\ else None
def get_animals_bounding_box_as_json(self): positions = [] if self.role.name == Role._ADMIN_ROLE: animals = Animal().queryObject().all() else: animals = self.animals for animal in animals: if animal.n_positions > 0: positions.append(animal.positions[0].geom) return self.session.scalar(func.ST_AsGeoJson(func.ST_Envelope( func.ST_MakeLine(array(positions))))) if len(positions) > 0\ else None
def _replace_geom_column(self) -> List[Column]: """ Replace the geom column by a geojson one. :return: """ cols = list() for c in self.columns: if isinstance(c.type, Geometry): cols.append( func.ST_AsGeoJson(self._the_geom()).cast(JSON).label( c.name)) else: cols.append(c) return cols
def get_approx_position_as_geojson(self, time=datetime.utcnow(), filter_charging=True): positions = [] if self.id != None: query = Position().queryObject().filter( Position.animal_id == self.id) if filter_charging: query = query.filter(Position.charging == False) query = query.filter( func.abs(func.date_part('hour', Position.date - time)) <= 2) aux = query.order_by(Position.date.desc()).limit(50) for position in aux: positions.append(position.geom) return self.session.scalar(func.ST_AsGeoJson( func.ST_MinimumBoundingCircle(func.ST_Collect( array(positions))))) if len(positions) > 1\ else None
def line_elevation(geometry, format_out, dataset): """ Performs PostGIS query to enrich a line geometry. :param geometry: Input 2D line to be enriched with elevation :type geometry: Shapely geometry :param format_out: Specifies output format. One of ['geojson', 'polyline', 'encodedpolyline'] :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 line as GeoJSON or WKT :rtype: string """ Model = _getModel(dataset) if geometry.geom_type == 'LineString': query_points2d = db.session\ .query(func.ST_SetSRID(ST_DumpPoints(geometry.wkt).geom, 4326) \ .label('geom')) \ .subquery().alias('points2d') query_getelev = db.session \ .query(query_points2d.c.geom, ST_Value(Model.rast, query_points2d.c.geom).label('z')) \ .filter(ST_Intersects(Model.rast, query_points2d.c.geom)) \ .subquery().alias('getelevation') query_points3d = db.session \ .query(func.ST_SetSRID(func.ST_MakePoint(ST_X(query_getelev.c.geom), ST_Y(query_getelev.c.geom), query_getelev.c.z), 4326).label('geom')) \ .subquery().alias('points3d') if format_out == 'geojson': # Return GeoJSON directly in PostGIS query_final = db.session \ .query(func.ST_AsGeoJson(func.ST_MakeLine(ST_SnapToGrid(query_points3d.c.geom, coord_precision)))) else: # Else return the WKT of the geometry query_final = db.session \ .query(func.ST_AsText(func.ST_MakeLine(ST_SnapToGrid(query_points3d.c.geom, coord_precision)))) else: raise InvalidUsage( 400, 4002, "Needs to be a LineString, not a {}!".format(geometry.geom_type)) # Behaviour when all vertices are out of bounds if query_final[0][0] == None: raise InvalidUsage( 404, 4002, 'The requested geometry is outside the bounds of {}'.format( dataset)) return query_final[0][0]
def add(id_role): """ Réalise l'ajout de données dans la BD """ # TRAITEMENT FORMULAIRE form = ConstatForm(request.form) valid = form.validate() if not valid: session["form_data"] = request.form if request.form["id_constat"]: return redirect( url_for("routes.form", idc=request.form["id_constat"])) return redirect(url_for("routes.form")) data = request.form p2154 = DB.session.query( func.ST_AsGeoJson( func.ST_Transform( func.ST_SetSRID( func.ST_Point(float(data["geomlng"]), float(data["geomlat"])), 4326), 2154, ))) json2154 = json.loads(p2154[0][0]) constat = Constats( date_attaque=form.date_attaque.data, date_constat=form.date_constat.data, nom_agent1=form.nom_agent1.data, nom_agent2=form.nom_agent2.data, proprietaire=form.proprietaire.data, type_animaux=form.type_animaux.data.id, nb_victimes_mort=form.nb_victimes_mort.data, nb_victimes_blesse=form.nb_victimes_blesse.data, nb_disparus=form.nb_disparus.data, nb_indemnises=form.nb_indemnises.data, statut=form.statut.data.id, nb_jour_agent=form.nb_jour_agent.data, the_geom_point=from_shape(Point(json2154["coordinates"][0], json2154["coordinates"][1]), srid=2154), id_role=id_role, declaratif=form.declaratif.data, comment=form.comment.data, ) if form.id_constat.data: constat_before_update = Constats.query.get(form.id_constat.data) # TODO : check droit d'update app = (DB.session.query(Application).filter( Application.code_application == "GC").one()) nivDroit = (DB.session.query( AppUser.id_droit_max).filter(AppUser.id_role == id_role).filter( AppUser.id_application == app.id_application).one()[0]) if nivDroit > 2 or constat_before_update.id_role == id_role: constat.id_constat = form.id_constat.data DB.session.merge(constat) else: return render_template("noRight.html") else: DB.session.add(constat) DB.session.commit() if "form_data" in session: session.pop("form_data") return redirect(url_for("routes.map"))
def column_expression(self, col): return func.ST_AsGeoJson(col, type_=self)
def line_elevation(geometry, format_out, dataset): """ Performs PostGIS query to enrich a line geometry. :param geometry: Input 2D line to be enriched with elevation :type geometry: Shapely geometry :param format_out: Specifies output format. One of ['geojson', 'polyline', 'encodedpolyline'] :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 line as GeoJSON or WKT :rtype: string """ Model = _getModel(dataset) input_crs = _get_crs(dataset) if geometry.geom_type == 'LineString': query_points2d = db.session\ .query(ST_Transform(func.ST_SetSRID(ST_DumpPoints(geometry.wkt).geom, 4326), input_crs) \ .label('geom')) \ .subquery().alias('points2d') query_getelev = db.session \ .query(ST_Transform(query_points2d.c.geom, 4326).label('geom'), ST_Value(Model.rast, query_points2d.c.geom, False).label('z')) \ .filter(ST_Intersects(Model.rast, query_points2d.c.geom)) \ .subquery().alias('getelevation') query_points3d = db.session \ .query(func.ST_MakePoint(ST_X(query_getelev.c.geom), ST_Y(query_getelev.c.geom), query_getelev.c.z.cast(Integer) ).label('geom')) \ .subquery().alias('points3d') if format_out == 'geojson': # Return GeoJSON directly in PostGIS query_final = db.session \ .query(func.ST_AsGeoJson(func.ST_MakeLine(ST_SnapToGrid(query_points3d.c.geom, coord_precision)))) else: # Else return the WKT of the geometry query_final = db.session \ .query(func.ST_AsText(func.ST_MakeLine(ST_SnapToGrid(query_points3d.c.geom, coord_precision)))) else: raise InvalidUsage( 400, 4002, "Needs to be a LineString, not a {}!".format(geometry.geom_type)) try: result = query_final.one() except NoResultFound: raise InvalidUsage( 404, 4002, f'{tuple(geometry.coords)[0]} has no elevation value in {dataset}') return result[0]