Geocoding aus PLZ + Stadt

Forumthread: Geocoding aus PLZ + Stadt

27.11.2017 12:12:59

Kian

Hallo zusammen,
ich möchte aus einer relativ langen Liste von Locations, die jeweils mit PLZ/ Stadt/ Land angegeben sind, die Lat und Lon in zwei Spalten daneben schreiben. Da ich bisher mit Geocoding keine Erfahrungen gemacht habe, wollte ich mal fragen, ob man das per Makro umsetzen kann. Die Liste hat mehrere hundert Einträge, was eine manuelle Suche der Koordinaten unmöglich macht. Nach der Internetrecherche konnte ich dazu finden, dass es wohl per GoogleMaps API geht. An der Umsetzung der Beispiele bin ich leider gescheitert -.-
Eigentlich geht es nur darum die Liste durch die Koordinaten der Städte zu ergänzen. Daher würde es auch reichen die PLZ oder den Städtenamen bei Google anzugeben oder?
Bin für jegliche Tipps dankbar.
Kian
Einen schönen Tag ansonsten!

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

über 11.000 Koordinaten deutscher Orte

27.11.2017 12:35:37

siehe:
https://www.destatis.de/DE/ZahlenFakten/LaenderRegionen/Regionales/Gemeindeverzeichnis/Gemeindeverzeichnis.html
Da bei Quartalsausgabe - liegt sogar in Excel vor.
WF

AW: über 11.000 Koordinaten deutscher Orte

27.11.2017 13:28:25

Kian

danke schon mal für den Ansatzpunkt. Leider handelt es sich um eine internationale Liste, nicht nur Deutschland. War nicht klar formuliert von meiner Seite.

und noch ca. 900 Koordinaten internationaler Orte

27.11.2017 14:07:12

immer Flughäfen - unsere:
http://www.excelformeln.de/tips.html?welcher=41
WF

AW: und noch ca. 900 Koordinaten internationaler Orte

01.12.2017 12:23:59

Kian

Danke für das Feedback. Nicht ganz was ich gesucht hatte, aber ich bin mittlerweile fündig geworden.
Anbei die Funktion, die per Request Long und Lat raussucht, falls jemand das selbe Problem hat.

Option Explicit
Function getGoogleMapsGeocode(sAddr As String) As String
Dim xhrRequest As XMLHTTP60
Dim sQuery As String
Dim domResponse As DOMDocument60
Dim ixnStatus As IXMLDOMNode
Dim ixnLat As IXMLDOMNode
Dim ixnLng As IXMLDOMNode
' Use the empty string to indicate failure
getGoogleMapsGeocode = ""
Set xhrRequest = New XMLHTTP60
sQuery = "http://maps.googleapis.com/maps/api/geocode/xml?sensor=false&address="
sQuery = sQuery & Replace(sAddr, " ", "+")
xhrRequest.Open "GET", sQuery, False
xhrRequest.send
Set domResponse = New DOMDocument60
domResponse.LoadXML xhrRequest.responseText
Set ixnStatus = domResponse.SelectSingleNode("//status")
If (ixnStatus.Text  "OK") Then
Exit Function
End If
Set ixnLat = domResponse.SelectSingleNode("/GeocodeResponse/result/geometry/location/lat")
Set ixnLng = domResponse.SelectSingleNode("/GeocodeResponse/result/geometry/location/lng")
getGoogleMapsGeocode = ixnLat.Text & ", " & ixnLng.Text
End Function

Infobox / Tutorial

Geocoding aus PLZ und Stadt in Excel umsetzen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um aus einer Liste von PLZ und Städten die entsprechenden Koordinaten zu erhalten, kannst Du die Google Maps API in Excel nutzen. Folge diesen Schritten:

VBA-Editor öffnen: Drücke ALT + F11, um den VBA-Editor zu öffnen.
Neues Modul einfügen: Klicke auf Einfügen > Modul, um ein neues Modul zu erstellen.

Code einfügen: Kopiere den folgenden Code in das neue Modul:

Option Explicit
Function getGoogleMapsGeocode(sAddr As String) As String
   Dim xhrRequest As XMLHTTP60
   Dim sQuery As String
   Dim domResponse As DOMDocument60
   Dim ixnStatus As IXMLDOMNode
   Dim ixnLat As IXMLDOMNode
   Dim ixnLng As IXMLDOMNode

   getGoogleMapsGeocode = ""
   Set xhrRequest = New XMLHTTP60
   sQuery = "http://maps.googleapis.com/maps/api/geocode/xml?sensor=false&address="
   sQuery = sQuery & Replace(sAddr, " ", "+")
   xhrRequest.Open "GET", sQuery, False
   xhrRequest.send
   Set domResponse = New DOMDocument60
   domResponse.LoadXML xhrRequest.responseText
   Set ixnStatus = domResponse.SelectSingleNode("//status")
   If (ixnStatus.Text <> "OK") Then Exit Function
   Set ixnLat = domResponse.SelectSingleNode("/GeocodeResponse/result/geometry/location/lat")
   Set ixnLng = domResponse.SelectSingleNode("/GeocodeResponse/result/geometry/location/lng")
   getGoogleMapsGeocode = ixnLat.Text & ", " & ixnLng.Text
End Function

Schließe den VBA-Editor: Drücke ALT + Q, um zurück zu Excel zu gelangen.
Anwendung der Funktion: In einer Zelle kannst Du nun die Funktion verwenden, um die Adresse in Koordinaten umzuwandeln. Beispiel: =getGoogleMapsGeocode(A1) (wobei in A1 die PLZ und Stadt stehen).

Häufige Fehler und Lösungen

Fehler 1: Funktion gibt keine Ergebnisse zurück.
- Lösung: Stelle sicher, dass die Adresse korrekt formatiert ist und überprüfe die Internetverbindung.
Fehler 2: „Status“ gibt nicht „OK“ zurück.
- Lösung: Dies kann daran liegen, dass die eingegebene Adresse nicht gefunden werden kann. Überprüfe die Eingabe auf Tippfehler.
Fehler 3: „XMLHTTP60“ nicht erkannt.
- Lösung: Du musst möglicherweise die Microsoft XML, v6.0-Bibliothek aktivieren. Gehe zu Extras > Verweise im VBA-Editor und wähle die Bibliothek aus.

Alternative Methoden

Online-Dienste: Es gibt verschiedene Webseiten, die Geocoding-Dienste anbieten, wo Du manuell PLZ und Stadt eingeben kannst, um die Koordinaten zu erhalten.
Excel Add-Ins: Einige Excel-Add-Ins ermöglichen das Geocoding direkt innerhalb von Excel, ohne VBA-Codierung.
Verwendung von Destatis: Für deutsche PLZ kannst Du die Daten aus dem Destatis Gemeindeverzeichnis herunterladen.

Praktische Beispiele

Beispiel 1: Um die Koordinaten für „10115 Berlin, Deutschland“ zu erhalten, gib in eine Zelle ein:
```
=getGoogleMapsGeocode("10115 Berlin, Deutschland")
```
Beispiel 2: Für eine internationale Adresse wie „SW1A 1AA London, UK“:
```
=getGoogleMapsGeocode("SW1A 1AA London, UK")
```
Beispiel 3: Wenn Du mehrere Adressen hast, ziehe die Formel nach unten, um die Koordinaten für alle Einträge zu erhalten.

Tipps für Profis

Batch-Verarbeitung: Nutze Excel-Tabellen, um eine große Anzahl von Adressen gleichzeitig zu verarbeiten. Achte darauf, die API-Nutzungsgrenzen zu beachten.
Fehlerüberprüfung: Implementiere eine Fehlerüberprüfung in Deiner Funktion, um ungültige Adressen zu identifizieren.
Optimiere die API-Anfragen: Reduziere die Anzahl der Anfragen, indem Du Adressen gruppierst oder nur relevante Daten abfragst.

FAQ: Häufige Fragen

1. Frage
Wie kann ich die Koordinaten in eine Adresse umwandeln?
Antwort: Du kannst die Google Maps API auch verwenden, um Koordinaten in Adressen umzuwandeln. Nutze dafür die entsprechende API-Funktion.

2. Frage
Sind die Koordinaten immer genau?
Antwort: Die Genauigkeit der Koordinaten hängt von der eingegebenen Adresse ab. Manchmal kann es zu kleinen Abweichungen kommen.

3. Frage
Wie viele Anfragen kann ich pro Tag an die Google Maps API senden?
Antwort: Die Google Maps API hat bestimmte Nutzungslimits. Diese sind abhängig von Deinem API-Schlüssel und den von Dir genutzten Diensten. Überprüfe die Google-Dokumentation für Details.

Beliebteste Forumthreads (12 Monate)

Forumthread: Geocoding aus PLZ + Stadt

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Geocoding aus PLZ und Stadt in Excel umsetzen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Geocoding aus PLZ + Stadt

Forumthread: Geocoding aus PLZ + Stadt

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Geocoding aus PLZ und Stadt in Excel umsetzen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread