import requests import pandas as pd import os import json import time from collections import defaultdict # ------------------------------------ # Kategorien # ------------------------------------ CATEGORIES = { "Lebensmittelmarkt": [ ("shop", "supermarket"), ("shop", "marketplace"), ("shop", "convenience"), ("shop", "grocery"), ("shop", "greengrocer"), ("shop", "deli"), ("shop", "farm"), ("shop", "organic") ], "Einzelhandel": [ ("shop", "chemist"), ("shop", "clothes"), ("shop", "electronics"), ("shop", "houseware"), ("shop", "furniture"), ("shop", "hardware"), ("shop", "doityourself"), ("shop", "mobile_phone"), ("shop", "computer"), ("shop", "stationery"), ("shop", "department_store"), ("shop", "variety_store"), ("shop", "general"), ("shop", "kitchen"), ("shop", "appliance"), ("shop", "outdoor"), ("shop", "sports"), ("shop", "shoes"), ("shop", "jewelry"), ("shop", "watch"), ("shop", "bag"), ("shop", "toys"), ("shop", "gift"), ("shop", "florist"), ("shop", "optician"), ("shop", "second_hand"), ("shop", "video"), ("shop", "music"), ("shop", "pet"), ("shop", "books"), ("shop", "newsagent"), ("shop", "photo"), ("shop", "perfumery"), ("shop", "tobacco"), ("shop", "cosmetics"), ("shop", "baby_goods"), ("shop", "medical_supply"), ("shop", "hearing_aids"), ("shop", "fishing"), ("shop", "hunting"), ("shop", "bicycle"), ("shop", "motorcycle"), ("shop", "car"), ("shop", "car_parts"), ("shop", "car_repair"), ("shop", "paint"), ("shop", "lighting"), ("shop", "window_blind"), ("shop", "interior_decoration"), ("shop", "rug"), ("shop", "frame"), ("shop", "ticket"), ("shop", "travel_agency"), ("shop", "music_instruments"), ("shop", "video_games"), ("shop", "anime"), ("shop", "model"), ("shop", "collector"), ("shop", "charity"), ("shop", "seafood") ], "Take-Away-Food und Genussmittel": [ ("shop", "bakery"), ("shop", "butcher"), ("shop", "kiosk"), ("shop", "ice_cream"), ("shop", "chocolate"), ("shop", "tea"), ("shop", "coffee"), ("shop", "confectionery"), ("shop", "alcohol"), ("shop", "beverages"), ("shop", "deli"), ("shop", "greengrocer"), ("amenity", "fast_food"), ("amenity", "food") ], "Dienstleistung des täglichen Bedarfs": [ ("amenity", "fuel"), ("amenity", "bank"), ("amenity", "atm"), ("amenity", "bureau_de_change"), ("amenity", "post_office"), ("amenity", "parcel_locker"), ("shop", "dry_cleaning"), ("shop", "laundry"), ("shop", "tailor"), ("shop", "shoe_repair"), ("amenity", "pharmacy") ], "Kinderbetreuung": [ ("amenity", "school"), ("amenity", "kindergarten"), ("amenity", "nursery"), ("amenity", "childcare") ], "Öffentliche/gemeinnützige Einrichtungen": [ ("amenity", "library"), ("amenity", "townhall"), ("amenity", "public_building"), ("amenity", "community_centre"), ("amenity", "social_facility"), ("office", "ngo"), ("office", "government"), ("amenity", "embassy") ], "Friedhof": [ ("landuse", "cemetery"), ("amenity", "grave_yard") ], "Arztpraxis": [ ("amenity", "doctors"), ("amenity", "dentist"), ("amenity", "clinic"), ("amenity", "hospital"), ("amenity", "physiotherapist"), ("healthcare", "general_practice"), ("healthcare", "specialist"), ("healthcare", "dentist"), ("healthcare", "yes") ], "Schönheits- und Körperpflegeangebote": [ ("shop", "hairdresser"), ("shop", "beauty"), ("shop", "massage"), ("amenity", "spa"), ("shop", "nail_salon"), ("shop", "tanning_salon"), ("shop", "tattoo") ], "Gastronomie": [ ("amenity", "restaurant"), ("amenity", "cafe"), ("amenity", "bar"), ("amenity", "pub"), ("amenity", "biergarten"), ("amenity", "food_court") ], "Kleingewerbe / Dienstleistungsgewerbe": [ ("office", "architect"), ("office", "lawyer"), ("office", "notary"), ("office", "accountant"), ("office", "tax_advisor"), ("craft", "electrician"), ("craft", "photographer"), ("shop", "printing"), ("shop", "copyshop"), ("craft", "locksmith"), ("craft", "plumber"), ("craft", "painter"), ("craft", "glaziery"), ("craft", "carpenter"), ("craft", "shoemaker"), ("shop", "car_repair"), ("amenity", "language_school"), ("amenity", "music_school"), ("amenity", "driving_school"), ("office", "it_services"), ("office", "consulting"), ("office", "advertising_agency") ], "Sport und Freizeit": [ ("leisure", "sports_centre"), ("leisure", "fitness_centre"), ("leisure", "gym"), ("leisure", "yoga"), ("leisure", "martial_arts"), ("leisure", "bowling_alley"), ("leisure", "pitch"), ("leisure", "stadium"), ("leisure", "swimming_pool"), ("leisure", "tennis_court"), ("leisure", "climbing"), ("leisure", "skatepark"), ("leisure", "golf_course"), ("leisure", "darts") ], "Glaubensstätte": [ ("amenity", "place_of_worship"), ("amenity", "church"), ("amenity", "mosque"), ("amenity", "synagogue"), ("amenity", "temple") ], "Naherholungsgebiete": [ ("leisure", "playground"), ("leisure", "park"), ("leisure", "garden"), ("landuse", "forest"), ("landuse", "meadow"), ("landuse", "greenfield"), ("leisure", "track") ], "Senioren- und Pflegeeinrichtungen": [ ("amenity", "nursing_home"), ("amenity", "assisted_living"), ("amenity", "retirement_home"), ("amenity", "care_home") ], "Übernachtungsangebote": [ ("tourism", "hotel"), ("tourism", "guest_house"), ("tourism", "motel"), ("tourism", "hostel"), ("tourism", "resort"), ("building", "hotel") ], "Großer Arbeitgeber": [ ("landuse", "industrial"), ("man_made", "factory"), ("building", "plant"), ("building", "warehouse"), ("building", "office") ], "Park&Ride": [ ("railway", "station"), ("railway", "halt"), ("railway", "tram_stop"), ("railway", "subway_entrance"), ("public_transport", "stop_position"), ("public_transport", "station"), ("public_transport", "platform"), ("highway", "bus_stop") ], "Kulturelle Einrichtungen und Bäder": [ ("amenity", "museum"), ("amenity", "theatre"), ("amenity", "arts_centre"), ("amenity", "cinema"), ("amenity", "gallery"), ("amenity", "cultural_centre"), ("amenity", "aquarium"), ("amenity", "zoo"), ("amenity", "planetarium"), ("amenity", "public_bath") ] } CATEGORY_PRIORITY = list(CATEGORIES.keys()) OVERPASS_SERVERS = [ "https://overpass-api.de/api/interpreter", "https://overpass.kumi.systems/api/interpreter", "https://overpass.openstreetmap.ru/api/interpreter" ] # ------------------------------------ # Hilfsfunktionen # ------------------------------------ def ist_koordinaten_string(text): if not isinstance(text, str): return False teile = text.split(",") if len(teile) != 2: return False try: float(teile[0].strip()) float(teile[1].strip()) return True except ValueError: return False def geocode(address): url = "https://nominatim.openstreetmap.org/search" params = {'q': address, 'format': 'json', 'limit': 1} r = requests.get(url, params=params, headers={"User-Agent": "MikroumfeldAnalyse/1.0"}) r.raise_for_status() data = r.json() if not data: raise ValueError("Adresse nicht gefunden") return float(data[0]['lat']), float(data[0]['lon']) def query_overpass_all(lat, lon, radius_m): tag_filters = [] for cat in CATEGORY_PRIORITY: for key, value in CATEGORIES[cat]: tag_filters.append(f'nwr["{key}"="{value}"](around:{radius_m},{lat},{lon});') query = f""" [out:json][timeout:60]; ( {" ".join(tag_filters)} ); out center tags; """ for server in OVERPASS_SERVERS: try: r = requests.post(server, data=query.encode('utf-8'), headers={"User-Agent": "MikroumfeldAnalyse/1.0"}) r.raise_for_status() return r.json() except requests.exceptions.RequestException: print(f"⚠ Server {server} nicht erreichbar, versuche nächsten...") time.sleep(2) raise ConnectionError("❌ Kein Overpass-Server verfügbar.") def filter_and_count_category(elements, category_key, assigned_ids, details_store, global_seen_names): osm_tags = CATEGORIES[category_key] seen_ids = set() for el in elements: tags = el.get("tags", {}) osm_uid = f"{el['type']}_{el['id']}" name = tags.get("name", "Unbenannt").strip() osm_link = f"https://www.openstreetmap.org/{el['type']}/{el['id']}" unique_key = (name, osm_link) # Kategorieübergreifend doppelt? -> nur bei bekannten Namen filtern if name != "Unbenannt" and unique_key in global_seen_names: continue # Park&Ride doppelte Namen vermeiden if category_key == "Park&Ride": if not ( tags.get("public_transport") in ["stop_position", "platform", "station"] or tags.get("railway") in ["station", "halt", "tram_stop"] or tags.get("station") in ["subway", "light_rail", "tram"] or tags.get("highway") == "bus_stop" ): continue if any(tags.get(k) == v for k, v in osm_tags): seen_ids.add(osm_uid) assigned_ids.add(osm_uid) global_seen_names.add(unique_key) details_store[category_key].append((name, osm_link)) return len(seen_ids), seen_ids def save_to_excel(data, base_path): df = pd.DataFrame(data) base_name = os.path.join(base_path, "mikroumfeld_ergebnisse") i = 1 while os.path.exists(f"{base_name}_{i}.xlsx"): i += 1 df.to_excel(f"{base_name}_{i}.xlsx", index=False) print(f"💾 Excel gespeichert als {base_name}_{i}.xlsx") def save_details_to_json(details, name, base_path): safe_name = name.replace(" ", "_") file_path = os.path.join(base_path, f"{safe_name}.json") with open(file_path, "w", encoding="utf-8") as f: json.dump(details, f, ensure_ascii=False, indent=2) print(f"💾 Details gespeichert als {file_path}") # ------------------------------------ # Hauptprogramm # ------------------------------------ def main(): mode = input("Standorte manuell eingeben oder aus CSV laden? (m/c): ").strip().lower() locations = [] base_path = None if mode == "c": csv_path = input("Pfad zur CSV-Datei: ").strip().strip('"').strip("'") base_path = os.path.dirname(csv_path) df = pd.read_csv(csv_path, sep=";", header=None) for _, row in df.iterrows(): loc_name = str(row[0]).strip() coords = str(row[1]).strip() if ist_koordinaten_string(coords): lat, lon = map(float, coords.split(",")) else: lat, lon = geocode(coords) locations.append((loc_name, lat, lon)) else: base_path = os.getcwd() while True: loc_name = input("Standortname: ").strip() loc_coord = input("Adresse oder 'lat,lon' eingeben: ").strip() if ist_koordinaten_string(loc_coord): lat, lon = map(float, loc_coord.split(",")) else: lat, lon = geocode(loc_coord) locations.append((loc_name, lat, lon)) if input("Weitere Standorteingabe? (j/n): ").strip().lower() != "j": break radius = int(input("Radius in Metern: ")) results = [] for name, lat, lon in locations: print(f"\n🔍 Verarbeite Standort: {name}") data_all = query_overpass_all(lat, lon, radius) elements = data_all.get("elements", []) assigned_ids = set() row_data = {"Standort": name, "Koordinaten": f"{lat},{lon}"} details_store = defaultdict(list) global_seen_names = set() # NEU – ganz am Anfang der Standort-Verarbeitung einfügen for cat in CATEGORY_PRIORITY: count, _ = filter_and_count_category( elements, cat, assigned_ids, details_store, global_seen_names ) row_data[cat] = count results.append(row_data) save_details_to_json(details_store, name, base_path) time.sleep(10) # Wartezeit zwischen Standorten if input("Excel Übersicht erstellen? (j/n): ").strip().lower() == "j": save_to_excel(results, base_path) if __name__ == "__main__": main()