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 query(self, source, target, core, column, pkey):
# Get all POIs of fclass `on`
pois = select(
[source.c[self.source_id], source.c.WKT],
source.c[self.source_column] == self.source_filter,
).cte("pois")
# Compute the distance from `column` to each POI within given distance
distance = func.ST_Distance(
core.ST_GeoFromText(target.c[column]),
core.ST_GeoFromText(pois.c.WKT),
)
pairs = (select(
[target, distance.label(self.feature_name)],
distance < self.within,
).select_from(pois).cte("pairs"))
# Partition results to get the smallest distance (nearest POI)
query = select([
pairs,
func.row_number().over(
partition_by=pairs.c[pkey],
order_by=pairs.c[self.feature_name].asc(),
).label("row_number"),
]).select_from(pairs)
query = select(
[col for col in query.columns if col.key != "row_number"],
query.c["row_number"] == 1,
)
return query
def get_closest_node(longitude, latitude):
"""
Get the closest 10 nodes to the given longitude and latitude.
This function converts geometry points to srid METER_UNIT_SRID(26986) point
using ST_Transform and then calculate their distance in meters using ST_Distance.
This function may not return an array of exact length 10 if there is less than 10 points
in the database.
:param longitude: the longitude of the origin point
:param latitude: the latitude of the origin point
:return: JSON of an array containing the 10 closest nodes (if there are more than 10 nodes).
The array is sorted in ascending distance order. node object keys: node_id(int),
geometry(geom{type(str), coordinates(list[int])})
"""
origin_point = _get_srid_point(longitude, latitude)
nodes_ascend_dist_order_query_result = Node.query \
.with_entities(Node.node_id, Node.geom.ST_AsGeoJSON()) \
.order_by(func.ST_Distance(func.ST_Transform(Node.geom, METER_UNIT_SRID),
_transform_to_meter_srid(origin_point)).asc())
ten_closest_nodes = []
node_count = 0
for node_data in nodes_ascend_dist_order_query_result:
if node_count >= 10:
break
ten_closest_nodes.append(parse_node_response(node_data))
node_count += 1
return jsonify(ten_closest_nodes)
def query(self, source, target, core, column):
# Get all POIs of fclass `on`
pois = select(
[source.c[self.source_id], source.c.WKT],
source.c[self.source_column] == self.source_filter,
).cte("pois")
# Compute the distance from `column` to each POI within given distance
distance = func.ST_Distance(
core.ST_GeoFromText(target.c[column]),
core.ST_GeoFromText(pois.c.WKT),
)
pairs = (select(
[target, pois.c[self.source_id],
distance.label("distance")],
distance < self.within,
).select_from(pois).cte("pairs"))
# Partition results to get the smallest distance (nearest POI)
keep_columns = [
cols for cols in pairs.columns
if cols.key not in ["distance", self.source_id]
]
query = (select([
*keep_columns,
func.count(distinct(pairs.c[self.source_id])).label(
self.feature_name),
]).select_from(pairs).group_by(*keep_columns))
return query
def get_poi_along_path(cls, lines, radius=5, filters=None):
if filters is None:
filters = {}
"""
SELECT park.*
FROM park
WHERE ST_DWithin(line.geom, park.geom, radius)
ORDER BY ST_Line_Locate_Point(line.geom, park.geom),
ST_Distance(line.geom, park.geom);
:param lines: an array of locations
:param radius: the search radius
:param filters: filters for searching
:return: list of all POI's found
"""
linestring = RoutePaths(paths=lines)
q = db.session.query(Park).join(Ride).filter(func.ST_DWithin(linestring.locations,
Park.location,
radius)) \
.order_by(
desc(func.ST_Line_Locate_Point(linestring.locations, Park.location)),
desc(func.ST_Distance(linestring.locations, Park.location)))
final = _combine_filters(q, filters)
return final.all()
def nearby():
try:
lat = float(request.args.get('lat'))
long = float(request.args.get('long'))
except (TypeError, ValueError):
logging.info('Missing latitude and longitude params')
return 'Missing latitude and longitude params', 400
dist = int(request.args.get('distance') or 350)
current_pos = 'POINT(%f %f)' % (long, lat)
places_raw = db.session \
.query(
Place,
func.ST_Distance(current_pos, Place.position).label('distance'),
func.count(Place.guides)
).filter(func.ST_DWithin(
Place.position,
current_pos,
dist)
).outerjoin(Place.guides) \
.group_by(Place.id) \
.order_by('distance') \
.all()
places = []
for p_raw in places_raw:
p = to_object(p_raw[0], excluded=['position'])
p['numGuides'] = p_raw[2]
p['distance'] = p_raw[1]
places.append(p)
logging.info('Returning %d results' % len(places))
return jsonify(places)
def add(cls, s, centre, radius_km, title, delete=False):
'''
if delete is True, nearby similar entries are deleted before insertion.
if delete is False, and there is already a nearby similar entry, it is returned.
otherwise, insertion is attempted without deletion.
centre is (lon, lat)
'''
lon, lat = centre
srid = utm_srid(lat, lon)
centre = text(point(lon, lat))
radius = radius_km * 1000
if delete is not None:
query = s.query(SectorGroup). \
filter(func.ST_Distance(SectorGroup.centre, centre) < radius,
SectorGroup.srid == srid)
n = query.count()
if delete:
if n:
log.warning(f'Deleting {n} previous sector groups')
query.delete(synchronize_session=False)
else:
if n:
sector_group = query.first()
log.info(f'Using previously defined sector group "{sector_group.title}"')
return sector_group
return add(s, SectorGroup(srid=srid, centre=centre, radius=radius, title=title))
def nearby(meters, lat, lng, start_time):
loc = WKTElement("POINT(%0.8f %0.8f)" % (lng, lat))
meals = Meal.query.filter(func.ST_Distance(loc, Meal.geo) <= meters) \
.filter(Meal.pickup_time >= start_time) \
.filter(Meal.pickup_time <= closing_datetime()) \
.order_by(Meal.pickup_time)
return meals.limit(20).all()
def test_distance_query(geospatial_db_session):
distances = geospatial_db_session\
.query(Place,
func.ST_Distance(REF_POINT, Place.position))\
.all()
assert len(distances) == 3
duomo_dist = next(p for p in distances if p[0].name == 'Piazza Duomo')
assert 190 < duomo_dist[1] < 200
def get(self, args, **_kwargs):
dist = func.ST_Distance(Shop.position, from_shape(Point(args['lng'], args['lat']), srid=4326), True). \
label('dist')
query = db.session.query(Shop, Shop.position, dist).options(
lazyload('prices'),
lazyload('tags')).filter(dist < 1000 * args['dist']).order_by(
dist.asc()).limit(args['count'])
shops = query.all()
return ShopDistSchema().dump(shops, many=True).data
def _within_distance(compiler, geom1, geom2, distance, *args):
"""ST_DWithin in early versions of PostGIS 1.3 does not work when
distance = 0. So we are directly using the (correct) internal
definition. Note that the definition changed in version 1.3.4, see also:
http://postgis.refractions.net/docs/ST_DWithin.html
"""
return and_(func.ST_Expand(geom2, distance).op('&&')(geom1),
func.ST_Expand(geom1, distance).op('&&')(geom2),
func.ST_Distance(geom1, geom2) <= distance)
def by_location(cls, location, distance_threshold=0.025):
location = location.to_wkt_element()
distance = func.ST_Distance(cls.location_wkt, location)
airport = db.session.query(cls, distance.label('distance')) \
.order_by(distance).first()
if airport is not None and (distance_threshold is None
or airport.distance < distance_threshold):
return airport.Airport
else:
return None
def get(self, cityId):
args = neighborParser.parse_args()
origin = City.query.filter(City.id == cityId).first()
if not origin:
abort(404, message='City {} does not exist'.format(cityId))
neighbors = City.query.filter(City.id != origin.id).order_by(
func.ST_Distance(City.location, origin.location))
if args['sameCountry']:
neighbors = neighbors.filter(
City.countryCode == origin.countryCode)
neighbors = neighbors.limit(args['limit']).offset(args['offset'])
return [asJson(n) for n in neighbors.all()]
def test_nearby_with_distances_query(geospatial_db_session):
nearby_places = geospatial_db_session\
.query(Place,
func.ST_Distance(REF_POINT, Place.position).label('distance'))\
.filter(func.ST_DWithin(
Place.position,
REF_POINT,
300)
).order_by('distance')\
.all()
assert len(nearby_places) == 2
assert nearby_places[1][0].name == 'Galleria Vittorio Emanuele'
assert nearby_places[0][0].name == 'Piazza Duomo'
def _run_activity_journal(self, s, ajournal, power_model):
if ajournal.route_edt:
for sector_group in s.query(SectorGroup). \
filter(func.ST_Distance(SectorGroup.centre, ajournal.centre) < SectorGroup.radius). \
all():
count = 0
for sjournal in find_and_add_sector_journals(s, sector_group, ajournal):
loader = self._get_loader(s, add_serial=False)
add_sector_statistics(s, sjournal, loader, power_model=power_model)
loader.load()
count += 1
if count:
log.info(f'Found {count} sectors for activity on {ajournal.start}')
def by_location(cls, location, distance_threshold=0.025, date=datetime.utcnow()):
location = location.to_wkt_element()
distance = func.ST_Distance(cls.location_wkt, location)
airport = db.session.query(cls, distance.label('distance')) \
.filter(or_(cls.valid_until == None, cls.valid_until > date)) \
.order_by(distance).first()
if airport is not None and (distance_threshold is None or
airport.distance < distance_threshold):
return airport.Airport
else:
return None
def get_galaxies():
try:
args = request.get_json()
except:
return ("Whoaaaa that JSON is a little wonky")
if args is None:
args = request.args
if args is None:
return ("Invalid Arguments.")
if "api_token" in args:
apitoken = args['api_token']
user = db.session.query(
models.users).filter(models.users.api_token == apitoken).first()
if user is None:
return jsonify("invalid api_token")
else:
return jsonify("api_token is required")
filter = []
filter1 = []
filter1.append(models.glade_2p3.pgc_number != -1)
filter1.append(models.glade_2p3.distance > 0)
filter1.append(models.glade_2p3.distance < 100)
trim = db.session.query(models.glade_2p3).filter(*filter1)
orderby = []
if 'ra' in args and 'dec' in args:
ra = args.get('ra')
dec = args.get('dec')
if function.isFloat(ra) and function.isFloat(dec):
geom = "SRID=4326;POINT(" + str(ra) + " " + str(dec) + ")"
orderby.append(func.ST_Distance(models.glade_2p3.position, geom))
if 'name' in args:
name = args.get('name')
ors = []
ors.append(models.glade_2p3._2mass_name.contains(name.strip()))
ors.append(models.glade_2p3.gwgc_name.contains(name.strip()))
ors.append(models.glade_2p3.hyperleda_name.contains(name.strip()))
ors.append(models.glade_2p3.sdssdr12_name.contains(name.strip()))
filter.append(fsq.sqlalchemy.or_(*ors))
galaxies = trim.filter(*filter).order_by(*orderby).limit(15).all()
galaxies = [x.json for x in galaxies]
return jsonify(galaxies)
def get(self):
parser = reqparse.RequestParser()
parser.add_argument('lng', type=float, required=True,
help='longitude is required')
parser.add_argument('lat', type=float, required=True,
help='latitude is required')
parser.add_argument('query', required=True,
help='query is required')
args = parser.parse_args()
point = WKTElement('POINT({0} {1})'.format(args['lng'], args['lat']),
srid=4326)
looking_for = '%{0}%'.format(args['query'])
stations = db.session.query(Station).filter(
or_(Station.name.ilike(looking_for),
Station.address.ilike(looking_for))
).order_by(func.ST_Distance(Station.position, point)).all()
payload = {'q': args['query'], 'lat': args['lat'], 'lon': args['lng']}
r = requests.get('http://photon.komoot.de/api/', params=payload)
poiList = []
for st in stations:
poiList.append(Poi(st.name, st.address, st.position))
responsejson = r.json()
for feature in responsejson['features']:
print feature
address = ''
if 'street' in feature['properties']:
address = feature['properties']['street']
if 'city' in feature['properties']:
address = address + ' - %s' % feature['properties']['city']
if 'state' in feature['properties']:
address = address + ' - %s' % feature['properties']['state']
if 'country' in feature['properties']:
address = address + ' - %s' % feature['properties']['country']
lon = feature['geometry']['coordinates'][0]
lat = feature['geometry']['coordinates'][1]
pos = WKTElement('POINT({0} {1})'.format(lon, lat), srid=4326)
poiList.append(Poi(feature['properties']['name'], address, pos))
return poiList
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 closest2Fesom(dbcon, fesominfo, geoWKT=None, samplePoints=[]):
"""returns vertices of a FESOM grid that have the smallest distance to sample points"""
#retrieve/reflect the table
tbl=dbcon.getTable(fesominfo["mesh"]["vertTable"],'fesom')
qry=select([tbl.c.topo, tbl.c.nodeid,literal_column('geom::geometry').label('geom')])
if geoWKT:
qry=qry.where(func.ST_within(literal_column('geom::geometry'),
func.ST_GeomFromText(geoWKT,4326)))
if len(samplePoints)!=0:
pp=[]
for p in samplePoints:
# print(p)
qry1=qry.order_by(func.ST_Distance(literal_column('geom::geometry'),
func.ST_GeomFromText(p.wkt,4326)))
qry1=qry1.limit(1)
pp.append(dbcon.dbeng.execute(qry1).first()._row)
return pp
def _search_with_position(current_pos, search_term):
places_raw = db.session \
.query(
Place,
func.ST_Distance(current_pos, Place.position).label('distance'),
func.count(Place.guides)
).filter(or_(
Place.name.ilike('%{}%'.format(search_term)),
Place.tags.like('%{}%'.format(search_term))
)) \
.outerjoin(Place.guides) \
.group_by(Place.id) \
.order_by('distance') \
.all()
places = []
for p_raw in places_raw:
p = to_object(p_raw[0], excluded=['position'])
p['numGuides'] = p_raw[2]
p['distance'] = p_raw[1]
places.append(p)
logging.info('Returning %d results' % len(places))
return jsonify(places)
def compute_listing_score(conn, listing, city, category):
def compute_score(selection_result):
score = 0
for row in selection_result:
doublestar = int(row.stars*2)
if doublestar in score_dict:
score += score_dict[doublestar]
return score
if listing.longitude == None or listing.latitude == None:
return 0
selection = select([yelp_table.c.stars])\
.select_from(yelp_table.join(yelp2category_table, yelp_table.c.id == yelp2category_table.c.yelp_id)\
.join(categories_table, yelp2category_table.c.category_id == categories_table.c.id)\
.join(yelp_geo_table, yelp_geo_table.c.yelp_id == yelp_table.c.id))\
.where(yelp_table.c.city == city)\
.where(categories_table.c.name == category)\
.where(func.ST_Distance(f'POINT({listing.longitude} {listing.latitude})',
yelp_geo_table.c.location) <= distance_threshold)\
.where(yelp_table.c.stars > 3)
result = conn.execute(selection)
return compute_score(result)
def search_nearest_partner():
try:
longitude = float(request.args.get('lng'))
latitude = float(request.args.get('lat'))
except ValueError:
message = {
'message':
f'Bad request: lng and lat are '
'required and should be float type'
}
return make_response(message, 400, HEADERS)
else:
partner = (Partner.query.filter(
func.ST_Intersects(
Partner.coverage_area,
f'POINT({longitude} {latitude})')).order_by(
func.ST_Distance(
Partner.address,
f'POINT({longitude} {latitude})')).first())
if partner:
return make_response(schema.dump(partner), 200, HEADERS)
return make_response('', 204, HEADERS)
def distance(self, location):
loc1 = cast(self.location_wkt, Geography)
loc2 = func.ST_GeographyFromText(location.to_wkt())
return db.session.scalar(func.ST_Distance(loc1, loc2))
def on_get(self, req, resp):
resp.content_type = falcon.MEDIA_JSON
session = Session()
matchedSymptoms = json.loads(req.get_header('symptoms'))
location = 'POINT('+req.get_header("latitude")+' '+req.get_header("longitude")+')'
clinicsForSympts = []
for symptId in matchedSymptoms:
clinicsForSympts.append(
set(
session.query(Clinic.id)
.join(Clinic.doctors)
.join(Doctor.specialities)
.join(Speciality.symptoms)
.filter(Symptom.id == symptId)
.distinct(Clinic.id)
.all()
)
)
matchedClinics = set(clinicsForSympts[0]).intersection(*clinicsForSympts)
closestClinic = session.query(Clinic.id) \
.filter(Clinic.id.in_(matchedClinics)) \
.order_by(asc(func.ST_Distance(Clinic.location, func.ST_GeographyFromText(location)))) \
.first()
if closestClinic is None:
resp.status = falcon.HTTP_404
resp.body = json.dumps({})
return
clinicOutput = session.query(Clinic).filter(Clinic.id == closestClinic.id).first()
matchedSpecs = session.query(Speciality.id) \
.join(Speciality.symptoms) \
.filter(Symptom.id.in_(matchedSymptoms)) \
.all()
doctorsOutput = session.query(Doctor).join(Doctor.clinics).join(Doctor.specialities) \
.filter(Clinic.id == closestClinic.id).filter(Speciality.id.in_(matchedSpecs)).distinct(Doctor.id).all()
specsOutput = session.query(Speciality.name).join(Clinic.doctors).join(Doctor.specialities)\
.filter(Clinic.id == closestClinic.id).filter(Speciality.id.in_(matchedSpecs))
losationStringX = session.query(func.ST_X(clinicOutput.location).label('x')).first()
losationStringY = session.query(func.ST_Y(clinicOutput.location).label('y')).first()
respp = {
'name': clinicOutput.name,
'location': str(losationStringX.x) + ' ' + str(losationStringY.y),
'opening': str(clinicOutput.opening),
'closure': str(clinicOutput.closure),
'doctors': [{
'fname': doctor.first_name,
'sname': doctor.second_name,
'pname': doctor.fathers_name,
'specialities': [
spec.name for spec in specsOutput.filter(Doctor.id == doctor.id).all()
],
'cabinet': doctor.cabinet,
'phone': doctor.phone
} for doctor in doctorsOutput
]
}
resp.body = json.dumps(respp)
resp.status = falcon.HTTP_200
def apply(self, query):
point = f"SRID=4326;POINT({self.values['lat']} {self.values['lon']})"
return query.order_by(
asc(func.ST_Distance(models.School.location, point)))
def get(self, args):
shop_ids = args['shops']
product_ids = args['products']
tags = args['tags']
with_geo = args['geoDist'] is not None and args[
'geoLng'] is not None and args['geoLat'] is not None
no_geo = args['geoDist'] is None and args['geoLng'] is None and args[
'geoLat'] is None
if not (with_geo or no_geo):
return custom_error(
'geo',
['Invalid geo parameters combination']), ErrorCode.BAD_REQUEST
with_date = (args['dateFrom'] is not None and args['dateTo'] is not None) and \
(args['dateFrom'] <= args['dateTo'])
no_date = args['dateFrom'] is None and args['dateTo'] is None
if not (with_date or no_date):
return custom_error(
'date',
['Invalid date parameters combination']), ErrorCode.BAD_REQUEST
sorts = [x.split('|') for x in args['sort']]
for sort in sorts:
if (sort[0] == 'geoDist' and no_geo) or (sort[0] == 'date'
and no_date):
return custom_error(
'sort', ['Invalid sort parameter']), ErrorCode.BAD_REQUEST
# extra validation, some combinations are illegal
dist_col = None
if with_geo:
dist = func.ST_Distance(Shop.position, from_shape(Point(args['geoLng'], args['geoLat']), srid=4326), True).\
label('dist')
query = db.session.query(Price, dist)
else:
query = Price.query
query = query.join(Price.product, Price.shop)
today = date.today()
date_from = args['dateFrom'] if args['dateFrom'] else today
date_to = args['dateTo'] if args['dateTo'] else today
query = query.filter(Price.date.between(date_from, date_to))
if shop_ids:
query = query.filter(Shop.id.in_(shop_ids))
if product_ids:
query = query.filter(Product.id.in_(product_ids))
if tags:
query = query.filter(
or_(
Product.tags.any(
func.lower(ProductTag.name).in_(map(str.lower, tags))),
Shop.tags.any(
func.lower(ShopTag.name).in_(map(str.lower, tags)))))
if with_geo:
subq = query.subquery()
dist_col = subq.c.dist
query = db.session.query(Price,
dist_col).select_entity_from(subq).filter(
dist_col < 1000 * args['geoDist'])
# nested query to avoid recalculating distance expr
# WHERE and HAVING clauses can't refer to column names (dist)
def to_sort_operator(field, order):
sort_field = {
'geoDist': dist_col,
'price': Price.price,
'date': Price.date
}[field]
sort_order = {'ASC': asc, 'DESC': desc}[order]
return sort_order(sort_field)
query = query.order_by(
*[to_sort_operator(field, order) for field, order in sorts])
start = args['start']
count = args['count']
total = query.count()
query = query.offset(start).limit(count)
prices_page = query.all()
prices = PricesResource.PriceSchema(many=True).dump(prices_page).data
return {
'start': start,
'count': count,
'total': total,
'prices': prices
}
def sort_by_distance(self, lat, lng) -> None:
self.query = self.query.order_by(
asc(
func.ST_Distance(
Item.geom,
WKTElement('POINT({} {})'.format(lng, lat), srid=4326))))
