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