Python - SqlAlchemy: Filter query by great circle distance?
Question: I am using Python and Sqlalchemy to store latitude and longitude values in a
Sqlite database. I have created a [hybrid
method](http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html) for my
Location object,

    
    
    @hybrid_method
    def great_circle_distance(self, other):
        """
        Tries to calculate the great circle distance between the two locations
    
        If it succeeds, it will return the great-circle distance
        multiplied by 3959, which calculates the distance in miles.
    
        If it cannot, it will return None.
    
        """
        return math.acos(  self.cos_rad_lat 
                         * other.cos_rad_lat 
                         * math.cos(self.rad_lng - other.rad_lng)
                         + self.sin_rad_lat
                         * other.sin_rad_lat
                         ) * 3959
    

All the values like `cos_rad_lat` and `sin_rad_lat` are values I pre-
calculated to optimize the calculation. Anyhow, when I run the following
query,

    
    
    pq = Session.query(model.Location).filter(model.Location.great_circle_distance(loc) < 10)
    

I get the following error,

    
    
    line 809, in great_circle_distance
        * math.cos(self.rad_lng - other.rad_lng)
    TypeError: a float is required
    

When I print the values for `self.rad_lng` and `other.rad_lng` I get, for
example,

    
    
    self.rad_lng: Location.rad_lng 
    other.rad_lng: -1.29154947064
    

What am I doing wrong?


Answer: You can't really use the `math` module that way:

    
    
    >>> c = toyschema.Contact()
    >>> c.lat = 10
    >>> c.lat
    10
    >>> import math
    >>> math.cos(c.lat)
    -0.83907152907645244
    >>> math.cos(toyschema.Contact.lat)
    Traceback (most recent call last):
      File "<stdin>", line 1, in <module>
    TypeError: a float is required
    

You'll have combine `sqalchemy.func.*` in place of `math.*` in a
[`@great_circle_distance.expression`
method](http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html#defining-
expression-behavior-distinct-from-attribute-behavior) for all of that kind of
cleverness. Unfortunately, you can't do that with sqlite, either; it [doesn't
provide trig functions](http://www.sqlite.org/lang_corefunc.html) You could
use PostgreSQL, which does, or you can try to [add these functions to sqlite
yourself:](http://www.sqlite.org/c3ref/create_function.html)

**EDIT** It's actually not to hard to add functions to sqlite: This is _NOT_
tested.

Have to add the math functions to sqlite:

    
    
    engine = sqlalchemy.create_engine("sqlite:///:memory:/")
    raw_con = engine.raw_connection()
    raw_con.create_function("cos", 1, math.cos)
    raw_con.create_function("acos", 1, math.acos)
    
    class Location(...):
        ...
        @hybrid_method
        def great_circle_distance(self, other):
            """
            Tries to calculate the great circle distance between 
            the two locations by using the Haversine formula.
    
            If it succeeds, it will return the Haversine formula
            multiplied by 3959, which calculates the distance in miles.
    
            If it cannot, it will return None.
    
            """
            return math.acos(  self.cos_rad_lat 
                             * other.cos_rad_lat 
                             * math.cos(self.rad_lng - other.rad_lng)
                             + self.sin_rad_lat
                             * other.sin_rad_lat
                             ) * 3959
    
        @great_circle_distance.expression
        def great_circle_distance(cls, other):
            return sqlalchemy.func.acos(  cls.cos_rad_lat 
                             * other.cos_rad_lat 
                             * sqlalchemy.func.cos(cls.rad_lng - other.rad_lng)
                             + cls.sin_rad_lat
                             * other.sin_rad_lat
                             ) * 3959
    

