def merge_geog():
"""
Choose best precision between initial coordinates
or geocoded coordinates if geog is not set from
cadastre information
"""
# Input dataset
basol_geocoded = Dataset("etl", "basol_normalized")
# Output dataset
basol_geog_merged = Dataset("etl", "basol_geog_merged")
basol_geog_merged.write_dtype([
*basol_geocoded.read_dtype(),
Column("geog", Geometry(srid=4326)),
Column("geog_precision", String),
Column("geog_source", String)
])
BasolGeocoded = basol_geocoded.reflect()
session = basol_geocoded.get_session()
point_lambert2 = func.ST_Transform(
func.ST_setSRID(
func.ST_MakePoint(BasolGeocoded.coordxlambertii,
BasolGeocoded.coordylambertii), LAMBERT2), WGS84)
point_geocoded = func.ST_setSRID(
func.ST_MakePoint(BasolGeocoded.geocoded_longitude,
BasolGeocoded.geocoded_latitude), WGS84)
q = session.query(BasolGeocoded, point_lambert2, point_geocoded).all()
with basol_geog_merged.get_writer() as writer:
for (row, point_lambert2, point_geocoded) in q:
output_row = {
**row2dict(row), "geog": None,
"geog_precision": None,
"geog_source": None
}
if row.l2e_precision == precisions.HOUSENUMBER:
output_row["geog"] = point_lambert2
output_row["geog_precision"] = row.l2e_precision
output_row["geog_source"] = "lambert2"
elif (row.geocoded_result_type == precisions.HOUSENUMBER) and \
(row.geocoded_result_score >= 0.6):
output_row["geog"] = point_geocoded
output_row["geog_precision"] = row.geocoded_result_type
output_row["geog_source"] = "geocodage"
writer.write_row_dict(output_row)
session.close()
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_dataset_bounds_query(md_type):
if 'lat' not in md_type.dataset_fields:
# Not a spatial product
return None
lat, lon = md_type.dataset_fields['lat'], md_type.dataset_fields['lon']
assert isinstance(lat, RangeDocField)
assert isinstance(lon, RangeDocField)
return func.ST_MakeBox2D(func.ST_MakePoint(lat.lower.alchemy_expression,
lon.lower.alchemy_expression),
func.ST_MakePoint(lat.greater.alchemy_expression,
lon.greater.alchemy_expression),
type_=Geometry)
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 get_city(self, lat, lng):
"""
Get nearby city for this lat and long
:param lat: Latitude
:param lng: Longitude
:return: City object
"""
point = func.ST_MakePoint(lng, lat)
city = self.session.query(
Cities.id,
Cities.name,
Cities.state_id,
State.name.label('state_name'),
State.short_name.label('state_short_name'),
State.slug.label('state_slug'),
Cities.slug,
Cities.lat,
Cities.lng,
# Cities.geom,
func.ST_Distance_Sphere(point, Cities.__table__.c.geom).label(
'distance')).join(State).order_by(
func.ST_Distance_Sphere(
point, Cities.__table__.c.geom)).limit(1).first()
return city
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 __init__(self,
name,
slug,
state_id=None,
lat=None,
lng=None,
geom=None):
"""
:param name: City name
:param slug: Slug
:param state_id: State
:param lat: Latitude (double)
:param lng: Longitude (double)
:param geom: Geometry from latitude and longitude
"""
self.name = name
self.slug = slug
self.state_id = state_id
self.lat = lat
self.lng = lng
# Geom point type
if geom is None:
self.geom = func.ST_MakePoint(self.lng, self.lat)
else:
self.geom = geom
def get_stadium_groups_by_points(points: List[PointDC]):
return db.session.query(StadiumGroup) \
.filter(func.ST_Contains(func.ST_MakePolygon(func.ST_GeomFromText(
f"LINESTRING("
f"{points[0].lat} {points[0].long}, "
f"{points[1].lat} {points[1].long}, "
f"{points[2].lat} {points[2].long}, "
f"{points[3].lat} {points[3].long}, "
f"{points[0].lat} {points[0].long} "
f")"
)), func.ST_MakePoint(StadiumGroup.lat, StadiumGroup.long))).all()
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 _get_srid_point(longitude, latitude):
"""
Make a point at the given longitude and latitude with the SRID provided in the environment.
Uses ST_SetSrid and ST_MakePoint.
Assumption: There is a POSTGIS_GEOM_SRID field in the system environment (os.environ).
:param longitude: the longitude of the point
:param latitude: the latitude of the point
:return: a point at the given longitude and latitude with srid in the environment
"""
return func.ST_SetSrid(func.ST_MakePoint(longitude, latitude),
int(os.environ['POSTGIS_GEOM_SRID']))
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 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 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 _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 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 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 _gis_point(doc, doc_offset):
return func.ST_MakePoint(
doc[doc_offset + ['x']].astext.cast(postgres.DOUBLE_PRECISION),
doc[doc_offset + ['y']].astext.cast(postgres.DOUBLE_PRECISION))
def merge_geog():
"""
Fusionne les infomations géographiques et produit
les champs normalisés geog, geog_precision, geog_source
On choisit de privilégier les informations de géocodage
lorsque la précision initiale est MUNICIPALITY et que
le score de géocodage est supérieur à 0.6
"""
# input dataset
s3ic_normalized = Dataset("etl", "s3ic_normalized")
# output dataset
s3ic_merged = Dataset("etl", "s3ic_geog_merged")
dtype = s3ic_normalized.read_dtype()
# geom => geog
# lib_precis => precision
dtype = [c for c in dtype if c.name not in ("geom", "lib_precis")]
output_dtype = [
*dtype,
Column("geog", Geometry(srid=4326)),
Column("geog_precision", String),
Column("geog_source", String)
]
s3ic_merged.write_dtype(output_dtype)
S3icNormalized = s3ic_normalized.reflect()
session = s3ic_normalized.get_session()
point_geocoded = func.ST_setSRID(
func.ST_MakePoint(S3icNormalized.geocoded_longitude,
S3icNormalized.geocoded_latitude), WGS84)
q = session.query(S3icNormalized, point_geocoded).all()
with s3ic_merged.get_writer() as writer:
for (row, point) in q:
output_row = {
**row2dict(row), "geog": row.geog,
"geog_precision": row.precision,
"geog_source": "initial_data"
}
c1 = row.precision not in \
(precisions.HOUSENUMBER, precisions.PARCEL)
c2 = row.geocoded_latitude and row.geocoded_longitude
c3 = row.geocoded_result_score and row.geocoded_result_score > 0.6
c4 = row.geocoded_result_type == precisions.HOUSENUMBER
if c1 and c2 and c3 and c4:
output_row["geog"] = point
output_row["geog_precision"] = row.geocoded_result_type
output_row["geog_source"] = "geocodage"
writer.write_row_dict(output_row)
session.close()
def merge_geog():
"""
Sélectionne la meilleure information géographique
entre
- (x_lambert2_etendue, y_lambert2_etendue)
- (xl2_adresse, yl2_adresse)
- point géocodé
Ajoute la précision associée et la source de l'info
"""
# Input dataset
basias_localisation_geocoded = Dataset("etl",
"basias_localisation_geocoded")
# Output dataset
basias_localisation_geog_merged = Dataset(
"etl", "basias_localisation_geog_merged")
basias_localisation_geog_merged.write_dtype([
*basias_localisation_geocoded.read_dtype(),
Column("geog", Geometry(srid=4326)),
Column("geog_precision", String),
Column("geog_source", String)
])
BasiasLocalisationGeocoded = basias_localisation_geocoded.reflect()
session = basias_localisation_geocoded.get_session()
point_lambert2_etendue = func.ST_Transform(
func.ST_setSRID(
func.ST_MakePoint(BasiasLocalisationGeocoded.x_lambert2_etendue,
BasiasLocalisationGeocoded.y_lambert2_etendue),
LAMBERT2), WGS84)
point_adresse = func.ST_Transform(
func.ST_setSRID(
func.ST_MakePoint(BasiasLocalisationGeocoded.xl2_adresse,
BasiasLocalisationGeocoded.yl2_adresse),
LAMBERT2), WGS84)
point_geocoded = func.ST_setSRID(
func.ST_MakePoint(BasiasLocalisationGeocoded.geocoded_longitude,
BasiasLocalisationGeocoded.geocoded_latitude), WGS84)
q = session.query(BasiasLocalisationGeocoded, point_lambert2_etendue,
point_adresse, point_geocoded).all()
with basias_localisation_geog_merged.get_writer() as writer:
for (row, p_lambert2_etendue, p_adresse, p_geocoded) in q:
output_row = {
**row2dict(row), "geog": None,
"geog_precision": None,
"geog_source": None
}
if p_lambert2_etendue is not None:
# assert this data is accurate
output_row["geog"] = p_lambert2_etendue
output_row["geog_precision"] = precisions.PARCEL
output_row["geog_source"] = "lambert2_etendue"
elif p_adresse is not None and row.precision_adresse == "numéro":
output_row["geog"] = p_adresse
output_row["geog_precision"] = precisions.HOUSENUMBER
output_row["geog_source"] = "adresse"
elif p_geocoded is not None and row.geocoded_result_score > 0.6 \
and row.geocoded_result_type == precisions.HOUSENUMBER:
output_row["geog"] = p_geocoded
output_row["geog_precision"] = precisions.HOUSENUMBER
output_row["geog_source"] = "geocodage"
writer.write_row_dict(output_row)
session.close()