Re: [sqlalchemy] Computed Columns
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") -- 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/-/eZ22zz7xgJEJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Computed Columns
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! -- 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/-/lKQrx809z8IJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Computed Columns
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. On Oct 16, 2012, at 1:30 PM, ThereMichael wrote: > 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! > > > On Tuesday, October 16, 2012 7:20:07 AM UTC-7, rpkelly wrote: > On Tue, Oct 16, 2012 at 07:06:00AM -0700, Michael Wilson wrote: > > 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. > > 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 > > -- > 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 sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Computed Columns
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! On Tuesday, October 16, 2012 7:20:07 AM UTC-7, rpkelly wrote: > > On Tue, Oct 16, 2012 at 07:06:00AM -0700, Michael Wilson wrote: > > 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. > > 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 > -- 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 sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Computed Columns
On Tue, Oct 16, 2012 at 07:06:00AM -0700, Michael Wilson wrote: > 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. 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 -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Computed Columns
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! -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.