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()