def persistRun(runID, filename):
result = db.session.query( func.ST_X(Prediction.location), func.ST_Y(Prediction.location), Prediction.certainty ).filter(Prediction.precogrun == runID)
data = pd.read_sql( result.statement , result.session.bind)
geojson = geojsonConvert_Predictions(data)
file_path = os.path.join("/app/app/static", filename + ".geojson")
with codecs.open( file_path, 'w', encoding="utf8") as fo:
fo.write(geojson)
def persist_recommendations():
result = db.session.query(func.ST_X(Recommendation.location),
func.ST_Y(Recommendation.location),
Recommendation.recommendation, Recommendation.id)
data = pd.read_sql(result.statement, result.session.bind)
json = geojsonConvert_Recommendation(data)
file_path = os.path.join("/app/app/static", "recommendations.geojson")
with codecs.open(file_path, 'w', encoding="utf8") as fo:
fo.write(json)
def filter_within_radius(self, lat, lng, radius):
"""
Filter user within radius from a center (lat, lng) coordinate
"""
# Define center point
point = 'POINT(%f %f)' % (lng, lat)
wkb_element = WKTElement(point, srid=4326)
# Define expression to calculate distance
# from center point to users location
if db.engine.name == 'sqlite':
distance = func \
.distance(User.location, wkb_element, 1) \
.label('distance')
else:
distance = User.location \
.distance_centroid(wkb_element) \
.cast(db.Float) \
.label('distance')
# Define lat, lng query set
lat = func.ST_Y(User.location).label('lat')
lng = func.ST_X(User.location).label('lng')
# Filter user within radius from center point
if db.engine.name == 'sqlite':
qs = User.query.filter(
func.PtDistWithin(User.location, wkb_element, radius))
else:
qs = User.query.filter(
func.ST_DWithin(User.location, wkb_element, radius))
# Append Query-time SQL expressions distance as mapped attributes
# https://docs.sqlalchemy.org/en/latest/orm/mapped_sql_expr.html
qs = qs.options(with_expression(User.distance, distance),
with_expression(User.lat, lat),
with_expression(User.lng, lng))
return qs
def update_heights(table):
query = db.session.query(func.ST_X(table.geometry),
func.ST_Y(table.geometry), table.osm_id)
for res in query:
x = res[0]
y = res[1]
transformer = Transformer.from_crs("epsg:3857", "epsg:4326")
x, y = transformer.transform(x, y)
print(x, y)
try:
url = ELEVATION_SERVICE_URL + "?lat=" + str(x) + "&lon=" + str(
y) + ""
with requests.get(url) as response:
heightdict = response.json()
# Set height to 0 if a HTTPError occurs
except Exception as e:
print(e)
heightdict = {"geometry": {"coordinates": [0, 0, 0]}}
result = db.session.query(table).filter(table.osm_id == res[2]).first()
result.height = heightdict["geometry"]["coordinates"][2]
db.session.commit()
class Cells(Base):
__tablename__ = "cells"
__table_args__ = {"schema": "infrastructure"}
cell_id = Column("id", Text, primary_key=True)
version = Column("version", Text, primary_key=True)
site_id = Column(Text)
geom_point = Column(Geometry("POINT"))
select_stmt = select([
Cells.cell_id.label("cell_id"),
Cells.version,
Cells.site_id,
func.ST_X(Cells.geom_point).label("lon"),
func.ST_Y(Cells.geom_point).label("lat"),
])
df_cells = pd.read_sql(select_stmt, engine)
# Pick only the latest version for each cell
df_cells = df_cells.loc[df_cells.groupby("cell_id")["version"].idxmax("max")]
df_cells = df_cells.reset_index(drop=True)
cells = list(df_cells.itertuples(index=False, name="Cell"))
print(f"Done.\nFound {len(cells)} cells.")
#
# Create list of transaction types and store them in dfs.transaction_types.
#
# Note that we insert the `transtype_id` column manually here; in a real ETL
# process this would be inserted automatically by Postgres as a serial id.