Abfrage der minimalsten Distanz

Hallo!

Ich habe die Aufgabe in einem Projekt ein Datenbank zu erstellen, Daten zu importieren usw. Das alles ist bereits geschehen. Kurz zu den relevanten Daten:

Es handelt sich um Stationen, die über ganz Deutschland verteilt sind. Nun muss ich über eine SQL-Abfrage herausfinden, welche Stationen die minimalste Distanz voneinander aufweisen, also sich am nächsten liegen. Habt ihr eine Ahnung, wie das möglich ist?


Vielen Dank schonmal

Google bitte nach “knn postgresql”. Das macht eine indexbasierte Umkreissuche.

Ansonsten müßtes Du die Distanz zwischen allen Stationen zueinander ermitteln und danach sortieren.

OK, aber wenn ich das richtig sehe, dann könnte man hier doch nur ausgehend von bestimmten Orten suchen lassen oder? Ich möchte ja die geringste Distanz haben, die zwischen allen Punkten existiert.

Ja, hab ich schon so verstanden, siehe meinen zweiten Satz.

Du hast:

test=*# select * from orte ;
 id | name | pos
----+------+-----
  1 | ort1 | 111
  2 | ort2 | 222
  3 | ort3 | 333
  4 | ort4 | 444
  5 | ort5 | 555
  6 | ort6 | 666
(6 rows)

und suchst:

test=*# select o1.name, o2.name, o1.pos-o2.pos from orte o1 cross join orte o2 where o1.id<o2.id;
 name | name | ?column?
------+------+----------
 ort1 | ort2 |     -111
 ort1 | ort3 |     -222
 ort1 | ort4 |     -333
 ort1 | ort5 |     -444
 ort1 | ort6 |     -555
 ort2 | ort3 |     -111
 ort2 | ort4 |     -222
 ort2 | ort5 |     -333
 ort2 | ort6 |     -444
 ort3 | ort4 |     -111
 ort3 | ort5 |     -222
 ort3 | ort6 |     -333
 ort4 | ort5 |     -111
 ort4 | ort6 |     -222
 ort5 | ort6 |     -111
(15 rows)

Anstatt o1.pos-o2.pos kommt da die Funktion zur Distanzberechnung hin, nach dieser Spalte sortierst Du dann.

Ok, aber der cross join der hier für zwei Orte durchgeführt wird, müsste ja dann für 80+ Orte gemacht werden… MIr ist unklar wie das aussehen soll.

Schau es Dir doch mal genau an. In der Tabelle orte sind 6 orte, nicht 2. Der Cross-Join erstellt alle möglichen sinnvollen (!) Kombinationen.

Dieser Befehl hat jetzt funktioniert:

select s1.stationsna, s2.stationsna, st_distance(s1.geom,s2.geom)
from stationen as s1 cross join stationen as s2
where s1.stationsna not like s2.stationsna
order by st_distance

Allerdings wird die Distanz nicht in Metern ausgegeben, sondern im Format “0,0056165” etc.
Muss hier zuvor eine Umrechnung von Geometry in Geography erfolgen oder ist dies auch während der Abfrage möglich?


Danke!

Dein Where führt dazu, daß Ort1 ↔ Ort2 und Ort2 ↔ Ort1 berechnet wird, also Du machst Dir doppelten Rechenaufwand, denn man kann davon ausgehen, daß Hin- und Rückweg mehr oder weniger gleich sein sollte.

Ich habe den Befehl jetzt nochmal mit st_distance_sphere statt st_distance durchgeführt und nun wird die Gradangabe in Meter umgerechnet.

Den Where-Befehl habe ich eingebaut, um zu verhindern, dass die Distanz zwischen ein und derselben Station berechnet wird. Wie kann ich nun diese von dir angesprochene doppelte Berechnung unterbinden?

Vielen Dank schonmal bis hierhin.

Lies meine Antwort vom 19 Aug 2014 14:29

Ah ok jetzt hat es funktioniert! Vielen Dank für deine Hilfe!

Wenn ich nun die minimale Distanz haben möchte, sollte es doch mit “min” vor der st_distance Funktionier klappen, oder?

Wohin kann ich nun die Rechnung schicken?

Ach das war doch ein Leichtes für Dich! :wink:

Ja, und? Ich habe Frau und 3 Kinder…