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",
