def calc_distance(cls, lon, lat, point):
'''
@params: lon, lat, point(lon, lat) ... thing we're comparing to the lon,lat of this object
@see: http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula/574736#574736
Here's the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the
45.5, -122.5 coordinate. It calculates the distance based on the latitude/longitude of that row and the target
latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query
by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.
Haversine formula
SELECT( 3959
* acos(
cos( radians(45.5) )
* cos( radians( lat ) )
* cos( radians( lng ) - radians(-122.5) )
+ sin( radians(45.5) ) * sin( radians( lat ) )
)
) AS distance
'''
haversine = (3959 * func.acos(
func.cos(func.radians(point[1])) * func.cos(func.radians(lat)) *
func.cos(func.radians(lon) - func.radians(point[0])) +
func.sin(func.radians(point[1])) * func.sin(func.radians(lat))))
return haversine
def getLocationsWithinOneKilometerRadius(json):
return Location().query.filter((func.degrees(
func.acos(
func.sin(func.radians(json['lattitude'])) * func.sin(func.radians(Location.lattitude)) +
func.cos(func.radians(json['lattitude'])) * func.cos(func.radians(Location.lattitude)) *
func.cos(func.radians(json['longitude']-Location.longitude))
)
) * 60 * 1.1515 * 1.609344) <= 1).all()
def sort_by_distance_from_user(offers, user_lon, user_lat):
return offers.order_by((func.degrees(
func.acos(
func.sin(func.radians(user_lat)) *
func.sin(func.radians(Offer.pickup_latitude)) +
func.cos(func.radians(user_lat)) *
func.cos(func.radians(Offer.pickup_latitude)) *
func.cos(func.radians(user_lon - Offer.pickup_longitude)))) *
60 * 1.1515 * 1.609344))
def get_local_products():
user_id = None
try:
user_id = session["id"]
except:
pass
lon = request.json.get("lon")
lat = request.json.get("lat")
rng = request.json.get("range")
params = func.acos(func.sin(func.radians(lat)) * func.sin(func.radians(Address.lat)) + func.cos(
func.radians(lat)) * func.cos(func.radians(Address.lat)) * func.cos(func.radians(Address.lon) - (func.radians(lon)))) * current_app.config['RADIUS']
products = Product.query.join(Product.address).filter(
params <= rng).order_by(params).all()
products_dict = [product.to_dict(user_id, lat, lon)
for product in products]
return {"products": products_dict}, 200
def calc_distance(cls, lon, lat, point):
'''
@params: lon, lat, point(lon, lat) ... thing we're comparing to the lon,lat of this object
@see: http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula/574736#574736
Here's the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the
45.5, -122.5 coordinate. It calculates the distance based on the latitude/longitude of that row and the target
latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query
by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.
Haversine formula
SELECT( 3959
* acos(
cos( radians(45.5) )
* cos( radians( lat ) )
* cos( radians( lng ) - radians(-122.5) )
+ sin( radians(45.5) ) * sin( radians( lat ) )
)
) AS distance
'''
haversine = (
3959
*
func.acos(
func.cos( func.radians(point[1]) )
*
func.cos( func.radians(lat) )
*
func.cos( func.radians(lon) - func.radians(point[0]) )
+
func.sin( func.radians(point[1]) )
*
func.sin( func.radians(lat) )
)
)
return haversine
def get_nearest_obj(self,ra_in,dec_in,size=5):
a=self.BC.assets
#this distance is correct up to multiplications factors (order is correct)
dis=func.asin(func.sqrt(func.power(func.sin(0.5*func.radians(dec_in-a.dec)),2) + func.cos(func.radians(dec_in))*func.cos(func.radians(a.dec))*func.power(func.sin(.5*func.radians(ra_in-a.ra)),2)))
result=self.session.query(a.name,a.id,a.ra,a.dec,a.location).order_by(dis.asc()).first()
return result
def get_nearest_obj(self,ra_in,dec_in,size=2):
a=self.BC.assets
#find a bounding box to speed up search (size depended on search regon)
if (ra_in<60) or (ra_in>308.5):
if (dec_in+size)<-1:
size=abs(dec_in+1)
elif (dec_in-size)>1:
size=abs(dec_in-1)
else:
if (dec_in+size)<-3:
size=abs(dec_in+3)
elif (dec_in-size)>76:
size=abs(dec_in-76)
box=geo_bounding_box(ra_in,dec_in,size=size)
#this distance is correct up to multiplications factors (order is correct)
dis=func.asin(func.sqrt(func.power(func.sin(0.5*func.radians(dec_in-a.dec)),2) + func.cos(func.radians(dec_in))*func.cos(func.radians(a.dec))*func.power(func.sin(.5*func.radians(ra_in-a.ra)),2)))
if isinstance(box[0],list):
#the search wraps around 360
result=self.session.query(a.name,a.id,a.ra,a.dec,a.location).filter(between(a.dec,box[1][0],box[1][1])).filter((between(a.ra,box[0][0][0],box[0][0][1]))|(between(a.ra,box[0][1][0],box[0][1][1]))).order_by(dis.asc()).first()
else:
result=self.session.query(a.name,a.id,a.ra,a.dec,a.location).filter(between(a.ra,box[0][0],box[0][1])).filter(between(a.dec,box[1][0],box[1][1])).order_by(dis.asc()).first()
#result=self.session.query(a.name,a.id,a.ra,a.dec,a.location).order_by(dis.asc()).first()
if result is None:
result=self.get_nearest_obj(ra_in,dec_in,size=size+20)
return result
def _Word(self, node, fieldname):
value = node[0]
if fieldname:
like = False
try:
if value.endswith('*'):
value = value.replace('*', '%')
like = True
except TypeError:
# Hack around field values containing parens
# The node[0] is a non-string if that's the case.
node[0] = '(' + value[0] + ')'
return self._Word(node, fieldname)
if fieldname == 'ftitle':
fieldname = 'title'
if fieldname == 'fbag':
fieldname = 'bag'
if fieldname == 'bag':
if like:
expression = (sTiddler.bag.like(value))
else:
expression = (sTiddler.bag == value)
elif fieldname == 'title':
if like:
expression = (sTiddler.title.like(value))
else:
expression = (sTiddler.title == value)
elif fieldname == 'id':
bag, title = value.split(':', 1)
expression = and_(sTiddler.bag == bag,
sTiddler.title == title)
elif fieldname == 'tag':
if self.in_and:
tag_alias = aliased(sTag)
self.query = self.query.join(tag_alias)
if like:
expression = (tag_alias.tag.like(value))
else:
expression = (tag_alias.tag == value)
else:
if not self.joined_tags:
self.query = self.query.join(sTag)
if like:
expression = (sTag.tag.like(value))
else:
expression = (sTag.tag == value)
self.joined_tags = True
else:
if like:
expression = (sTag.tag.like(value))
else:
expression = (sTag.tag == value)
elif fieldname == 'near' and self.geo:
# proximity search on geo.long, geo.lat based on
# http://cdent.tiddlyspace.com/bags/cdent_public/tiddlers/Proximity%20Search.html
try:
lat, long, radius = [float(item)
for item in value.split(',', 2)]
except ValueError, exc:
raise StoreError(
'failed to parse search query, malformed near: %s'
% exc)
field_alias1 = aliased(sField)
field_alias2 = aliased(sField)
distance = label(u'greatcircle', (6371000
* func.acos(
func.cos(
func.radians(lat))
* func.cos(
func.radians(field_alias2.value))
* func.cos(
func.radians(field_alias1.value)
- func.radians(long))
+ func.sin(
func.radians(lat))
* func.sin(
func.radians(field_alias2.value)))))
self.query = self.query.add_columns(distance)
self.query = self.query.join(field_alias1)
self.query = self.query.join(field_alias2)
self.query = self.query.having(
u'greatcircle < %s' % radius).order_by('greatcircle')
expression = and_(field_alias1.name == u'geo.long',
field_alias2.name == u'geo.lat')
self.limit = 20 # XXX: make this passable
elif fieldname == '_limit':
try:
self.limit = int(value)
except ValueError:
pass
self.query = self.query.order_by(
sRevision.modified.desc())
expression = None
elif fieldname == 'text':
if not self.joined_text:
self.query = self.query.join(sText)
self.joined_text = True
if self.fulltext:
expression = (text_(
'MATCH(text.text) '
+ "AGAINST('%s' in boolean mode)" % value))
else:
value = '%' + value + '%'
expression = sText.text.like(value)
elif fieldname in ['modifier', 'modified', 'type']:
if like:
expression = (getattr(sRevision,
fieldname).like(value))
else:
expression = (getattr(sRevision,
fieldname) == value)
else:
if self.in_and:
field_alias = aliased(sField)
self.query = self.query.join(field_alias)
expression = (field_alias.name == fieldname)
if like:
expression = and_(expression,
field_alias.value.like(value))
else:
expression = and_(expression,
field_alias.value == value)
else:
if not self.joined_fields:
self.query = self.query.join(sField)
expression = (sField.name == fieldname)
if like:
expression = and_(expression,
sField.value.like(value))
else:
expression = and_(expression,
sField.value == value)
self.joined_fields = True
else:
expression = (sField.name == fieldname)
if like:
expression = and_(expression,
sField.value.like(value))
else:
expression = and_(expression,
sField.value == value)