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 _transform_to_meter_srid(original_point): """ Transforms the original point to a new point using the METER_UNIT_SRID. :param original_point: the point to be transformed :return: original point with srid transformed to METER_UNIT_SRID """ return func.ST_Transform(original_point, METER_UNIT_SRID)
def get_labels(session, score, detection_filters, vehicle_filters, model, threshold): """Retrieves all possible detection-annotation pairings that satify the VOC criterion.""" overlap_score = overlap(Detection, Vehicle) # pylint: disable-msg=E1101 dist_x = (func.ST_X(func.ST_Transform(Detection.lla, 102718)) - func.ST_X(func.ST_Transform(Vehicle.lla, 102718))) \ * 0.3048 dist_y = (func.ST_Y(func.ST_Transform(Detection.lla, 102718)) - func.ST_Y(func.ST_Transform(Vehicle.lla, 102718))) \ * 0.3048 dist = func.sqrt(dist_x * dist_x + dist_y * dist_y) height_diff = func.abs( func.ST_Z(Detection.lla) - func.ST_Z(Vehicle.lla)) labels = session.query( overlap_score.label('overlap'), Vehicle.id.label('vid'), Detection.id.label('did'), dist.label('dist'), height_diff.label('height_diff'), score.label('score')) \ .select_from(Detection) \ .join(Photo) \ .join(Vehicle) \ .join(Model) \ .filter(Model.filename == model) \ .filter(Photo.test == True) \ .filter(overlap_score > 0.5) \ .filter(score > threshold) # pylint: enable-msg=E1101 for query_filter in detection_filters: labels = labels.filter(query_filter) for query_filter in vehicle_filters: labels = labels.filter(query_filter) labels = labels.order_by(desc(overlap_score)).all() return labels
def column_expression(self, col): """The column_expression() method is overrided to ensure that the SRID of the resulting WKBElement is correct""" return getattr( func, self.impl.as_binary )(func.ST_Transform(col, self.app_srid), type_=self.__class__.impl(srid=self.app_srid) # srid could also be -1 so that the SRID is deduced from the # WKB data )
def get_by_sujeto(db_session: Session, *, sujeto: SujetoInDB) -> Optional[Location]: if sujeto.latlng is not None: location = db_session.query(Location).filter( func.ST_Contains( func.ST_Buffer(func.ST_Transform(Location.center, 32631), Location.radius), func.ST_Transform( func.ST_GeomFromEWKT('SRID=4326;POINT({} {})'.format( *crud.coordinates.get_latlng_from_geom( sujeto.latlng)[::-1])), 32631))).order_by( func.ST_Transform(Location.center, 32631).ST_Buffer( Location.radius).ST_Area().asc()).first() if location is not None: return location.name else: return google.get_sujeto_place(sujeto) else: return None
class DimLocalidadRural(Base): __tablename__ = "dim_localidad_rural" __table_args__ = {'extend_existing': True} localidad_rural_geom_6362 = Column( Geometry(geometry_type='MULTIPOLYGON',srid = 6362), nullable = True) localidad_rural_geom_4326 = Column( Geometry(geometry_type='MULTIPOLYGON',srid = 4326), nullable = True) localidad_rural_geom_6362_json = column_property( func.ST_AsGeoJSON(func.ST_Transform(localidad_rural_geom_6362, 6362))) localidad_rural_geom_4326_json = column_property( func.ST_AsGeoJSON(func.ST_Transform(localidad_rural_geom_4326, 4326))) def to_dict(self): data = { 'localidad_rural_geom_6362_json': func.ST_AsGeoJSON( func.ST_Transform(self.localidad_rural_geom_6362)), 'localidad_rural_geom_4326_json': func.ST_AsGeoJSON( func.ST_Transform(self.localidad_rural_geom_4326)) } return data
class DimEntidad(Base): __tablename__ = "dim_entidad" __table_args__ = {'extend_existing': True} entidad_geom_6362 = Column( Geometry(geometry_type='MULTIPOLYGON',srid = 6362), nullable = True) entidad_geom_4326 = Column( Geometry(geometry_type='MULTIPOLYGON',srid = 4326), nullable = True) entidad_geom_6362_json = column_property( func.ST_AsGeoJSON(func.ST_Transform(entidad_geom_6362, 6362))) entidad_geom_4326_json = column_property( func.ST_AsGeoJSON(func.ST_Transform(entidad_geom_4326, 4326))) def to_dict(self): data = { 'entidad_geom_6362_json': func.ST_AsGeoJSON( func.ST_Transform(self.entidad_geom_6362)), 'entidad_geom_4326_json': func.ST_AsGeoJSON( func.ST_Transform(self.entidad_geom_4326)) } return data
def area_interseccion(ageb, poligono): # Geometría de la intersección del polígono de entrada con el ageb interseccion = session.scalar( ageb.geom.ST_Intersection(WKTElement(str(shape(poligono)), srid=4326))) # Convertimos la geometría de la intersección en formato WKTElement interseccion_wkte = WKTElement(str(to_shape(interseccion)), srid=4326) # Obtenemos el área de la geometría de la interseccion area_interseccion = session.scalar( select([func.ST_Area(func.ST_Transform(interseccion_wkte, 4486))])) return area_interseccion
def fetch_street_edges_intersecting(bounding_box: BoundingBox): """Fetch all the polygons intersecting the given rectangular bounding box Reference http://geoalchemy-2.readthedocs.io/en/0.3/orm_tutorial.html#spatial-query """ envelope_geom = StreetEdge._envelope(bounding_box, geom=True) query = session.query(StreetEdge).filter( func.ST_Intersects(func.ST_Transform(StreetEdge.geom, 4326), envelope_geom)) return query
def _value(self): if self.raw_data: return self.raw_data[0] if type(self.data) is geoalchemy2.elements.WKBElement: if self.srid is -1: self.data = self.session.scalar(func.ST_AsGeoJson(self.data)) else: self.data = self.session.scalar( func.ST_AsGeoJson( func.ST_Transform(self.data, self.web_srid))) return super(GeoJSONField, self)._value()
def process_formdata(self, valuelist): super(GeoJSONField, self).process_formdata(valuelist) if str(self.data) is '': self.data = None if self.data is not None: web_shape = self.session.scalar( func.ST_AsText( func.ST_Transform( func.ST_GeomFromText( shape(self.data).wkt, self.web_srid), self.transform_srid))) self.data = 'SRID=' + str(self.srid) + ';' + str(web_shape)
def _value(self): if self.raw_data: return self.raw_data[0] if type(self.data) is geoalchemy2.elements.WKBElement: if self.srid == -1: return self.session.scalar(func.ST_AsGeoJSON(self.data)) else: return self.session.scalar( func.ST_AsGeoJSON( func.ST_Transform(self.data, self.web_srid))) else: return ''
def generate_grids(config, area=None): bounding_box = WKTElement(config['BOUNDING_BOX'], srid=4326) grid_obj = config['GRID_OBJ'] resolution = config['RESOLUTION'] epsg = config['EPSG'] try: grids = session.query(func.ST_Dump( func.makegrid_2d(bounding_box, resolution, resolution)).geom.label('geom') # self-defined function in Psql ).subquery() # using the boundary to crop the area # if config['AREA'] == 'los_angeles': # grids = session.query(grids.c.geom) \ # .filter(func.ST_Intersects(LosAngelesCountyBoundary.wkb_geometry, grids.c.geom)).subquery() results = session.query( func.row_number().over().label('gid'), func.ST_Centroid(grids.c.geom).label('centroid'), func.ST_X(func.ST_Centroid(grids.c.geom)).label('lon'), func.ST_Y(func.ST_Centroid(grids.c.geom)).label('lat'), grids.c.geom, func.ST_X(func.ST_Transform(func.ST_Centroid(grids.c.geom), epsg)).label('lon_proj'), func.ST_Y(func.ST_Transform(func.ST_Centroid(grids.c.geom), epsg)).label('lat_proj')).all() obj_results = [] for res in results: obj_results.append(grid_obj(gid=res[0], centroid=res[1], lon=res[2], lat=res[3], geom=res[4], lon_proj=res[5], lat_proj=res[6])) # session.add_all(obj_results) # session.commit() return except Exception as e: print(e) exit(-1)
def make_selectables(cls, table, rel_table): fields = [table.c[col] for col in cls._columns if col in table.c] if 'level' not in table.c and 'piste' in table.c: fields.append(table.c.piste) fields.append( func.ST_Length2dSpheroid( func.ST_Transform(table.c.geom, 4326), 'SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]]' ).label("length")) fields.append(table.c.geom.ST_Envelope().label('bbox')) fields.append(rel_table.c.tags) return fields
def points_to_projected_line(cls, line): '''Acts as a wrapper around a PostGIS Geometry constructor. Args: cls (SpatialQueries): Class object line (tuple): Tuple of geographic coordinate pairs. Returns: Geometry: A postgis Geometry object ''' return func.ST_Transform( func.ST_GeometryFromText(cls.construct_linestring_string(settings.TARGET_DATUM, line)), settings.TARGET_PROJECTION )
def segmentized_line_with_geographic_points(cls, trip_id): ''' Returns (lat, lon) of geographic coordinate of points every 50m along route. Mainly intended for use with testing.''' s = select([ func.ST_DumpPoints(func.ST_Transform(func.ST_Segmentize(Trip.geom_path, 50), settings.TARGET_DATUM))\ .label('dp') ]).where(Trip.trip_id == trip_id) inner_select = s.correlate(None).alias() s = select([ func.ST_Y(inner_select.columns.dp.geom), func.ST_X(inner_select.columns.dp.geom), ]) return list(engine.execute(s))
def fetch_polygons_intersecting(bounding_box: BoundingBox): """Fetch all the polygons intersecting the given rectangular bounding box Reference http://geoalchemy-2.readthedocs.io/en/0.3/orm_tutorial.html#spatial-query """ envelope = """POLYGON(( %f %f, %f %f, %f %f, %f %f, %f %f ))""" % ( bounding_box.west, bounding_box.south, bounding_box.west, bounding_box.north, bounding_box.east, bounding_box.north, bounding_box.east, bounding_box.south, bounding_box.west, bounding_box.south) envelope_geom = func.ST_GeomFromText(envelope, 4326) query = session.query(SidewalkPolygon).filter( func.ST_Intersects(func.ST_Transform(SidewalkPolygon.geom, 4326), envelope_geom)) return query
def roadbed_query(session, detection): """Find roadbeds that intersect the detection's footprint.""" car_lla = detection.lonlat # pylint: disable-msg=E1101 roadbeds4326 = func.ST_Transform(Roadbed.geom, 4326) car_roadbed_dist = func.ST_Distance(roadbeds4326, car_lla) query = session.query( car_roadbed_dist, Roadbed.gid) \ .filter(func.ST_Intersects(car_lla, roadbeds4326)) \ .order_by(car_roadbed_dist.asc()) # pylint: enable-msg=E1101 roadbed = query.first() return roadbed
def convert_geographic_coordinates_to_projected_point(cls, lat, lon): '''Returns a projected point from geographic coordinates. Args: cls (SpatialQueries): Class object lat (float): latitude of geographic coordinate lon (float): longitude of geographic coordinate Returns: This returns a query object, that when executed will return the desired projected point. ''' point_string = cls.point_to_string(lat, lon) return func.ST_Transform( func.ST_GeometryFromText(point_string, settings.TARGET_DATUM), settings.TARGET_PROJECTION )
def intersect_grids(geom_expr, geom_tables: List[Table]): """Generate Intersection Query Conditions with Grid Tables.""" sub_where = [] outer_join = [] for geom_table in geom_tables: if not hasattr(geom_table.c, "tile"): break get_srid_expr = func.ST_SRID(geom_table.c.geom) sub_where.append( and_( func.ST_Intersects(func.ST_Transform(geom_expr, get_srid_expr), geom_table.c.geom), Tile.name == geom_table.c.tile, )) outer_join.append((geom_table, [Tile.name == geom_table.c.tile])) return [or_(*sub_where)], outer_join
def convert_projected_point_to_geographic_coordinates(cls, point): '''Returns a tuple of (latitude, longitude) coordinates Args: cls (SpatialQueries): Class object point (geometry): PostGIS Geometry type of a projected point Returns: tuple of (latitude, longitude) coordinates ''' s = select([ func.ST_Transform(func.ST_WKBToSQL(point), settings.TARGET_DATUM).label('p') ]) inner_select = s.correlate(None).alias() s = select([ func.ST_Y(inner_select.columns.p), func.ST_X(inner_select.columns.p), ]) return list(engine.execute(s))[0]
def test_postgis_transformation(self): from sqlalchemy import func v = self.session.query(func.ST_AsText( func.ST_Transform( func.ST_GeomFromText( ('POLYGON((' '743238 2967416,' '743238 2967450,' '743265 2967450,' '743265.625 2967416,' '743238 2967416))'), 2249), 4326))).one()[0] self.assertEqual(v, ( 'POLYGON((' '-71.1776848522251 42.3902896512902,' '-71.1776843766326 42.3903829478009,' '-71.1775844305465 42.3903826677917,' '-71.1775825927231 42.3902893647987,' '-71.1776848522251 42.3902896512902))' ))
def _value(self): if self.raw_data: return self.raw_data[0] if type(self.data) is geoalchemy2.elements.WKBElement: if self.srid is -1: return self.session.scalar( func.ST_AsGeoJson( self._flip_coordinates(self.data) ) ) else: return self.session.scalar( func.ST_AsGeoJson( self._flip_coordinates( func.ST_Transform(self.data, self.web_srid) ) ) ) else: return ''
def tree_api(): trees = db.session.query( Tree.id, Tree.loaicay, Tree.chieucao, func.ST_AsGeoJSON(func.ST_Transform(Tree.geom, 4326)).label('geometry')).all() tree_feature = [] for tree in trees: properties_temp = { "loaicay": tree.loaicay, "chieucao": tree.chieucao, "id": tree.id, } geometry_temp = json.loads(tree.geometry) feature = { "type": "Feature", "properties": properties_temp, "geometry": geometry_temp } tree_feature.append(feature) return jsonify({"features": tree_feature})
def get_dataset_footprint_region(self, dataset_id): """ Get the recorded WGS84 footprint and region code for a given dataset. Note that these will be None if the product has not been summarised. """ rows = self._engine.execute( select([ func.ST_Transform(DATASET_SPATIAL.c.footprint, 4326).label("footprint"), DATASET_SPATIAL.c.region_code, ]).where(DATASET_SPATIAL.c.id == dataset_id)).fetchall() if not rows: return None, None row = rows[0] footprint = row.footprint return ( to_shape(footprint) if footprint is not None else None, row.region_code, )
def coverage_query(session, detection): """Computes the percentage of the vehicles on the roadbeds.""" # pylint: disable-msg=E1101 car_polygon = Detection.geom car_polygon102718 = func.ST_Transform(car_polygon, 102718) car_road_intersection = func.ST_Area( func.ST_Intersection(Roadbed.geom, car_polygon102718)) car_area = func.ST_Area(car_polygon102718) car_filter = func.ST_Intersects( Roadbed.geom, car_polygon102718) query = session.query( func.sum(car_road_intersection / car_area)) \ .filter(Detection.id == detection.id) \ .filter(car_filter) # pylint: enable-msg=E1101 coverage, = query.one() if coverage is None: coverage = 0 return coverage
def test_transform(self): self._create_one_point() # Query the point and check the result pt = session.query(Point).one() assert pt.id == 1 assert pt.raw_geom.srid == 4326 check_wkb(pt.raw_geom, 5, 45) assert pt.geom.srid == 4326 check_wkb(pt.geom, 5, 45) # Check that the data is correct in DB using raw query q = "SELECT id, ST_AsEWKT(geom) AS geom FROM point;" res_q = session.execute(q).fetchone() assert res_q.id == 1 assert res_q.geom == "SRID=2154;POINT(857581.899319668 6435414.7478354)" # Compare geom, raw_geom with auto transform and explicit transform pt_trans = session.query( Point, Point.raw_geom, func.ST_Transform(Point.raw_geom, 2154).label("trans")).one() assert pt_trans[0].id == 1 assert pt_trans[0].geom.srid == 4326 check_wkb(pt_trans[0].geom, 5, 45) assert pt_trans[0].raw_geom.srid == 4326 check_wkb(pt_trans[0].raw_geom, 5, 45) assert pt_trans[1].srid == 4326 check_wkb(pt_trans[1], 5, 45) assert pt_trans[2].srid == 2154 check_wkb(pt_trans[2], 857581.89932, 6435414.74784)
def db_import_zones(self): '''Import zones.csv''' assert self.models_id is not None zone_wkt = self._get_zone_shapes() # Parse zone file with open(self.zones_csv) as f: r = csv.reader(f, delimiter=';', quoting=csv.QUOTE_NONNUMERIC) next(r) # skip header models_id = self.models_id srid = self.srid values = ({ 'models_id': models_id, 'id': int(row[0]), 'data': tuple(row[1:]), 'area': func.ST_Transform( func.ST_GeomFromText(zone_wkt[int(row[0])], srid), 900913) } for row in r) self._insert_with_limit(db.zones, values)
def building_api(): buildings = db.session.query( Building.id, Building.addr_house, Building.typeHouse, Building.floor, Building.square, func.ST_AsGeoJSON(func.ST_Transform(Building.geom, 4326)).label('geometry')).all() buidling_feature = [] for building in buildings: properties_temp = { "diaChi": building.addr_house, "loaiNha": building.typeHouse, "soTang": building.floor, "dienTich": building.square, "id": building.id, } geometry_temp = json.loads(building.geometry) feature = { "type": "Feature", "properties": properties_temp, "geometry": geometry_temp } buidling_feature.append(feature) return jsonify({"features": buidling_feature})
"dataset_type_ref", "center_time", ), # Faster region pages. Could be removed if faster summary generation is desired... Index( "dataset_spatial_dataset_type_ref_region_code_idx", "dataset_type_ref", "region_code", postgresql_ops={"region_code": "text_pattern_ops"}, ), ) DATASET_SPATIAL.indexes.add( Index( "dataset_spatial_footprint_wrs86_idx", func.ST_Transform(DATASET_SPATIAL.c.footprint, 4326), postgresql_using="gist", )) # An index matching the default Stac API Item search and its sort order. _COLLECTION_ITEMS_INDEX = Index( "dataset_spatial_collection_items_idx", "dataset_type_ref", "center_time", "id", # Stac API only returns datasets with a geometry -- it's mandatory in Stac Items. postgresql_where=DATASET_SPATIAL.c.footprint.isnot(None), _table=DATASET_SPATIAL, ) # An index matching the default return of '/stac/search' (ie, all collections.) _ALL_COLLECTIONS_ORDER_INDEX = Index( "dataset_spatial_all_collections_order_idx",