Пример #1
0
    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()
Пример #3
0
 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))
Пример #4
0
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
Пример #5
0
    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
Пример #6
0
 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
Пример #7
0
 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)