def index(self):
polygons = db.session.query(
CrowdsourcingPolygon.id, CrowdsourcingPolygon.geom,
func.count(Point.geom).label("num_points")).outerjoin(
Point,
and_(
func.ST_Contains(CrowdsourcingPolygon.geom, Point.geom),
func.substr(Point.properties[('tags', 'timestamp')].astext,
1, 10) == "2017-05-09")).group_by(
CrowdsourcingPolygon.id)
#map(self.mostPoints, polygons)
for polygon in polygons:
self.mostPoints(polygon)
polygons = map(
lambda polygon: {
'id': polygon.id,
'geom': list(to_shape(polygon.geom).exterior.coords),
'num_points': polygon.num_points,
'is_winner': self.is_winner(polygon.id)
}, polygons)
polygons = list(polygons)
return Response(json.dumps(polygons), mimetype='application/json')
def start_geojson():
pools = Carpool.query
if request.args.get('ignore_prior') != 'false':
pools = pools.filter(Carpool.leave_time >= datetime.datetime.utcnow())
near_lat = request.args.get('near.lat')
near_lon = request.args.get('near.lon')
near_radius = request.args.get('near.radius') or None
if near_lat and near_lon:
try:
near_lat = float(near_lat)
near_lon = float(near_lon)
except ValueError:
abort(400, "Invalid lat/lon format")
try:
near_radius = int(near_radius)
except ValueError:
abort(400, "Invalid radius format")
center = from_shape(Point(near_lon, near_lat), srid=4326)
if near_radius:
# We're going to say that radius is in meters.
# The conversion factor here is based on a 40deg latitude
# (roughly around Virginia)
radius_degrees = near_radius / 111034.61
pools.filter(
func.ST_Distance(Carpool.from_point, center) <= radius_degrees)
pools = pools.order_by(func.ST_Distance(Carpool.from_point, center))
riders = db.session.query(RideRequest.carpool_id,
func.count(RideRequest.id).label('pax')).\
filter(RideRequest.status == 'approved').\
group_by(RideRequest.carpool_id).\
subquery('riders')
pools = pools.filter(Carpool.from_point.isnot(None)).\
outerjoin(riders, Carpool.id == riders.c.carpool_id).\
filter(riders.c.pax.is_(None) | (riders.c.pax < Carpool.max_riders))
features = []
dt_format = current_app.config.get('DATE_FORMAT')
for pool in pools:
features.append({
'type':
'Feature',
'geometry':
mapping(to_shape(pool.from_point)),
'id':
url_for('carpool.details', uuid=pool.uuid, _external=True),
'properties': {
'from_place': escape(pool.from_place),
'to_place': escape(pool.destination.name),
'seats_available': pool.seats_available,
'leave_time': pool.leave_time.isoformat(),
'return_time': pool.return_time.isoformat(),
'leave_time_human': pool.leave_time.strftime(dt_format),
'return_time_human': pool.return_time.strftime(dt_format),
'driver_gender': escape(pool.driver.gender),
},
})
feature_collection = {'type': 'FeatureCollection', 'features': features}
return jsonify(feature_collection)
def start_geojson():
pools = Carpool.query.filter(Carpool.canceled == False)
if request.args.get('ignore_prior') != 'false':
pools = pools.filter(Carpool.leave_time >= datetime.datetime.utcnow())
try:
near_lat = request.args.get('near.lat', type=float)
near_lon = request.args.get('near.lon', type=float)
except ValueError:
abort(400, "Invalid lat/lon format")
max_distance = 80467 # 50 miles in meters
search_point = 'POINT(%s %s)' % (near_lon, near_lat)
# ST_DistanceSphere returns minimum distance in meters between two lon/lat geometries
pools = pools.filter(
func.ST_DistanceSphere(Carpool.from_point, search_point) <= max_distance
)
pools = pools.order_by(func.ST_DistanceSphere(Carpool.from_point, search_point))
riders = db.session.query(RideRequest.carpool_id,
func.count(RideRequest.id).label('pax')).\
filter(RideRequest.status == 'approved').\
group_by(RideRequest.carpool_id).\
subquery('riders')
pools = pools.filter(Carpool.from_point.isnot(None)).\
filter(Carpool.destination_id == Destination.id).\
filter(Destination.hidden.isnot(True)).\
outerjoin(riders, Carpool.id == riders.c.carpool_id).\
filter(riders.c.pax.is_(None) | (riders.c.pax < Carpool.max_riders))
features = []
dt_format = current_app.config.get('DATE_FORMAT')
# get the current user's confirmed carpools
confirmed_carpools = []
if not current_user.is_anonymous:
rides = RideRequest.query.filter(RideRequest.status == 'approved').\
filter(RideRequest.person_id == current_user.id)
for ride in rides:
confirmed_carpools.append(ride.carpool_id)
else:
# anonymous user can only see 3 results
pools = pools.limit(3)
for pool in pools:
# show real location to driver and confirmed passenger
geometry = mapping(to_shape(pool.from_point))
if not current_user.is_anonymous and \
(pool.driver_id == current_user.id or pool.id in confirmed_carpools):
is_approximate_location = False
else:
is_approximate_location = True
geometry = approximate_location(geometry)
features.append({
'type': 'Feature',
'geometry': geometry,
'id': url_for('carpool.details', uuid=pool.uuid, _external=True),
'properties': {
'from_place': escape(pool.from_place),
'to_place': escape(pool.destination.name),
'seats_available': pool.seats_available,
'leave_time': pool.leave_time.isoformat(),
'return_time': pool.return_time.isoformat(),
'leave_time_human': pool.leave_time.strftime(dt_format),
'return_time_human': pool.return_time.strftime(dt_format),
'driver_gender': escape(pool.driver.gender),
'is_approximate_location': is_approximate_location,
'hidden': pool.destination.hidden
},
})
feature_collection = {
'type': 'FeatureCollection',
'features': features
}
return jsonify(feature_collection)
def get_evaluations(countries):
station_tags = ['substation', 'station', 'sub_station']
plant_tags = ['plant', 'generator']
countries_stats = {}
for country in countries:
country_stat = {
'all_line_length_with_duplicates': 0,
'plants_count': 0,
'substations_count': 0,
'length_by_voltages': {},
'relation_length_by_voltages': {}
}
try:
country_stat['all_line_length_with_duplicates'] = \
db.session.query(func.sum(TransnetPowerline.length * TransnetPowerline.osm_replication).label(
'sum_line')).filter(
TransnetPowerline.country == country)[0][0] / 1000
country_stat['plants_count'] = db.session.query(
func.count(TransnetStation.id)).filter(
TransnetStation.country == country).filter(
TransnetStation.type.in_(plant_tags))[0][0]
country_stat['substations_count'] = db.session.query(
func.count(TransnetStation.id)).filter(
TransnetStation.country == country).filter(
TransnetStation.type.in_(station_tags))[0][0]
voltages = db.session.query(
func.unnest(TransnetPowerline.voltage)).filter(
TransnetPowerline.country == country).distinct()
relations_voltages = db.session.query(
func.unnest(TransnetCountry.voltages)).filter(
TransnetCountry.country == country).distinct()
for voltage in relations_voltages:
country_stat['relation_length_by_voltages'][voltage[0]] = 0
length_with_dup = db.session \
.query(func.sum(TransnetPowerline.length * TransnetPowerline.osm_replication).label('sum_line')) \
.filter(TransnetPowerline.country == country) \
.filter(TransnetPowerline.voltage.any(voltage)) \
.filter(TransnetPowerline.relations.any(TransnetRelation.voltage.in_([voltage])))
if length_with_dup.count() and length_with_dup[0][0]:
country_stat['relation_length_by_voltages'][
voltage[0]] = length_with_dup[0][0] / 1000
for voltage in voltages:
country_stat['length_by_voltages'][voltage[0]] = 0
length_shared_with_dup = db.session.query(
func.sum(TransnetPowerline.length *
TransnetPowerline.osm_replication).
label('sum_line')).filter(
TransnetPowerline.country == country).filter(
TransnetPowerline.voltage.any(voltage[0]))
if length_shared_with_dup.count(
) and length_shared_with_dup[0][0]:
country_stat['length_by_voltages'][
voltage[0]] = length_shared_with_dup[0][0] / 1000
except Exception as ex:
print(ex)
countries_stats[country] = country_stat
if len(countries) > 1:
country_stat = {
'all_line_length_with_duplicates':
sum([
cn['all_line_length_with_duplicates']
for cn in countries_stats.values()
]),
'plants_count':
sum([cn['plants_count'] for cn in countries_stats.values()]),
'substations_count':
sum([
cn['substations_count'] for cn in countries_stats.values()
]),
'length_by_voltages': {},
'relation_length_by_voltages': {}
}
for cn in countries_stats.values():
for voltage in cn['length_by_voltages'].keys():
if voltage in country_stat['length_by_voltages'].keys():
country_stat['length_by_voltages'][voltage] += cn[
'length_by_voltages'][voltage]
else:
country_stat['length_by_voltages'][voltage] = cn[
'length_by_voltages'][voltage]
for cn in countries_stats.values():
for voltage in cn['relation_length_by_voltages'].keys():
if voltage in country_stat[
'relation_length_by_voltages'].keys():
country_stat['relation_length_by_voltages'][voltage] += \
cn['relation_length_by_voltages'][voltage]
else:
country_stat['relation_length_by_voltages'][
voltage] = cn['relation_length_by_voltages'][
voltage]
countries_stats['aaa'] = country_stat
return countries_stats