Geocoding mit Excel

Ich habe mich entschlossen, das ohnehin vorhandene Excel zu benutzen. Die meisten Adressbücher können nach CSV exportieren, und damit ist eine recht einfache Konvertierung gewährleistet.


Die Kommunikation mit der Yahoo-API ist simpel: In der URL gibt man die zu konvertierende Adresse an, und bekommt Längen- und Breitengrad sowie weitere Angaben in der Antwort im XML-Format.

Um die API zu nutzen, ist ein Entwicklerschlüssel notwendig, den man kostenlos beantragen kann. Tipp: Für Tests tut es auch die ID „YahooDemo“… Leider geht YahooDemo nicht mehr

Um die XML-Antwort verarbeiten zu können, verwende ich die Microsoft Core XML Services (MSXML) in der Version 6.0 (ob andere Versionen auch gehen, habe ich nicht getestet).

Die Funktion, um die Yahoo-API anzusprechen, sieht damit so aus:

Private Function Geocode(Address As String) As Collection
    ' Bei Yahoo eine ID beantragen
    Const APPID = "XXXXXXXXX"

    Const YahooURL = "http://local.yahooapis.com/MapsService/V1/geocode"

    Dim url As String
    Dim Request As XMLHTTP
    Dim Response As DOMDocument
    Dim Section As IXMLDOMNode
    Dim Node As IXMLDOMNode

    ' Adresse basteln
    url = YahooURL & "?appid=" & APPID & "&location=" & Address

    ' Abfrage der Adresse
    Set Request = New XMLHTTP

    Request.Open "get", url, False
    Request.send

    ' Antwort verarbeiten
    Set Response = New DOMDocument
    Response.loadXML Request.responseText

    ' XML-Antwort analysieren
    Set Section = Response.selectSingleNode("//ResultSet/Result")

    Set Geocode = New Collection
    For Each Node In Section.childNodes
        Geocode.Add Node.Text, Node.nodeName
    Next

    Geocode.Add Section.Attributes.getNamedItem("precision").Text, "Precision"

    ' Ressourcen freigeben
    Set Section = Nothing
    Set Response = Nothing
    Set Request = Nothing
End Function

Damit bekommt man z.B. den Breitengrad mit der folgenden Funktion:

Public Function Latitude(Address As String) As Double
    Dim coords As Collection
    Set coords = Geocode(Address)

    Latitude = Val(coords("Latitude"))
End Function

Der Charme dieser Lösung besteht darin, dass die Funktionen jetzt den Excel-Umfamg erweitern. Wenn also in A1 die Straße und in B1 der Ort steht, so liefert mir

=Latitude(A1 & " " & B1)

den Breitengrad dieser Adresse.

' Ermittelt den Längengrad einer Adresse
Public Function Longitude(Address As String) As Double
    Dim coords As Collection
    Set coords = Geocode(Address)
    Longitude = Val(coords("Longitude"))
End Function

' Ermittelt die Straße einer Adresse
Public Function Street(Address As String) As String
    Dim coords As Collection
    Set coords = Geocode(Address)
    Street = coords("Address")
End Function

' Ermittelt die Stadt einer Adresse
Public Function City(Address As String) As String
    Dim coords As Collection
    Set coords = Geocode(Address)
    City = coords("City")
End Function

' Ermittelt die Präzision des Geocodings einer Adresse
Public Function Precision(Address As String) As String
    Dim coords As Collection
    Set coords = Geocode(Address)
    Precision = coords("Precision")
End Function

Die so erhaltenen Werte eignen sich perfekt, um beispielsweise eine ASC-Datei zu erzeugen, aus der man mit PoiEdit eine TomTom-OVI machen kann.

6 Antworten auf „Geocoding mit Excel“

  1. Hallo Jörg,
    ich suche genau eine solche Lösung, wie du in „Geocoding mit Excel“ aufzeigst. Ich bekomme es aber nicht zum laufen 🙁
    Ich erhalte eine Fehlermeldung sobald die „falsche?“ URL mit request.send senden möchte.
    Die URL im VBA-code wird ja so nicht als String akzeptiert. Ich habe unterschiedliche urls getestet und bekomme im Browser (firefox) auch richtige Ergebisse.
    (zb. url = YahooURL & „?appid=%22“ & APPID & „%22“ & Chr(38) & „location=“ & Address)

    Kannst du mir einen Tipp geben was ich falsch mache?
    Vielen Dank
    Matze

  2. Super Sache, nach der ich lange gegoogelt habe, werde ich mal ausprobieren. Danke Dir schon jetzt.
    Gruß
    Micha

  3. Hallo!

    Seit heute funktioniert diese Methode scheinbar nicht mehr. Sieht so aus als hätte Yahoo die Maps API eingestellt:
    http://developer.yahoo.com/maps/

    Wie komme ich nun mit OpenOffice/LibreOffice Calc an die Koordinaten einer Adresse ran? Hat irgendjemand Ideen bezüglich GoogleMaps, Openstreetmaps, OviMaps, o.ä.?

    Schöne Grüße,
    Dirk

  4. Hallo!

    Mittlerweile habe ich eine Lösung gefunden: Google bietet eine ähnliche Funktion wie yahoo. Sendet man eine Anfrage an

    http://maps.google.com/maps/geo?output=xml&sensor=false&key=abcdefg&q=

    gefolgt von der Straßenadresse, dann bekommt man wie bei yahoo eine XML-Datei zurück.

    Für alle, die es interessiert, anbei der Libreoffice-Code, der die Koordinaten in sCoordinates ausspuckt.
    (Vorsicht mit sLatitude und sLongitude: das sind nur extrem rudimentär ausgewertete Substrings, die auf unsere Situation zugeschnitten sind (Längengrad hat bei uns immer eine Stelle vor dem Komma, Breitengrad immer zwei).)

    Hoffe, es hilft.
    Gruß,
    Dirk

    Function GetGeoData(sSearch as String) as String
    If Len(sSearch) = 0 Then Exit Function
    URL = „http://maps.google.com/maps/geo?output=xml&sensor=false&key=abcdefg&q=“
    URL = URL & sSearch
    oSimpleFileAccess = createUnoService( „com.sun.star.ucb.SimpleFileAccess“ )
    On Error GoTo ErrorResponse
    oInputStream = oSimpleFileAccess.openFileRead(URL)
    oTextStream = createUnoService(„com.sun.star.io.TextInputStream“)
    oTextStream.InputStream = oInputStream
    aDelimiters = Array(ASC(„>“),ASC(„<"))
    sLastString = ""
    Do While NOT oTextStream.isEOF
    sThisString = oTextStream.readString(aDelimiters,True)
    Select Case sLastString
    Case "coordinates":
    sCoordinates = sThisString
    Case "PostalCodeNumber":
    sPLZ = sThisString
    Case "CountryName":
    sState = sThisString
    Case "ThoroughfareName":
    sAddress = sThisString
    Case "DependentLocalityName":
    sCity = sThisString
    End Select
    sLastString = sThisString
    Loop
    sLongitude = Left(sCoordinates,7)
    sLatitude = Mid(sCoordinates,InStr(sCoordinates,",")+1,8)
    if sAddress = "" then
    GetGeoData = "ACHTUNG!!!ACHTUNG!!! " & sPLZ & " " & sCity & Chr(13) & _
    "Straße: " & sAddress & Chr(13) & " Koordinaten: " & _
    sCoordinates & Chr(13) & " Längengrad: " & sLongitude & " Breitengrad: " &sLatitude
    else
    GetGeoData = sPLZ & " " & sCity & Chr(13) & _
    "Straße: " & sAddress & Chr(13) & " Koordinaten: " & _
    sCoordinates & Chr(13) & " Längengrad: " & sLongitude & " Breitengrad: " &sLatitude
    end if
    oInputStream.closeInput()
    Exit Function
    ErrorResponse:
    GetGeoData = "Kein Ergebnis gefunden!!!"
    End Function

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.