Flask SQLalchemy order_by komplexe mathematische Berechnung für Radius basierend auf longitude und latitude

Django, Flask, Bottle, WSGI, CGI…
Antworten
Zoja
User
Beiträge: 145
Registriert: Freitag 28. Februar 2014, 14:04

Hallo zusammen.

Ich habe eine Anwendung, auf der Benutzer Objekte mit einer Adresse reinstellen können, hier speichere ich die dazugehörigen LAT und LNG Werte.

Nun gibt ein suchender Nutzer ins Suchfeld eine Stadt ein und kommt auf eine Ergebnissseite.

Auf dieser Seite möchte ich zuerst die Stadt und alle anderen Städte um Umkreis von 60km zeigen. Das alles habe ich bereits geschafft mit folgender Berechnung:

Code: Alles auswählen

acos(sin(loc_latitude) * sin('zimmer_lat') + cos(loc_latitude) * cos('zimmer_lat') * cos('zimmer_lng' - (loc_longitude))) * 6371 <= 2000
Wobei hier loc_latitude und loc_longitude die LAT und LNG Werte der eingegebenen Stadt sind und zimmer_lat und zimmer_lng die von jedem Zimmer.
Die 2000 sollen 60km sein, weil die Werte alles in allem viel zu hoch sind musste ich es anpassen, aber es funktioniert. Ich habe mehrere Tests gemacht und es werden immer Zimmer im 60~ km Radius geladen.

Wo ich mir jetzt Gedanken mache ist, ob man das nicht auf DB Ebene in der Query bereits lösen kann/sollte, denn was ich mache ist alle Zimmer querien und dann in der Jinja2 begrenze ich die Ergebnisse so:

Code: Alles auswählen

{% if the_acos(the_sin(loc_latitude) * the_sin(zimmer.zimmer_lat) + the_cos(loc_latitude) * the_cos(zimmer.zimmer_lat) * the_cos(zimmer.zimmer_lng - (loc_longitude))) * 6371 <= 2000 %} ... {% endif %}
Außerdem sind die Zimmer unsortiert, d.h. ich muss eigentlich die query vorher noch mit order_by sortieren, dies klappt aber nicht:

So klappt es, hier wird zimmer_lat als ein float erkannt (zimmer_lat ist der column name):

Code: Alles auswählen

all_rooms_in_city = Zimmer.query.join(User).filter_by(bezahlt=True).order_by(asc('zimmer_lat'))
Aber wenn ich die Berechnung reinnehme, wird 'zimmer_lat' als string angesehen, dann kriege ich float expected error und mit einem typecast zu float kriege ich cannot cast string to float error:

Code: Alles auswählen

all_rooms_in_city = Zimmer.query.join(User).filter_by(bezahlt=True).order_by(asc(acos(sin(loc_latitude) * sin('zimmer_lat') + cos(loc_latitude) * cos('zimmer_lat') * cos('zimmer_lng' - (loc_longitude))) * 6371))
Ambesten wäre ist, wenn ich bereits eine query schaffen könnte die es mit dieser Berechnung begrenz und die Ergebnisse nach der Entferung sortiert. Ich bin ganz nah dran, aber dennoch klappt es immer wieder nicht. Vielleicht kennt jemand eine leichtere Lösung?

Danke
BlackJack

@Zoja: Entfernungsberechnung, sortieren, und filtern würde ich im Programm erledigen. Und zwar wirklich im Programm und nicht im Template. Bei der Datenbankabfrage kann man Längen- und Breitengrade effizient auf ein 60km²-Gebiet um den Suchpunkt einschränken.

Sollte das nicht ausreichen was Effizienz angeht, ist die übliche Lösung GIS-Erweiterungen für das DBMS zu verwenden, was dann Datentypen für Punkte und Abfragen zu Entfernungen effizient(er) ermöglicht als alle Punkte durchzugehen.
DasIch
User
Beiträge: 2718
Registriert: Montag 19. Mai 2008, 04:21
Wohnort: Berlin

Mit einem Query kann man das Problem machen. Du musst die Spalten direkt als Attribut des Models referenzieren und nutz sqlalchemy.func um die Datenbank Funktionen für mathematische Berechnungen zu nutzen. Wenn du es in eine nette API verpacken willst schau dir dafür hybrid Methods an.

Grundsätzlich wird es aber zu lange dauern wenn du den Query als Reaktion auf einen Request ausführst. Du solltest die Informationen schon im Cache haben oder in einem Materialized View. Das einzige was du dann machen müsstest ist sortieren und filtern.
Zoja
User
Beiträge: 145
Registriert: Freitag 28. Februar 2014, 14:04

Danke, ich werde mir func() angucken!
Zoja
User
Beiträge: 145
Registriert: Freitag 28. Februar 2014, 14:04

Okay die Lösung sieht folgendermaßen aus, statt

Code: Alles auswählen

filter_by()
einfach

Code: Alles auswählen

filter()
benutzen und alles bereits in der Query machen. Sortierung nach Entfernung klappt auch wunderbar mit

Code: Alles auswählen

order_by()
. Das einzige Problem hier wieder ist, dass ich den Join mit dem User wegmachen musste, jemand da eine Idee?

Alternative Lösung wäre hier alle User zu querien und in der Jinja2 zu vergleichen welche User payed sind und welche nicht, es sollen nämlich erst sortiert die Ergebnisse von bezahlten Usern angezeigt werden gefolgt von den Free Usern. Eigentlich müsste ein Join aber irgendwie funktionieren.

Lösung:

Code: Alles auswählen

all_rooms_in_city = Zimmer.query.filter(func.acos(func.sin(loc_latitude) * func.sin(Zimmer.zimmer_lat) + func.cos(loc_latitude) * func.cos(Zimmer.zimmer_lat) * func.cos(Zimmer.zimmer_lng - (loc_longitude))) * 6371 <= 2000).order_by(asc(func.acos(func.sin(loc_latitude) * func.sin(Zimmer.zimmer_lat) + func.cos(loc_latitude) * func.cos(Zimmer.zimmer_lat) * func.cos(Zimmer.zimmer_lng - (loc_longitude))) * 6371))
Antworten