Moin,

Am 03.10.22 um 14:13 schrieb Luca Bertoncello:

Hallo Leute!

Irgendwie habe ich jetzt eine Denkfehler und komme nicht weiter...

Also, sagen wir, dass ich eine Tabelle "airports" habe, mit u.a. die Koordinaten (Latitude und Longitude) der Flugplätzen. Ich will den nächstgelegen Flugplatz einem Punkt suchen, von dem ich ebenso die Koordinaten habe.

Dein 'Denkfehler' ist offensichtlich die Nicht-Nutzung von PostGIS, welches exakt dafür eine indexbasierte Umkreissuche besitzt.

https://postgis.net/workshops/postgis-intro/knn.html

Andreas

Am 03.10.22 um 14:13 schrieb Luca Bertoncello:

Irgendwie habe ich jetzt eine Denkfehler und komme nicht weiter... [...] Mit dieser Anfrage hole ich die Flugplatzdaten zusammen mit den Koordinatendifferenzen:

SELECT *, ABS(latitude - 51.04952025) AS latdiff, ABS(longitude - 13.68193347) AS lngdiff FROM airports;

Nun will ich den Datensatz finden, bei dem latdiff = MIN(latdiff) und lngdiff = MIN(lngdiff).

Ähh, nein. Diesen Flughafen gibt es wahrscheinlich nicht. Du willst nicht den künstlichen Flughafen, der aus dem minimalen horizontalen und minimal vertikalen Abstand zu einem Bezugspunkt besteht (das sind nämlich aller Wahrscheinlichkeit nach zwei verschiedene Flughäfen!), sondern den, bei dem die Summe der Quadrate beider Werte am kleinsten ist (Pythagoras, Hypotenuse und so).¹

So wäre meine Anfrage:

SELECT *, ABS(latitude - 51.04952025) AS latdiff, ABS(longitude - 13.68193347) AS lngdiff FROM airports GROUP BY id HAVING latdiff = min(latdiff) AND lngdiff = min(lngdiff);

Aber das gibt mir völlig flasche Ergebnisse, denn die Koordinaten, die ich hier nutze, sind in Dresden Cotta und der Flughafen Aachen ist deutlich weiter als den Flughafen Dresden, von mir gesehen...

Kann jemand mir sagen, wo mein Denkfehler liegt?

Mein SQL ist schon arg eingerostet, aber eventuell das (ungetestet!):

SELECT *, latitude - 51.04952025 AS latdiff, longitude - 13.68193347 AS lngdiff FROM airports ORDER BY latdiff*latdiff + lngdiff*lngdiff LIMIT 1;

Durch die Multiplikation können wir uns auch das ABS() sparen.

Viele Grüße, Mathias

¹ Ja, ich ignoriere wohl wissend, dass die Erde rund^Wbuckelig ist :P

On Mon, Oct 3, 2022 at 2:35 PM Luca Bertoncello lucabert@lucabert.de wrote:

Hallo Leute!

Irgendwie habe ich jetzt eine Denkfehler und komme nicht weiter...

Also, sagen wir, dass ich eine Tabelle "airports" habe, mit u.a. die Koordinaten (Latitude und Longitude) der Flugplätzen. Ich will den nächstgelegen Flugplatz einem Punkt suchen, von dem ich ebenso die Koordinaten habe.

Mir ist klar, dass um richtig genau zu sein, die Datenbank Funktionen enthalten muss, die die Entfernung zwischen zwei Punkte berechnen, und das haben die meisten DB-Engines nicht. Sicherlich nicht SQLite auf Android...

Aber für meine Zwecke würde es reichen, wenn ich den Flugplatz finde, bei dem die Differenz zwischen den eigenen Koordinaten und den Punktkoordinaten am kleinsten ist.

Irgendwie komme ich aber nicht klar...

Mit dieser Anfrage hole ich die Flugplatzdaten zusammen mit den Koordinatendifferenzen:

SELECT *, ABS(latitude - 51.04952025) AS latdiff, ABS(longitude - 13.68193347) AS lngdiff FROM airports;

Nun will ich den Datensatz finden, bei dem latdiff = MIN(latdiff) und lngdiff = MIN(lngdiff). So wäre meine Anfrage:

SELECT *, ABS(latitude - 51.04952025) AS latdiff, ABS(longitude - 13.68193347) AS lngdiff FROM airports GROUP BY id HAVING latdiff = min(latdiff) AND lngdiff = min(lngdiff);

Aber das gibt mir völlig flasche Ergebnisse, denn die Koordinaten, die ich hier nutze, sind in Dresden Cotta und der Flughafen Aachen ist deutlich weiter als den Flughafen Dresden, von mir gesehen...

Kann jemand mir sagen, wo mein Denkfehler liegt?

Schau mal, vielleicht hilft dir das hier: https://git.osuv.de/m/dotfiles/src/branch/master/mariadb/distance.sql https://git.osuv.de/m/dotfiles/src/branch/master/mariadb/deg2rad.sql

Ob sqlite auch funktionen kann wie mariadb etc, ...keine ahnung.

Danke Luca Bertoncello (lucabert@lucabert.de)