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]