Computed Columns

[Michael Wilson]

Hi,

I've like to include the distance of a particular row (object) from a given point in a SQLAlchemy query using the haversine formula, and SORT on the distance, similar to this example:

http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula

But can't figure out how to do it. It seems like it's a combination of a .label('distance') and .text(…) but I can't find a good example.

My incoming rows have a longitude and latitude column, and I'd like to be able to include the origin (37, -122 in the example) as parameters at runtime.

Thanks!

[Ryan Kelly]

Completely untested, but hopefully sends you in the right direction:

from sqlalchemy import func, literal, Column, Integer, Numeric, MetaData

metadata = MetaData()
markers = Table("markers", metadata,
Column("id", Integer, primary_key=True),
Column("lat", Numeric),
Column("lng", Numeric),
)

origin_lat = 37
origin_lng = -122

distance = (
3959
* func.acos(func.cos(func.radians(literal(origin_lat))))
* func.cos(func.radians(markers.lat))
* func.cos(func.radians(markers.lng) - func.radians(literal(origin_lng)))
+ func.sin(func.radians(literal(origin_lat)))
* func.sin(func.radians(markers.lat))
)

query = (
session.query(markers.lat, markers.lng, distance.label("distance"))
.having(distance < literal(25))
.order_by(distance)
.limit(20)
)

-Ryan Kelly

[ThereMichael]

Thanks! 

Your example worked perfectly.

But to simplify the question, I left out a wrinkle. My tables look roughly like this:

mapper(Thing, things_table,properties={

'place_id' : things_table.c.place_id,

..

'place' : relation(Place,

          primaryjoin=(things_table.c.place_id==places_table.c.id)),

})

And then my query would be:

things = session.query(Thing).options(eagerload(place)).order_by(...).all()

Your solution was perfect, but it didn't end up applying as expected to the existing primaryjoin.

Any ideas how to make it work in this fashion?

Thanks again!

[Michael Bayer]

it's not going to be simple as relationship() doesn't render HAVING, it would have to be embedded in a subquery and then set into the relationship as a "secondary" table or something similar - this is assuming the lat/long columns in question are on "places_table".  There are also special recipes that are used to get parameters into a relationship() but overall this is not the easy way to go and not for a specific query you only need in once place (or two).  Especially with the eager loading, easier to just specify what you're looking for at query time.

If you just need to do this in isolation it would be better to render out the query using session.query(Thing).join(Thing.places).having(distance < 25), and then contains_eager(Thing.places).

The above is just an approximation.  If you can work out the SQL we can show you how to make query(Thing) do it exactly.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/XpMNDBBcmhMJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[ThereMichael]

I got it to work. Here's what I did:

   distance_function = ( 

        3959 

        * func.acos(func.cos(func.radians(bindparam('origin_lat')))) 

        * func.cos(func.radians(places_table.c.latitude)) 

        * func.cos(func.radians(places_table.c.longitude) - func.radians(bindparam('origin_lng'))) 

        + func.sin(func.radians(bindparam('origin_lat'))) 

        * func.sin(func.radians(places_table.c.latitude)) 

      ) 

    mapper(Place, places_table, properties={

      'id':places_table.c.id,

      'longitude' : places_table.c.longitude,

      'latitude' : places_table.c.latitude,

      'distance' : column_property(distance_function)

      }

    )

(Places are joined with things, as before)

And then:

    if sort_by == 'alphabetical':

      order_by    = asc(Thing.title)

      bind_params = {} 

    elif sort_by == "location":

      order_by    = asc(Place.distance)

      bind_params = {'origin_lat':37, 'origin_lng':-122}

    else:

      order_by    = desc(Thing.creation)

      bind_params = {}

     things = session.query(Thing).\

        options(eagerloads).\

        join(Look.place).\

        ...

        params(bind_params).\

        all()

SQLAlchemy is cooler every time I use it!

Thanks to everyone for their help!

[ThereMichael]

The "distance" function should be this:

    distance_function = ( 

        (6371 

        * func.acos(func.cos(func.radians(bindparam('origin_lat')))

                    * func.cos(func.radians(places_table.c.latitude))

                    * func.cos(func.radians(places_table.c.longitude) - 

                               func.radians(bindparam('origin_lng'))

                              ) 

                    + func.sin(func.radians(bindparam('origin_lat'))) 

                    * func.sin(func.radians(places_table.c.latitude))

                  )

        ) 

      ) 

(We're taking the acos of the whole shebang, not just the "origin_lat")