Python-Code

"""
#359: Stände von Koordinaten (Standort) im Umkreis von x km ermitteln
Erstelle eine Funktion, eventuell mit Hilfe einer Datenbank, welche über die Parameter Location (Latitude & Longitude)
und Radius in Kilometer alle Städte in dem Umkreis mit den Koordinaten ausgibt. Oder eine Liste mit den Koordinaten
in diesem Umkreis. Somit können dann die Städte in einer GeoDatenbank ermittelt werden.
"""

import sqlite3
from geopy.distance import geodesic


def distanz(latvon, lonvon, radius):
    conn = sqlite3.connect('orte.db')
    cursor = conn.cursor()
    table = """CREATE TABLE  IF NOT EXISTS orte(ort VARCHAR(25), lat FLOAT, lon FLOAT);"""

    cursor.execute(table)
    cursor.execute('''INSERT INTO orte VALUES ('Siegen', 50.883331, 8.016667)''')
    cursor.execute('''INSERT INTO orte VALUES ('Erlangen', 49.583332, 11.016667)''')
    cursor.execute('''INSERT INTO orte VALUES ('BergischGladbach', 51.099998, 7.116667)''')
    cursor.execute('''INSERT INTO orte VALUES ('Reutlingen', 48.483334, 9.216667)''')
    cursor.execute('''INSERT INTO orte VALUES ('Bremerhaven', 53.549999, 8.583333)''')
    cursor.execute('''INSERT INTO orte VALUES ('Recklinghausen', 51.584999, 7.161944)''')
    cursor.execute('''INSERT INTO orte VALUES ('Bottrop', 51.524723, 6.922778)''')
    cursor.execute('''INSERT INTO orte VALUES ('Pforzheim', 48.901943, 8.716667)''')
    cursor.execute('''INSERT INTO orte VALUES ('Heilbronn', 49.150002, 9.216600)''')
    cursor.execute('''INSERT INTO orte VALUES ('Ulm', 48.400002, 9.983333)''')
    cursor.execute('''INSERT INTO orte VALUES ('Wuerzburg', 49.783333, 9.933333)''')
    cursor.execute('''INSERT INTO orte VALUES ('Ingolstadt', 48.766666, 11.433333)''')
    cursor.execute('''INSERT INTO orte VALUES ('Paderborn', 51.716667, 8.766667)''')
    cursor.execute('''INSERT INTO orte VALUES ('Herne', 51.549999, 7.216667)''')
    cursor.execute('''INSERT INTO orte VALUES ('Leverkusen', 51.033333, 6.983333)''')
    cursor.execute('''INSERT INTO orte VALUES ('Oldenburg', 53.143890, 8.213889)''')
    cursor.execute('''INSERT INTO orte VALUES ('Muelheim', 51.433334, 6.883333)''')
    cursor.execute('''INSERT INTO orte VALUES ('Hamm', 51.683334, 7.816667)''')
    cursor.execute('''INSERT INTO orte VALUES ('Hagen', 51.366669, 7.483333)''')
    cursor.execute('''INSERT INTO orte VALUES ('Krefeld', 51.333332, 6.566667)''')
    cursor.execute('''INSERT INTO orte VALUES ('Magdeburg', 52.133331, 11.616667)''')
    cursor.execute('''INSERT INTO orte VALUES ('Halle', 51.483334, 11.966667)''')
    cursor.execute('''INSERT INTO orte VALUES ('Aachen', 50.775555, 6.083611)''')
    cursor.execute('''INSERT INTO orte VALUES ('Braunschweig', 52.266666, 10.516667)''')
    cursor.execute('''INSERT INTO orte VALUES ('Mönchengladbach', 51.200001, 6.433333)''')
    cursor.execute('''INSERT INTO orte VALUES ('Gelsenkirchen', 51.516666, 7.100000)''')
    cursor.execute('''INSERT INTO orte VALUES ('Mannheim', 49.488888, 8.469167)''')
    cursor.execute('''INSERT INTO orte VALUES ('Bielefeld', 52.021111, 8.534722)''')
    cursor.execute('''INSERT INTO orte VALUES ('Duisburg', 51.435146, 6.762692)''')
    cursor.execute('''INSERT INTO orte VALUES ('Essen', 51.450832, 7.013056)''')
    cursor.execute('''INSERT INTO orte VALUES ('Duesseldorf', 51.233334, 6.783333)''')
    cursor.execute('''INSERT INTO orte VALUES ('Stuttgart', 48.783333, 9.183333)''')
    cursor.execute('''INSERT INTO orte VALUES ('Freiburg', 47.997791, 7.842609)''')
    cursor.execute('''INSERT INTO orte VALUES ('Tuebingen', 48.521637, 9.057645)''')
    cursor.execute('''INSERT INTO orte VALUES ('Kassel', 51.312801, 9.481544)''')
    cursor.execute('''INSERT INTO orte VALUES ('Bochum', 51.481846, 7.216236)''')
    cursor.execute('''INSERT INTO orte VALUES ('Augsburg', 48.366512, 10.894446)''')
    cursor.execute('''INSERT INTO orte VALUES ('Karlsruhe', 49.006889, 8.403653)''')
    cursor.execute('''INSERT INTO orte VALUES ('Wolfsburg', 52.427547, 10.780420)''')
    cursor.execute('''INSERT INTO orte VALUES ('Dortmund', 51.514244, 7.468429)''')

    data = cursor.execute('''SELECT * FROM orte''')

    print("Folgende Orte sind von", "lat", latvon, "lon", lonvon, "weniger als", radius, "km entfernt:", "\n")
    for row in data:
        city = row[0]
        lati = row[1]
        long = row[2]
        origin = (latvon, lonvon)
        dist = (lati, long)
        entf = geodesic(origin, dist).kilometers
        if entf < radius:
            print(city, "=", entf, "km, lat", lati, "long", long)

    conn.commit()
    cursor.execute("DROP TABLE orte")


lavon = 51.099998
lovon = 7.116667
radiu = 50
distanz(lavon, lovon, radiu)