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.
