3

I am currently trying to implement a calculation for a radius:

I have a DB with rooms, every single room has a different adress and also a latiude and longitude.

A user can enter a cityname in a searchform to search for rooms in this city. Here I want to show a result page with rooms in the searched city + all rooms in other cities in a 60 km radius from the searched city.

Here I found some solutions which I would like to try, but I cant figure out how to translate this into an SQLalchemy query:

SELECT * FROM Places WHERE acos(sin(1.3963) * sin(Lat) + cos(1.3963) * cos(Lat) * cos(Lon - (-0.6981))) * 6371 <= 60;

So theoretically I have to replace 1.3963 and -0.6981 with the latitude and longitude of the searched city and I will get what I want.

Roman
  • 3,563
  • 5
  • 48
  • 104

3 Answers3

4

Here is the solution in sqlalchemy:

loc is the location which someone searches for. For example a user entering a cityname.

Zimmer is my table, where all DB entries are, every Zimmer has a lat and lng, these are used to calculate the radius of the input loc. To limit the radius we use <= 100 (for 100km).

all_rooms_in_city = Zimmer.query.filter(func.acos(func.sin(func.radians(loc_latitude)) * func.sin(func.radians(Zimmer.zimmer_lat)) + func.cos(func.radians(loc_latitude)) * func.cos(func.radians(Zimmer.zimmer_lat)) * func.cos(func.radians(Zimmer.zimmer_lng) - (func.radians(loc_longitude)))) * 6371 <= 100)

Ordering is also possible by simply putting the same code in an order_by() and leave out the limiting <= 100 radius:

.order_by(asc(func.acos(func.sin(func.radians(loc_latitude)) * func.sin(func.radians(Zimmer.zimmer_lat)) + func.cos(func.radians(loc_latitude)) * func.cos(func.radians(Zimmer.zimmer_lat)) * func.cos(func.radians(Zimmer.zimmer_lng) - (func.radians(loc_longitude)))) * 6371))

Dont forget here to import func from sqlalchemy, because otherwise the math methods wont work in the query. If you have saved your lat and lng in a Radians Column in your database you dont need the typecast. I used func.radians() because my lat and lng where saved in a Float Column

Roman
  • 3,563
  • 5
  • 48
  • 104
0

you can use SQL alchemy's from_statement(statement) method, as described here :

http://docs.sqlalchemy.org/en/latest/orm/query.html

With this, you do not need to translate this to SQL Alchemy "syntax", the QueryAPI will do it for you.

So you could do something like:

q = session.query(PlacesClass).from_statement(
    'SELECT * FROM Places WHERE acos(sin(1.3963) * sin(Lat) + '\
    'cos(1.3963) * cos(Lat) * cos(Lon - (-0.6981))) * 6371 <= 60;')
BaL
  • 93
  • 1
  • 8
0

Though @Roman's answer above give me the basic idea how to achieve this, however, his solution didn't work for me. So, here is my solution:

Location.query.filter(
    (func.degrees(
        func.acos(
            func.sin(func.radians(lat)) * func.sin(func.radians(Location.latitude)) + 
            func.cos(func.radians(lat)) * func.cos(func.radians(Location.latitude)) * 
            func.cos(func.radians(lon-Location.longitude))
        )
    ) * 60 * 1.1515 * 1.609344) <= distance)

Here lat and lon is the given coords from where I was calculating the distance. The distance was limited by given distance in the query. This query is calculating the distance in km, if you want to calculate in mile, remove the * 1.609344 from the query.

moshfiqur
  • 2,065
  • 3
  • 24
  • 27