def apply(self, args, query):
if self.key not in args:
return query
WKT_shape_text = args[self.key]
WKT_query = "SRID=4326;" + WKT_shape_text
db = app_context().database
sample = db.model.sample
model_name = create_model_name_string(self.model)
if hasattr(self.model, "sample_collection") and self.model != sample:
return query.join(self.model.sample_collection).filter(
func.ST_GeomFromEWKT(WKT_query).ST_Contains(
func.ST_SetSRID(sample.location, 4326)))
path = nested_collection_path(model_name, "sample")
if path is not None and len(path) > 1:
db_query = nested_collection_joins(path, query, db, self.model)
return db_query.filter(
func.ST_GeomFromEWKT(WKT_query).ST_Contains(
func.ST_SetSRID(sample.location, 4326)))
return query.filter(
func.ST_GeomFromEWKT(WKT_query).ST_Contains(
func.ST_SetSRID(self.model.location, 4326)))
def pathandrow(cls, lat, lon):
"""
Output path and row that contains lat lon.
"""
try:
scene = (Session.query(cls.path, cls.row).filter(
func.ST_Within(func.ST_SetSRID(
func.ST_MakePoint(float(lon), float(lat)), 4236),
func.ST_SetSRID(cls.geom, 4236)), cls.mode == u'D').all())
return scene
except:
return u'----'
def import_nivo_sensor_station():
# this need refactor
res = requests.get(f"{Config.METEO_FRANCE_NIVO_BASE_URL}/postesNivo.json")
res.raise_for_status()
with connection_scope() as con:
with con.begin():
for feature in res.json()["features"]:
pointz = feature["geometry"]
pointz["coordinates"].append(
int(feature["properties"]["Altitude"]))
mf_id = (feature["properties"]["ID"]
if feature["properties"]["ID"] != "" else None)
ins = (insert(SensorStationTable).values(
**{
"nss_name":
feature["properties"]["Nom"],
"nss_meteofrance_id":
mf_id,
"the_geom":
func.ST_SetSRID(
func.ST_GeomFromGeoJSON(json.dumps(pointz)), 4326),
}).on_conflict_do_nothing(index_elements=["nss_name"]))
con.execute(ins)
inserted = (con.execute(
select([func.count(SensorStationTable.c.nss_id).label("count")
])).first().count)
click.echo(f"{inserted} sensor station imported")
def _get_data_from_a_point(lat, lng):
results = []
nearby = []
from application.extensions import db
point = func.ST_SetSRID(func.ST_MakePoint(float(lng), float(lat)), 4326)
features = db.session.query(Feature.data, Feature.feature,
Feature.publication).filter(
Feature.geometry.ST_Contains(point)).all()
for feature in features:
publication = Publication.query.filter_by(
publication=feature.publication).first()
# organisation = Organisation.query.filter_by(feature_id=feature.feature).first()
results.append({
'feature': feature,
'organisation': publication.organisation,
'publication': publication
})
# from geoalchemy2 import Geography
# from sqlalchemy import cast
# nearby_features = db.session.query(Feature.data).filter(func.ST_DWithin(Feature.geometry, cast(point, Geography), 500)).all()
# for feature in nearby_features:
# publication = Publication.query.filter_by(publication=feature.data['properties']['publication']).first()
# nearby.append({'feature': feature.data, 'organisation': publication.organisation, 'publication': publication})
return results, nearby
def update_meta(metatable, table):
"""
After ingest/update, update the metatable registry to reflect table information.
:param metatable: MetaTable instance to update.
:param table: Table instance to update from.
:returns: None
"""
metatable.update_date_added()
metatable.obs_from, metatable.obs_to = session.query(
func.min(table.c.point_date),
func.max(table.c.point_date)
).first()
metatable.bbox = session.query(
func.ST_SetSRID(
func.ST_Envelope(func.ST_Union(table.c.geom)),
4326
)
).first()[0]
session.add(metatable)
try:
session.commit()
except:
session.rollback()
raise
def update_meta(metatable, table):
"""
After ingest/update, update the metatable registry to reflect table information.
:param metatable: MetaTable instance to update.
:param table: Table instance to update from.
:returns: None
"""
metatable.update_date_added()
metatable.obs_from, metatable.obs_to = postgres_session.query(
func.min(table.c.point_date),
func.max(table.c.point_date)
).first()
metatable.bbox = postgres_session.query(
func.ST_SetSRID(
func.ST_Envelope(func.ST_Union(table.c.geom)),
4326
)
).first()[0]
metatable.column_names = {
c.name: str(c.type) for c in metatable.column_info()
if c.name not in {'geom', 'point_date', 'hash'}
}
postgres_session.add(metatable)
postgres_session.commit()
def from_geojson(cls, geojson):
"""Turn a GeoJSON string into a Geometry object that can
be put into the database.
"""
geometry = func.ST_GeomFromGeoJSON(geojson)
geometry = func.ST_SetSRID(geometry, 4326)
return geometry
def calculate(self):
self.center = func.ST_SetSRID(
func.ST_MakePoint(*self.array.mean(axis=0)), SRID)
try:
pca = Orientation(self.centered_array)
except IndexError:
# If there aren't enough coordinates
return
except ValueError:
return
self.principal_axes = pca.axes.tolist()
self.singular_values = pca.singular_values.tolist()
# Really this is hyperbolic axis lengths
# should change API to reflect this distinction
self.hyperbolic_axes = sampling_axes(pca).tolist()
self.n_samples = pca.n
self.strike, self.dip, self.rake = pca.strike_dip_rake()
if self.dip == 90:
self.valid = False
a = angular_errors(self.hyperbolic_axes)
self.min_angular_error = 2 * N.degrees(a[0])
self.max_angular_error = 2 * N.degrees(a[1])
# Analogous to correlation coefficient for PCA
# but not exactly the same
self.correlation_coefficient = pca.explained_variance
def point_elevation(geometry, format_out, dataset):
"""
Performs PostGIS query to enrich a point geometry.
:param geometry: Input point to be enriched with elevation
:type geometry: shapely.geometry.Point
:param format_out: Specifies output format. One of ['geojson', 'point']
: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 Point as GeoJSON or WKT
:rtype: string
"""
Model = _getModel(dataset)
input_crs = _get_crs(dataset)
if geometry.geom_type == "Point":
query_point2d = db.session \
.query(ST_Transform(func.ST_SetSRID(func.St_PointFromText(geometry.wkt), 4326), input_crs).label('geom')) \
.subquery() \
.alias('points2d')
query_getelev = db.session \
.query(ST_Transform(query_point2d.c.geom, 4326).label('geom'),
ST_Value(Model.rast, query_point2d.c.geom, False).label('z')) \
.filter(ST_Intersects(Model.rast, query_point2d.c.geom)) \
.subquery().alias('getelevation')
if format_out == 'geojson':
query_final = db.session \
.query(func.ST_AsGeoJSON(ST_SnapToGrid(func.ST_MakePoint(ST_X(query_getelev.c.geom),
ST_Y(query_getelev.c.geom),
query_getelev.c.z.cast(Integer)),
coord_precision)))
else:
query_final = db.session \
.query(func.ST_AsText(ST_SnapToGrid(func.ST_MakePoint(ST_X(query_getelev.c.geom),
ST_Y(query_getelev.c.geom),
query_getelev.c.z.cast(Integer)),
coord_precision)))
else:
raise InvalidUsage(
400, 4002,
"Needs to be a Point, not {}!".format(geometry.geom_type))
try:
result = query_final.one()
return result[0]
except NoResultFound:
raise InvalidUsage(
404, 4002,
f'{tuple(geometry.coords)[0]} has no elevation value in {dataset}')
def point_elevation(geometry, format_out, dataset):
"""
Performs PostGIS query to enrich a point geometry.
:param geometry: Input point to be enriched with elevation
:type geometry: shapely.geometry.Point
:param format_out: Specifies output format. One of ['geojson', 'point']
: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 Point as GeoJSON or WKT
:rtype: string
"""
Model = _getModel(dataset)
if geometry.geom_type == "Point":
query_point2d = db.session \
.query(func.ST_SetSRID(func.St_PointFromText(geometry.wkt), 4326).label('geom')) \
.subquery() \
.alias('points2d')
query_getelev = db.session \
.query(query_point2d.c.geom,
ST_Value(Model.rast, query_point2d.c.geom).label('z')) \
.filter(ST_Intersects(Model.rast, query_point2d.c.geom)) \
.subquery().alias('getelevation')
if format_out == 'geojson':
query_final = db.session \
.query(func.ST_AsGeoJSON(ST_SnapToGrid(func.ST_MakePoint(ST_X(query_getelev.c.geom),
ST_Y(query_getelev.c.geom),
query_getelev.c.z),
coord_precision)))
else:
query_final = db.session \
.query(func.ST_AsText(ST_SnapToGrid(func.ST_MakePoint(ST_X(query_getelev.c.geom),
ST_Y(query_getelev.c.geom),
query_getelev.c.z),
coord_precision)))
else:
raise InvalidUsage(
400, 4002,
"Needs to be a Point, not {}!".format(geometry.geom_type))
try:
return query_final[0][0]
except:
raise InvalidUsage(
404, 4002,
'The requested geometry is outside the bounds of {}'.format(
dataset))
def get_tile_mercator(easting, northing):
session = session_factory()
with session.begin(subtransactions=True):
r = session.query(Tile).filter(
func.ST_Contains(
Tile.geometry,
func.ST_SetSRID(func.ST_Point(easting, northing),
cs.WEB_MERCATOR_CODE))).first()
if r:
return r
ewkt = Tile.create_tile_ewkt_mercator(easting, northing)
tile = Tile()
tile.geometry = ewkt
CountryBounds.set_country_for_tile(tile)
return tile
def sf_crime(fpath=None, crime_type='violent'):
#raw_crime = sf_raw_crime(fpath=fpath)
# Assume for now there's no duplicate in the raw data, which means we don't
# - dedupe_crime()
# - and don't create src_crime()
raw_crime_table = Table('raw_sf_crimes_all',
Base.metadata,
autoload=True,
autoload_with=engine,
extend_existing=True)
if crime_type == 'violent':
categories = ['ASSAULT', 'ROBBERY', 'SEX OFFENSES, FORCIBLE']
elif crime_type == 'property':
categories = ['LARCENY/THEFT', 'VEHICLE THEFT', 'BURGLARY', 'STOLEN PROPERTY',\
'ARSON', 'VANDALISM']
# Create table "dat_sf_crimes_all", that contains additional fields needed
# by Plenario, in addition to the raw data
crime_table = sf_crime_table('sf_{0}_crimes'.format(crime_type),
Base.metadata)
# Add geom column
crime_table.append_column(Column('geom', Geometry('POINT', srid=4326)))
# Add obs_date column
crime_table.append_column(Column('obs_date', DateTime))
# Add row_id column
crime_table.append_column(Column('row_id', Integer, primary_key=True))
# Constrain (id, start_date) to be unique (?)
# dat_crime_table.append_constraint(UniqueConstraint('id', 'start_date'))
crime_table.drop(bind=engine, checkfirst=True)
crime_table.create(bind=engine)
new_cols = ['row_id']
# Insert data from raw_crime_table (to be src_crime_table when we'll check
# for duplicates)
dat_ins = crime_table.insert()\
.from_select(
[c for c in crime_table.columns.keys() if c not in new_cols],
select([c for c in raw_crime_table.columns if c.name !=
'dup_row_id'] + [
func.ST_SetSRID(
func.ST_MakePoint(raw_crime_table.c['longitude'],
raw_crime_table.c['latitude']), 4326) ] + \
[ raw_crime_table.c['date'].label('obs_date') ])\
.where(raw_crime_table.c['category'].in_(categories))
)
conn = engine.contextual_connect()
res = conn.execute(dat_ins)
return 'Table sf_{0}_crime created'.format(crime_type)
def patch_links():
# Get request body as JSON document
body = request.get_json()
# Sanitise body
if body is None:
raise ApiBadRequest('request body must be non-empty')
if not isinstance(body, dict):
raise ApiBadRequest('request body must be a JSON object')
# Extract create requests
try:
create_requests = body['create']
except KeyError:
create_requests = []
if not isinstance(create_requests,
list) or len(create_requests) > PAGE_LIMIT:
raise ApiBadRequest(
'create request must be an array of at most {0} items'.format(
PAGE_LIMIT))
# Process create requests
created_links = []
for r in create_requests:
geom_geojson = json.dumps(
dict(type='LineString', coordinates=r['coordinates']))
created_links.append(
Link(uuid=uuid.uuid4().hex,
geom=func.ST_SetSRID(func.ST_GeomFromGeoJSON(geom_geojson),
4326)))
db.session.add_all(created_links)
def make_create_response(l):
id = uuid_to_urlsafe_id(l.uuid)
return dict(id=id,
url=url_for('.link', unverified_link_id=id,
_external=True))
create_responses = list(make_create_response(l) for l in created_links)
db.session.commit()
response = dict(create=create_responses)
return jsonify(response)
def update_meta(metadata, table):
"""
After ingest/update, update the metadata registry to reflect
:param metadata:
:param table:
"""
metadata.update_date_added()
metadata.obs_from, metadata.obs_to =\
session.query(func.min(table.c.point_date),
func.max(table.c.point_date)).first()
bbox = session.query(
func.ST_SetSRID(func.ST_Envelope(func.ST_Union(table.c.geom)),
4326)).first()[0]
metadata.bbox = bbox
session.add(metadata)
try:
session.commit()
except:
session.rollback()
raise
def _geom_col(self):
"""
Derive selectable with a PostGIS point in 4326 projection
derived from either the latitude and longitude columns
or single location column
"""
t = self.staging
d = self.dataset
if d.lat and d.lon:
# Assume latitude and longitude columns are both numeric types.
geom_col = func.ST_SetSRID(func.ST_Point(t.c[d.lon], t.c[d.lat]),
4326).label('geom')
elif d.loc:
geom_col = func.point_from_loc(t.c[d.loc]).label('geom')
else:
msg = 'Staging table does not have geometry information.'
raise PlenarioETLError(msg)
return geom_col
def neighborhood_found(self):
"""Use PostGIS to find which neighborhood a long/lat pair is in."""
log.debug('neighborhood_found')
SESSION.query(
Location
).filter(
func.ST_Contains(
Neighborhood.geom,
func.ST_SetSRID(
func.ST_Point(
cast(Location.longitude, Float),
cast(Location.latitude, Float)
),
4326
)
)
).update(
{Location.neighborhood: Neighborhood.gnocdc_lab},
synchronize_session='fetch'
)
SESSION.commit()
def data_route():
west = request.args.get('w')
east = request.args.get('e')
north = request.args.get('n')
south = request.args.get('s')
month = request.args.get('m')
nb = int(request.args.get('nb', config['max_nb_cities_at_once']))
if nb > config['max_nb_cities_at_once']:
nb = config['max_nb_cities_at_once']
if west is None or east is None or south is None or north is None:
return 'TODO 404'
rectangle = 'POLYGON(({0} {1}, {0} {2}, {3} {2}, {3} {1}, {0} {1}))' \
.format(west, south, north, east)
with session_scope() as session:
# choose N cities
sq = session.query(City) \
.filter(func.ST_Covers(
cast(rectangle, Geometry()),
func.ST_SetSRID(cast(City.location, Geometry()), 0))) \
.order_by(City.priority_index) \
.limit(nb).subquery('city')
# get their data
query = session.query(
sq.c.id,
sq.c.name,
func.ST_Y(cast(sq.c.location, Geometry())),
func.ST_X(cast(sq.c.location, Geometry())),
sq.c.population, MonthlyStat.month,
MonthlyStat.value, Stat.code, sq.c.country, sq.c.source) \
.join(MonthlyStat) \
.join(Stat) \
.filter(Stat.code.in_(['avgHigh', 'avgLow', 'precipitation',
'precipitationDays', 'monthlySunHours',
'rain', 'rainDays']))
if month is not None:
query = query.filter(MonthlyStat.month == month)
def default():
return {'month_stats': defaultdict(dict)}
cities = defaultdict(default)
# print(query)
# format what is returned from the query
for row in query:
id = row[0]
cities[id]['name'] = row[1]
cities[id]['coords'] = (row[2], row[3])
cities[id]['pop'] = row[4]
cities[id]['month_stats'][row[7]][row[5]] = row[6]
cities[id]['country'] = row[8]
cities[id]['source'] = row[9]
# changing rain to precipitation
# TODO something similar with snow?
for _,c in cities.items():
for old, new in [('rain', 'precipitation'),
('rainDays', 'precipitationDays')]:
if old in c['month_stats'] and new not in c['month_stats']:
c['month_stats'][new] = c['month_stats'].pop(old)
# from pprint import pprint
# pprint(cities)
return json.dumps(cities)
from geonature.utils.env import db
from geonature.utils.config import config
from geonature.core.ref_geo.models import BibAreasTypes, LiMunicipalities, LAreas
from utils_flask_sqla.response import json_resp
routes = Blueprint("ref_geo", __name__)
altitude_stmt = sa.select([
sa.column('altitude_min'),
sa.column('altitude_max'),
]).select_from(
func.ref_geo.fct_get_altitude_intersection(
func.ST_SetSRID(
func.ST_GeomFromGeoJSON(sa.bindparam('geojson')),
4326,
), ))
geojson_intersect_filter = func.ST_Intersects(
LAreas.geom,
func.ST_Transform(
func.ST_SetSRID(func.ST_GeomFromGeoJSON(sa.bindparam('geojson')),
4326),
config['LOCAL_SRID'],
),
)
area_size_func = func.ST_Area(
func.ST_Transform(
func.ST_SetSrid(
func.ST_GeomFromGeoJSON(sa.bindparam('geojson')),
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 pg_geometry(feature):
geom = dumps(feature["geometry"])
_ = func.ST_GeomFromGeoJSON(geom)
return func.ST_SetSRID(func.ST_MakeValid(func.ST_Multi(_)), 4326)
def convert_vehicle(nyc3dcars_session, labeler_vehicle):
"""Converts the vehicle from Labeler format to NYC3DCars format."""
photo, lat, lon, alt = nyc3dcars_session.query(
Photo,
func.ST_Y(Photo.lla),
func.ST_X(Photo.lla),
func.ST_Z(Photo.lla)) \
.filter_by(id=labeler_vehicle.revision.annotation.pid) \
.options(joinedload('dataset')) \
.one()
left = labeler_vehicle.x1
right = labeler_vehicle.x2
top = labeler_vehicle.y1
bottom = labeler_vehicle.y2
camera_lla = numpy.array([[lat], [lon], [alt]])
camera_enu = pygeo.LLAToENU(camera_lla.T).reshape((3, 3))
dataset_correction = numpy.array([
[photo.dataset.t1],
[photo.dataset.t2],
[photo.dataset.t3],
])
camera_rotation = numpy.array([
[photo.r11, photo.r12, photo.r13],
[photo.r21, photo.r22, photo.r23],
[photo.r31, photo.r32, photo.r33],
])
camera_up = camera_enu.T.dot(
camera_rotation.T.dot(numpy.array([[0], [1], [0]])))
offset = numpy.array([[-labeler_vehicle.x], [-labeler_vehicle.z], [0]])
camera_offset = camera_up * \
labeler_vehicle.revision.cameraheight / camera_up[2]
total_offset = offset - camera_offset
ecef_camera = pygeo.LLAToECEF(camera_lla.T).T
ecef_camera += dataset_correction
ecef_total_offset = camera_enu.dot(total_offset)
vehicle_ecef = ecef_camera + ecef_total_offset
vehicle_type = labeler_vehicle.type
model = nyc3dcars_session.query(VehicleType) \
.filter_by(label=vehicle_type) \
.one()
vehicle_lla = pygeo.ECEFToLLA(vehicle_ecef.T).T
theta = math.radians(-labeler_vehicle.theta)
mlength = model.length
mwidth = model.width
car_a = -math.sin(theta) * 0.3048 * \
mlength / 2 + math.cos(theta) * 0.3048 * mwidth / 2
car_b = math.cos(theta) * 0.3048 * mlength / \
2 + math.sin(theta) * 0.3048 * mwidth / 2
car_c = math.sin(theta) * 0.3048 * mlength / \
2 + math.cos(theta) * 0.3048 * mwidth / 2
car_d = -math.cos(theta) * 0.3048 * \
mlength / 2 + math.sin(theta) * 0.3048 * mwidth / 2
car_corner_offset1 = camera_enu.dot(numpy.array([[car_a], [car_b], [0]]))
car_corner_offset2 = camera_enu.dot(numpy.array([[car_c], [car_d], [0]]))
car_corner1 = pygeo.ECEFToLLA(
(vehicle_ecef + car_corner_offset1).T).T.flatten()
car_corner2 = pygeo.ECEFToLLA(
(vehicle_ecef - car_corner_offset1).T).T.flatten()
car_corner3 = pygeo.ECEFToLLA(
(vehicle_ecef + car_corner_offset2).T).T.flatten()
car_corner4 = pygeo.ECEFToLLA(
(vehicle_ecef - car_corner_offset2).T).T.flatten()
pg_corner1 = func.ST_SetSRID(
func.ST_MakePoint(car_corner1[1], car_corner1[0], car_corner1[2]),
4326)
pg_corner2 = func.ST_SetSRID(
func.ST_MakePoint(car_corner2[1], car_corner2[0], car_corner2[2]),
4326)
pg_corner3 = func.ST_SetSRID(
func.ST_MakePoint(car_corner3[1], car_corner3[0], car_corner3[2]),
4326)
pg_corner4 = func.ST_SetSRID(
func.ST_MakePoint(car_corner4[1], car_corner4[0], car_corner4[2]),
4326)
collection = func.ST_Collect(pg_corner1, pg_corner2)
collection = func.ST_Collect(collection, pg_corner3)
collection = func.ST_Collect(collection, pg_corner4)
car_polygon = func.ST_ConvexHull(collection)
camera_ecef = pygeo.LLAToECEF(camera_lla.T).T
vehicle_ecef = pygeo.LLAToECEF(vehicle_lla.T).T
diff = camera_ecef - vehicle_ecef
normalized = diff / numpy.linalg.norm(diff)
vehicle_enu = pygeo.LLAToENU(vehicle_lla.T).reshape((3, 3))
rotated = vehicle_enu.T.dot(normalized)
theta = func.acos(rotated[2][0])
view_phi = func.atan2(rotated[1][0], rotated[0][0])
vehicle_phi = math.radians(-labeler_vehicle.theta)
phi = vehicle_phi - view_phi
out = nyc3dcars_session.query(
theta.label('theta'),
phi.label('phi')) \
.one()
out.phi = ((out.phi + math.pi) % (2 * math.pi)) - math.pi
out.theta = ((out.theta + math.pi) % (2 * math.pi)) - math.pi
view_phi = out.phi
view_theta = out.theta
left = labeler_vehicle.x1
right = labeler_vehicle.x2
top = labeler_vehicle.y1
bottom = labeler_vehicle.y2
for bbox_session in labeler_vehicle.bbox_sessions:
if not bbox_session.user.trust:
continue
print((bbox_session.user.username,
labeler_vehicle.revision.annotation.pid))
left = bbox_session.x1
right = bbox_session.x2
top = bbox_session.y1
bottom = bbox_session.y2
break
occlusions = [
occlusion.category for occlusion in labeler_vehicle.occlusionrankings
if occlusion.occlusion_session.user.trust and occlusion.category != 5
]
if len(occlusions) == 0:
return
pg_lla = func.ST_SetSRID(
func.ST_MakePoint(vehicle_lla[1][0], vehicle_lla[0][0],
vehicle_lla[2][0]), 4326)
nyc3dcars_vehicle = Vehicle(
id=labeler_vehicle.id,
pid=photo.id,
x=labeler_vehicle.x,
z=labeler_vehicle.z,
theta=labeler_vehicle.theta,
x1=left,
x2=right,
y1=top,
y2=bottom,
type_id=model.id,
occlusion=min(occlusions),
geom=car_polygon,
lla=pg_lla,
view_theta=view_theta,
view_phi=view_phi,
cropped=labeler_vehicle.cropped,
)
nyc3dcars_session.add(nyc3dcars_vehicle)
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 compute_car_pose(photo, bbox, angle, vehicle_types):
"""Compute 3D pose for 2D bounding box."""
camera_rotation = numpy.array([[photo.r11, photo.r12, photo.r13],
[photo.r21, photo.r22, photo.r23],
[photo.r31, photo.r32, photo.r33]])
camera_position = - \
camera_rotation.T.dot([[photo.t1], [photo.t2], [photo.t3]])
# Small correction factor computed from NYC3DCars annotation results.
dataset_correction = numpy.array([
[photo.dataset.t1],
[photo.dataset.t2],
[photo.dataset.t3],
])
camera_position += dataset_correction
# Just approximate it for this first calculation and correct it later.
vehicle_height = 1.445
det_focal = photo.focal
det_height = photo.height
det_width = photo.width
det_bottom = bbox.y2 * det_height
det_top = bbox.y1 * det_height
det_middle = (bbox.x1 + bbox.x2) / 2 * det_width
new_dir = numpy.array([[(det_middle - det_width / 2) / det_focal],
[(det_height / 2 - det_bottom) / det_focal],
[-1]])
distance = vehicle_height / ((det_height / 2 - det_top) / det_focal - (
det_height / 2 - det_bottom) / det_focal)
car_position_wrt_camera = distance * new_dir
car_position = camera_rotation.T.dot(car_position_wrt_camera)
car_ecef = car_position + camera_position
car_lla = pygeo.ECEFToLLA(car_ecef.T)
car_enu = pygeo.LLAToENU(car_lla).reshape((3, 3))
middle_x = (bbox.x1 + bbox.x2) / 2
middle_y = (bbox.y1 + bbox.y2) / 2
left_ray = numpy.array(
[[(bbox.x1 * photo.width - det_width / 2) / det_focal],
[(det_height / 2 - middle_y * photo.height) / det_focal],
[-1]])
left_ray_enu = car_enu.T.dot(camera_rotation.T.dot(left_ray))
right_ray = numpy.array(
[[(bbox.x2 * photo.width - det_width / 2) / det_focal],
[(det_height / 2 - middle_y * photo.height) / det_focal],
[-1]])
right_ray_enu = car_enu.T.dot(camera_rotation.T.dot(right_ray))
middle_ray = numpy.array(
[[(middle_x * photo.width - det_width / 2) / det_focal],
[(det_height / 2 - middle_y * photo.height) / det_focal],
[-1]])
middle_ray_enu = car_enu.T.dot(camera_rotation.T.dot(middle_ray))
top_ray = numpy.array(
[[(middle_x * photo.width - det_width / 2) / det_focal],
[(det_height / 2 - bbox.y1 * photo.height) / det_focal],
[-1]])
top_ray_enu = car_enu.T.dot(camera_rotation.T.dot(top_ray))
bottom_ray = numpy.array(
[[(middle_x * photo.width - det_width / 2) / det_focal],
[(det_height / 2 - bbox.y2 * photo.height) / det_focal],
[-1]])
bottom_ray_enu = car_enu.T.dot(camera_rotation.T.dot(bottom_ray))
middle_angle = math.atan2(middle_ray_enu[1], middle_ray_enu[0])
right_angle = math.atan2(right_ray_enu[1], right_ray_enu[0])
left_angle = math.atan2(left_ray_enu[1], left_ray_enu[0])
if not angle:
total_angle = middle_angle
else:
total_angle = middle_angle + angle
for vehicle_type in vehicle_types:
half_width = 0.3048 * vehicle_type.tight_width / 2
half_length = 0.3048 * vehicle_type.tight_length / 2
height = 0.3048 * vehicle_type.tight_height
pointa = numpy.array([[half_width], [half_length]])
pointb = numpy.array([[half_width], [-half_length]])
pointc = numpy.array([[-half_width], [-half_length]])
pointd = numpy.array([[-half_width], [half_length]])
half_pi = math.pi / 2
if in_range(total_angle, right_angle, left_angle):
left = pointd
right = pointc
elif in_range(total_angle, left_angle, half_pi + right_angle):
left = pointa
right = pointc
elif in_range(total_angle, half_pi + right_angle, left_angle + half_pi):
left = pointa
right = pointd
elif in_range(total_angle, left_angle + half_pi, right_angle + math.pi):
left = pointb
right = pointd
elif in_range(total_angle, right_angle + math.pi, left_angle + math.pi):
left = pointd
right = pointa
elif in_range(total_angle, left_angle + math.pi, 3 * half_pi + right_angle):
left = pointc
right = pointa
elif in_range(total_angle, 3 * half_pi + right_angle, left_angle + 3 * half_pi):
left = pointc
right = pointb
elif in_range(total_angle, left_angle + 3 * half_pi, right_angle):
left = pointd
right = pointb
else:
raise Exception('Invalid angle???')
rot = numpy.array([
[math.cos(total_angle), -math.sin(total_angle)],
[math.sin(total_angle), math.cos(total_angle)],
])
left_rot = rot.dot(left)
right_rot = rot.dot(right)
A = numpy.array([
[left_ray_enu[1][0], -left_ray_enu[0][0]],
[right_ray_enu[1][0], -right_ray_enu[0][0]],
])
b = numpy.array([
[-left_rot[0][0] * left_ray_enu[1][0]
+ left_rot[1][0] * left_ray_enu[0][0]],
[-right_rot[0][0] * right_ray_enu[1][0]
+ right_rot[1][0] * right_ray_enu[0][0]],
])
x = numpy.linalg.solve(A, b)
a_rot = rot.dot(pointa)
b_rot = rot.dot(pointb)
c_rot = rot.dot(pointc)
d_rot = rot.dot(pointd)
distance = numpy.linalg.norm(x)
bottom_point = distance * bottom_ray_enu / \
numpy.linalg.norm(bottom_ray_enu)
left_right_position = numpy.array([
x[0],
x[1],
bottom_point[2],
])
A = numpy.hstack((top_ray_enu, -bottom_ray_enu))
b = numpy.array([[0], [0], [height]])
x = numpy.linalg.solve(A.T.dot(A), A.T.dot(b))
assert x[0][0] > 0
assert x[1][0] > 0
bottom_point = x[1][0] * bottom_ray_enu
bottom_point = (bottom_point + left_right_position) / 2
position1 = numpy.array([
[bottom_point[0][0] + a_rot[0][0]],
[bottom_point[1][0] + a_rot[1][0]],
[bottom_point[2][0]],
])
position2 = numpy.array([
[bottom_point[0][0] + b_rot[0][0]],
[bottom_point[1][0] + b_rot[1][0]],
[bottom_point[2][0]],
])
position3 = numpy.array([
[bottom_point[0][0] + c_rot[0][0]],
[bottom_point[1][0] + c_rot[1][0]],
[bottom_point[2][0]],
])
position4 = numpy.array([
[bottom_point[0][0] + d_rot[0][0]],
[bottom_point[1][0] + d_rot[1][0]],
[bottom_point[2][0]],
])
ecef1 = car_enu.dot(position1) + camera_position
ecef2 = car_enu.dot(position2) + camera_position
ecef3 = car_enu.dot(position3) + camera_position
ecef4 = car_enu.dot(position4) + camera_position
lla1 = pygeo.ECEFToLLA(ecef1.T).flatten()
lla2 = pygeo.ECEFToLLA(ecef2.T).flatten()
lla3 = pygeo.ECEFToLLA(ecef3.T).flatten()
lla4 = pygeo.ECEFToLLA(ecef4.T).flatten()
pglla1 = func.ST_SetSRID(
func.ST_MakePoint(lla1[1], lla1[0], lla1[2]), 4326)
pglla2 = func.ST_SetSRID(
func.ST_MakePoint(lla2[1], lla2[0], lla2[2]), 4326)
pglla3 = func.ST_SetSRID(
func.ST_MakePoint(lla3[1], lla3[0], lla3[2]), 4326)
pglla4 = func.ST_SetSRID(
func.ST_MakePoint(lla4[1], lla4[0], lla4[2]), 4326)
collected = func.ST_Collect(pglla1, pglla2)
collected = func.ST_Collect(collected, pglla3)
collected = func.ST_Collect(collected, pglla4)
geom = func.ST_ConvexHull(collected)
world = car_enu.dot(bottom_point) + camera_position
lla = pygeo.ECEFToLLA(world.T).flatten()
pglla = func.ST_SetSRID(
func.ST_MakePoint(lla[1], lla[0], lla[2]), 4326)
yield pglla, geom, vehicle_type, total_angle
def add_airspace(self, country_code, airspace_class, name, base, top, geom_str):
try:
geom = loads(geom_str)
except ReadingError:
print name + "(" + airspace_class + ") is not a polygon (maybe not enough points?)"
return False
# orient polygon clockwise
geom = polygon.orient(geom, sign=-1)
if not airspace_class:
print name + " has no airspace class"
return False
base = self.normalise_height(base, name)
top = self.normalise_height(top, name)
flightlevel_re = re.compile(r'^FL (\d+)$')
match = flightlevel_re.match(base)
if match and int(match.group(1)) >= 200:
print name + " has it's base above FL 200 and is therefore disregarded"
return False
airspace = Airspace()
airspace.country_code = country_code
airspace.airspace_class = airspace_class
airspace.name = name
airspace.base = base
airspace.top = top
# Check geometry type, disregard everything except POLYGON
if geom.geom_type != 'Polygon':
print name + " is not a polygon (it's a " + geom.geom_type + ")"
return False
wkb = from_shape(geom, srid=4326)
# Try to fix invalid (self-intersecting) geometries
valid_dump = (func.ST_Dump(func.ST_MakeValid(wkb))).geom
valid_query = db.session.query(func.ST_SetSRID(valid_dump, 4326)).order_by(func.ST_Area(valid_dump).desc()).first()
if not valid_query:
print 'Error importing ' + name
print 'Could not validate geometry'
return False
else:
wkb = valid_query[0]
geom_type = db.session.query(func.ST_GeometryType(wkb)).first()[0]
if geom_type != 'ST_Polygon':
print name + " got some errors makeing it valid..."
return False
tolerance = 0.0000001
simplify = lambda x: func.ST_SimplifyPreserveTopology(x, tolerance)
airspace.the_geom = case(
[
(func.ST_IsValid(wkb), wkb),
(func.ST_IsValid(simplify(wkb)), simplify(wkb)),
],
else_=None)
db.session.add(airspace)
return True
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]
def _null_malformed_geoms(existing):
# We decide to set the geom to NULL when the given lon/lat is (0,0)
# (off the coast of Africa).
upd = existing.update().values(geom=None).\
where(existing.c.geom == select([func.ST_SetSRID(func.ST_MakePoint(0, 0), 4326)]))
postgres_engine.execute(upd)
def geojson_to_geo(self, geometry: Dict) -> object:
return self.session.query(
func.ST_SetSRID(func.ST_GeomFromGeoJSON(json.dumps(geometry)),
4326).label('geom')).one().geom
