def test_node_distance(session):
if session.bind.name == "postgresql":
pytest.skip("Check only applicable to spatialite")
con1_too_close = factories.ConnectionNodeFactory(
the_geom="SRID=4326;POINT(4.728282 52.64579283592512)"
)
con2_too_close = factories.ConnectionNodeFactory(
the_geom="SRID=4326;POINT(4.72828 52.64579283592512)"
)
# Good distance
factories.ConnectionNodeFactory(
the_geom="SRID=4326;POINT(4.726838755789598 52.64514133594995)"
)
# sanity check to see the distances between the nodes
node_a = aliased(models.ConnectionNode)
node_b = aliased(models.ConnectionNode)
distances_query = Query(
geo_func.ST_Distance(node_a.the_geom, node_b.the_geom, 1)
).filter(node_a.id != node_b.id)
# Shows the distances between all 3 nodes: node 1 and 2 are too close
distances_query.with_session(session).all()
check = ConnectionNodesDistance(minimum_distance=10)
invalid = check.get_invalid(session)
assert len(invalid) == 2
invalid_ids = [i.id for i in invalid]
assert con1_too_close.id in invalid_ids
assert con2_too_close.id in invalid_ids
def findNearStop(self, geom):
stop_id = None
try:
near_stop = db.session.query(models.Stops.gid,
func.ST_Distance(models.Stops.geom, geom).label("dist"))\
.filter_by(rte=int(self.rte), dir=int(self.dir))\
.order_by(models.Stops.geom.distance_centroid(geom))\
.first()
if near_stop:
stop_id = near_stop.gid
except Exception as e:
app.logger.warn("Exception thrown in findNearStop: " + str(e))
return stop_id
def request_pois(self):
"""
Queries pois or statistics from the database. Pois will always be less then querying category statistics
because one poi can have several categories.
:return: Will either return a feature collection of pois or poi statistics.
:type: dict
"""
params = self.payload
geom_filters, geom = self.generate_geom_filters(
params['geometry'], Pois)
logger.debug('geometry filters: {}, geometry: {}'.format(
geom_filters, geom))
category_filters = []
if 'filters' in params:
if 'category_ids' in params['filters']:
category_filters.append(
Categories.category.in_(params['filters']['category_ids']))
# currently only available for request=pois
custom_filters = []
if 'filters' in params:
custom_filters = self.generate_custom_filters(params['filters'])
if params['request'] == 'stats':
bbox_query = db.session \
.query(Pois.uuid, Categories.category) \
.filter(*geom_filters) \
.filter(*category_filters) \
.outerjoin(Categories) \
.subquery()
stats_query = db.session \
.query(bbox_query.c.category, func.count(bbox_query.c.category).label("count")) \
.group_by(bbox_query.c.category) \
# .all()
places_json = self.generate_category_stats(stats_query)
return places_json
# join with tags
elif params['request'] == 'pois':
bbox_query = db.session \
.query(Pois) \
.filter(*geom_filters) \
.subquery()
# sortby needed here for generating features in next step
# sortby_group = [bbox_query.c.osm_id]
sortby_group = []
if 'sortby' in params:
if params['sortby'] == 'distance':
sortby_group.append(
geo_func.ST_Distance(type_coerce(geom, Geography),
bbox_query.c.geom))
elif params['sortby'] == 'category':
sortby_group.append(bbox_query.c.category)
# start = timer()
keys_agg = func.array_agg(Tags.key).label('keys')
values_agg = func.array_agg(Tags.value).label('values')
categories_agg = func.array_agg(
Categories.category).label('categories')
pois_query = db.session \
.query(bbox_query.c.osm_id,
bbox_query.c.osm_type,
bbox_query.c.geom.ST_Distance(type_coerce(geom, Geography)),
bbox_query.c.geom,
keys_agg,
values_agg,
categories_agg) \
.order_by(*sortby_group) \
.filter(*category_filters) \
.filter(*custom_filters) \
.outerjoin(Tags) \
.outerjoin(Categories) \
.group_by(bbox_query.c.uuid) \
.group_by(bbox_query.c.osm_id) \
.group_by(bbox_query.c.osm_type) \
.group_by(bbox_query.c.geom) \
# .all()
# end = timer()
# print(end - start)
# print(str(pois_query))
# for dude in pois_query:
# print(dude)
# response as geojson feature collection
features = self.generate_geojson_features(pois_query,
params['limit'])
return features
def distance(col_1, col_2):
return geo_func.ST_Distance(transform(col_1), transform(col_2))
def home():
def convertCoordinates(hexlocation):
point = wkb.loads(hexlocation, hex=True)
long = point.x
lat = point.y
return [lat, long]
datetime = dt.datetime.now()
delta = datetime + dt.timedelta(days=4)
defaultEvents = db.session.query(Event.title, Event.ageRestriction, Event.category_name, Event.startdate, Event.venueCoordinates,Event.facebookEventUrl,Event.venueName,Event.venueAddress)\
.filter(Event.startdate >= datetime, Event.startdate <= delta)
defaultCords = []
for event in defaultEvents:
if event.venueCoordinates is not None:
coord = convertCoordinates(str(event.venueCoordinates))
defaultCords.append(coord)
else:
if event.venueAddress is not None:
try :
c = adresstocoordinates((event.venueAddress))
defaultCords.append(c)
except:
defaultCords.append('None')
else :
std = [0, 0]
defaultCords.append(std)
if request.method == 'POST':
#Get user input from form
date = request.form.get('date')
time = request.form.get('time')
category = request.form.get('category')
ticketPrize = request.form.get('ticketPrize')
ageLimit = request.form.get('ageLimit')
pos = request.form.get('pos')
distance = request.form.get('distance')
lat = 0
long = 0
filters = []
if date and time:
date = dt.datetime.strptime(date, "%Y-%m-%d").date()
time = dt.datetime.strptime(time, "%H:%M").time()
datetime = dt.datetime.combine(date, time)
delta = datetime + dt.timedelta(days=1)
filters.append(Event.startdate >= datetime)
filters.append(Event.startdate <= delta)
if not date and not time:
filters.append(Event.startdate >= dt.datetime.now())
filters.append(Event.startdate <= dt.datetime.now()+ dt.timedelta(days=1))
if ageLimit:
filters.append(or_(Event.ageRestriction < ageLimit, Event.ageRestriction == None))
if category:
filters.append(Event.category_name == category)
if ticketPrize:
filters.append(Event.regularPrice < ticketPrize)
if pos:
lat, long = float(pos.split(',')[0]), float(pos.split(',')[1])
if pos and distance:
user_point = WKTElement('POINT({} {})'.format(pos.split(',')[1],pos.split(',')[0]))
filters.append(ga.ST_Distance(Event.venueCoordinates, user_point) <= distance)
events = db.session.query(Event.title, Event.ageRestriction, Event.category_name, Event.startdate,
Event.venueCoordinates,Event.facebookEventUrl,Event.venueName,
Event.venueAddress).filter(and_(*filters)).all()
cords = []
for event in events:
if event.venueCoordinates is not None:
coord = convertCoordinates(str(event.venueCoordinates))
cords.append(coord)
else:
if event.venueAddress is not None:
c = adresstocoordinates((event.venueAddress))
cords.append(c)
else :
std = [0, 0]
cords.append(std)
return render_template('home.html', categories=Event.CATEGORY_CHOICES, events_latlong=zip(events,cords), lat_user=lat, long_user=long, distance = distance)
return render_template('home.html', categories=Event.CATEGORY_CHOICES, events_latlong=zip(defaultEvents, defaultCords))
def read_all_addresses():
query = db.session.query(Address)
# -- name --
# Filter address on a wildcard name string
name_filter = request.args.get('name')
if name_filter:
query = query.filter(Address.name.like(f"%{name_filter}%"))
# -- address --
# Filter address on a wildcard address string
address_filter = request.args.get('address')
if address_filter:
query = query.filter(Address.address.like(f"%{address_filter}%"))
# -- city --
# Filter city on a wildcard city string
city_filter = request.args.get('city')
if city_filter:
query = query.filter(Address.city.like(f"%{city_filter}%"))
# -- area_id --
# Filter on an area_id string
area_filter = request.args.get('area_id')
if area_filter:
query = query.filter_by(area_id=area_filter)
# -- country_code --
# Filter country_code on a wildcard country_code string
country_filter = request.args.get('country_code')
if country_filter:
query = query.filter(Address.country_code.like(f"%{country_filter}%"))
# -- latitude --
# Filter latitude between start and end latitudes
lat_start_filter = request.args.get('lat_start')
lat_end_filter = request.args.get('lat_end')
if lat_start_filter:
query = query.filter(Address.latitude >= lat_start_filter)
if lat_end_filter:
query = query.filter(Address.latitude <= lat_end_filter)
# -- longitude --
# Filter longitude between start and end longitudes
lon_start_filter = request.args.get('lon_start')
lon_end_filter = request.args.get('lon_end')
if lon_start_filter:
query = query.filter(Address.longitude >= lon_start_filter)
if lon_end_filter:
query = query.filter(Address.longitude <= lon_end_filter)
# -- distance from filters --
# Filter addresses by their distance from a specified latitude and
# longitude
dist_lat_filter = request.args.get('dist_lat')
dist_lng_filter = request.args.get('dist_lng')
dist_filter = request.args.get('dist')
if dist_lat_filter and dist_lng_filter and dist_filter:
query = query.filter(
func.ST_Distance(
func.ST_GeogFromWKB(
func.ST_Point(Address.longitude, Address.latitude)),
func.ST_GeogFromWKB(
func.ST_Point(dist_lng_filter, dist_lat_filter))) <
dist_filter)
# Filter addresses by their distance from a specified address
dist_addr_lat_filter = request.args.get('dist_addr_lat')
dist_addr_lng_filter = request.args.get('dist_addr_lng')
dist_addr_filter = request.args.get('dist_addr')
if dist_addr_lat_filter and dist_addr_lng_filter and dist_addr_filter:
query = query.filter(
func.ST_Distance(
func.ST_GeogFromWKB(
func.ST_Point(Address.longitude, Address.latitude)),
func.ST_GeogFromWKB(
func.ST_Point(dist_addr_lng_filter, dist_addr_lat_filter)))
< dist_addr_filter)
result = query.all()
return jsonify(address_schema.dump(result, many=True))