def shops_for_users(db_session: Session, *, longitude: float,
latitude: float) -> Shop:
point_ewkt = f'SRID=4326;POINT({longitude} {latitude})'
shops = db_session.query(Shop).filter(
Shop.location.ST_DWithin(func.ST_GeogFromText(point_ewkt),
Shop.radius_metres))
return shops
def chunk(pickup_file):
session = db_connection.SESSIONMAKER()
with open(pickup_file, 'r') as pickup_file:
pickups = csv.DictReader(pickup_file)
count = 0
ditched = 0
start = datetime.datetime.now()
for pickup in pickups:
pickup_loc = 'SRID=4326;POINT({} {})'.format(
pickup['Lon'], pickup['Lat'])
pickup_geo = func.ST_GeogFromText(pickup_loc)
nearest = session.query(db_model.Intersection.id, func.ST_Distance(pickup_geo, db_model.Intersection.location)).\
filter(func.ST_DWithin(db_model.Intersection.location, pickup_geo, 200)).\
order_by(func.ST_Distance(pickup_geo, db_model.Intersection.location).asc()).first()
if nearest is None:
ditched += 1
continue
nearest = nearest[0]
pickup_time = datetime.datetime.strptime(pickup['Date/Time'],
"%m/%d/%Y %H:%M:%S")
session.add(
db_model.Pickup(time=pickup_time,
base=pickup['Base'],
location=pickup_geo,
intersection=nearest,
week_chunk=week_chunk(pickup_time),
month_chunk=month_chunk(pickup_time)))
if count % 10000 == 0:
stop = datetime.datetime.now()
print((stop - start).total_seconds(), count, ditched)
start = datetime.datetime.now()
count += 1
session.commit()
session.close()
def shop_details_for_user(
db_session: Session,
*,
shop_id: str,
location: Optional[Dict[str, float]] = None) -> Tuple[Shop, bool]:
shop: Shop = db_session.query(Shop).filter_by(id=shop_id).first()
deliverable: bool = None
if shop and location:
longitude = location["longitude"]
latitude = location["latitude"]
point_ewkt = f"SRID=4326;POINT({longitude} {latitude})"
calc_query = db_session.query(
shop.location.ST_DWithin(
func.ST_GeogFromText(point_ewkt),
shop.radius_metres).label("deliverable")).one()
deliverable = calc_query.deliverable
return (shop, deliverable)
def ST_GeoFromText(self, x):
return func.ST_GeogFromText(x)
def argoQuery(dbcon, geoWKT=None, tspan=None, withinDmeter=None, tsort=None):
tbl = dbcon.getTable('argo2', 'oceanobs')
#first create a subquery to quickly discard argo profiles
subqry = select([tbl])
if tspan:
subqry = subqry.where(
func.overlaps(tbl.c.tstart, tbl.c.tend, tspan[0], tspan[1]))
# Apply initial geospatial constraints
if geoWKT:
if withinDmeter:
#only base initial constraints ont he bounding box
subqry = subqry.where(
func.ST_DWithin(
literal_column('ST_Envelope(geom::geometry)::geography'),
func.ST_GeogFromText(geoWKT), withinDmeter))
else:
subqry = subqry.where(
func.ST_Intersects(literal_column('geom::geometry'),
func.ST_GeomFromText(geoWKT, 4326)))
#we need to assign an alias to this subquery in order to work with it
subqry = subqry.alias("ar")
#expand the arrays and points int he subquery
qry = select([
subqry.c.wmoid, subqry.c.uri, subqry.c.datacenter,
func.unnest(subqry.c.mode).label('mode'),
func.unnest(subqry.c.ascend).label('ascend'),
func.unnest(subqry.c.tlocation).label('tlocation'),
func.unnest(subqry.c.cycle).label('cycle'),
func.unnest(subqry.c.iprof).label('iprof'),
ST_Dump(literal_column("ar.geom::geometry")).geom.label('geom')
])
#additional spatial constraints
finalqry = qry
qry = qry.alias("arex")
if tspan:
finalqry = select([qry]).where(
between(qry.c.tlocation, tspan[0], tspan[1]))
if geoWKT:
if withinDmeter:
#only base initial constraints ont he bounding box
finalqry = finalqry.where(
func.ST_DWithin(qry.c.geom, func.ST_GeogFromText(geoWKT),
withinDmeter))
else:
finalqry = finalqry.where(
func.ST_Within(literal_column("arex.geom"),
func.ST_GeomFromText(geoWKT, 4326)))
if tsort:
finalqry = finalqry.order_by(qry.c.tlocation)
return dbcon.dbeng.execute(finalqry)