def distance(cls, latitude, longitude):
earth_radius = 6371
pi_converter = func.pi() / 180
dlat = (latitude - cls.latitude) * pi_converter
dlon = (longitude - cls.longitude) * pi_converter
lat1 = (cls.latitude) * pi_converter
lat2 = (latitude) * pi_converter
haversine = func.sin(dlat/2) * func.sin(dlat/2) + func.sin(dlon/2) * func.sin(dlon/2) * func.cos(lat1) * func.cos(lat2)
return earth_radius * 2 * func.atan2(func.sqrt(haversine), func.sqrt(1-haversine))
def search_location():
latitude = request.args['latitude']
longitude = request.args['longitude']
radius = int(request.args['radius'])
primary = True if (request.args['primary'] == 'primary') else False
page = int(request.args.get('page', '1'))
format = request.args.get('format', 'html')
radius_m = radius * 1000
wkt = "POINT(%s %s)" % (longitude, latitude)
location = WKTSpatialElement(wkt)
loc = sql.cast(LocationBase.location, Geography)
q_loc = sql.cast(location, Geography)
query = CadorsReport.query.join(LocationRef).join(LocationBase).filter(
functions.within_distance(loc, q_loc, radius_m))
if primary:
query = query.filter(LocationRef.primary == True)
if format == 'html':
query = query.add_column(functions.distance(loc, q_loc).label('distance'))
query = query.add_column(
func.ST_Azimuth(location,
LocationBase.location.RAW) * (180/func.pi()))
query = query.add_column(LocationBase.name)
query = query.order_by('distance ASC',
CadorsReport.timestamp.desc())
pagination = query.paginate(page)
response = make_response(
render_template('sr_loc.html',
reports=pagination.items, pagination=pagination,
get_direction=get_direction, radius=radius,
latitude=latitude, longitude=longitude))
return prepare_response(response, 300)
else:
pagination = query.paginate(page)
title = "Events within %s km of %s, %s" % (radius, latitude,
longitude)
return render_list(pagination, title, format)
##Step 4. Dealing with Duplicates
from sqlalchemy import distinct
print(session.query(Customer.town).filter(Customer.id < 10).all())
print(session.query(Customer.town).filter(Customer.id < 10).distinct().all())
print(
session.query(func.count(distinct(Customer.town)),
func.count(Customer.town)).all())
##Step 5. Casting
from sqlalchemy import cast, Date, distinct, union
print(
session.query(
cast(func.pi(), Integer),
cast(
func.pi(),
String(200),
cast("2010-12-01", DateTime),
cast("2010-12-01", Date),
).all()))
##Step 6. Unions
s1 = session.query(Item.id, Item.name).filter(Item.name.like("Wa%"))
s2 = session.query(Item.id, Item.name).filter(Item.name.like("%e%"))
print(s1.union(s2).all())
##Step 7. Updating Data
i = session.query(Item).get(8)
i.selling_price = 25.91
print("Name: ", i.first_name, " ", i.last_name, " Address:", i.address, " Email:", i.email)
#Dealing with Duplicates
from sqlalchemy import distinct
print(session.query(Customer.town).filter(Customer.id < 10).all())
print(session.query(Customer.town).filter(Customer.id < 10).distinct().all())
print(session.query(
func.count(distinct(Customer.town)),
func.count(Customer.town)
).all())
#Casting
from sqlalchemy import cast, Date, distinct, union
session.query(
cast(func.pi(), Integer),
cast(func.pi(), Numeric(10, 2)),
cast("2010-12-01", DateTime),
cast("2010-12-01", Date),
all()
)
# Unions
s1 = session.query(Item.id, Item.name).filter(Item.name.like("Wa%"))
s2 = session.query(Item.id, Item.name).filter(Item.name.like("%e%"))
print(s1.union(s2).all())
# Union All
print(s1.union_all(s2).all())