Slovenian Postal Codes v. 2

In my previous blog post I gave a list of Slovenian postal codes based on a revision of an online source. Below I give you a Google based object oriented VBA solution, which easily generalizes to parsing of other xml sources. Geocoding can have an enormous impact on presentations and requires a careful processing. I use an automated VBA routine inspired by the code below, but still find that inspection is necessary. Often there are several loations of observations, businesses or respondents in a survey, that have to pinpointed using auxiliary information, which depends on the problem at hand. Have a look at www.hellmund-laier.dk

VBA module


Option Explicit

Public xmlDoc As
MSXML2.DOMDocument60

Public Function lat(searchfield As String)
Dim googleUrl As String
googleUrl = "http://maps.googleapis.com/maps/api/geocode/xml?address=" & searchfield & "&sensor=false"
Set xmlDoc = New MSXML2.DOMDocument60
xmlDoc.async = False
xmlDoc.Load (googleUrl)
lat = xmlDoc.SelectSingleNode("GeocodeResponse/result/geometry/location/lat").Text
End Function

Public Function lng(searchfield As String)
lng = xmlDoc.SelectSingleNode("GeocodeResponse/result/geometry/location/lng").Text
End Function



Example


lng function call must follow a lat function call which instantiates the MSXML2.DOMDocument object.

In Excel with contents 1000,+Slovenia in cell A1, we place coordinate values in two separate cells using a single Google Map query  =lat(A1) superseeded by =lng(A1). The queries to the XML object return the values 46.0363798 and 14.4896074.
To get things working you have to check Microsoft XML, v6.0 in the References... menu item found in the Tools menu of the VBA editor.

Supplementary note


I needed to convert a unicode input string to UTF-8 encoding. Below is the complete VBA module source including a converter suggested by Mycopka.

The UTF-8 encoded input string Forbindelsesvejen 116,+9400,+Nørresundby,+Denmark  returns values 57.0706070 and 9.9386770.



Source


Option Explicit



Public Declare Function WideCharToMultiByte Lib "kernel32" (ByVal CodePage As Long, ByVal dwFlags As Long, ByVal lpWideCharStr As Long, ByVal cchWideChar As Long, ByRef lpMultiByteStr As Any, ByVal cchMultiByte As Long, ByVal lpDefaultChar As String, ByVal lpUsedDefaultChar As Long) As Long

Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (lpvDest As Any, lpvSource As Any, ByVal cbCopy As Long)

Private Const m_bIsNt As Boolean = True

Public Const CP_UTF8 = 65001

Public xmlDoc As MSXML2.DOMDocument60



'Purpose:Convert Unicode string to UTF-8.
Public Function UTF8_Encode(ByVal strUnicode As String, Optional ByVal bHTML As Boolean = True) As String
   Dim i                As Long
   Dim TLen             As Long
   Dim lPtr             As Long
   Dim UTF16            As Long
   Dim UTF8_EncodeLong  As String
   TLen = Len(strUnicode)
   If TLen = 0 Then Exit Function
   If m_bIsNt Then
      Dim lngBufferSize    As Long
      Dim lngResult        As Long
      Dim bytUtf8()        As Byte
      'Set buffer for longest possible string.
      lngBufferSize = TLen * 3 + 1
      ReDim bytUtf8(lngBufferSize - 1)
      'Translate using code page 65001(UTF-8).
      lngResult = WideCharToMultiByte(CP_UTF8, 0, StrPtr(strUnicode), _
         TLen, bytUtf8(0), lngBufferSize, vbNullString, 0)
      'Trim result to actual length.
      If lngResult Then
         lngResult = lngResult - 1
         ReDim Preserve bytUtf8(lngResult)
         'CopyMemory StrPtr(UTF8_Encode), bytUtf8(0&), lngResult
         UTF8_Encode = StrConv(bytUtf8, vbUnicode)
         ' For i = 0 To lngResult
         '    UTF8_Encode = UTF8_Encode & Chr$(bytUtf8(i))
         ' Next
      End If
   Else
      For i = 1 To TLen
         ' Get UTF-16 value of Unicode character
         lPtr = StrPtr(strUnicode) + ((i - 1) * 2)
         CopyMemory UTF16, ByVal lPtr, 2
         'Convert to UTF-8
         If UTF16 < &H80 Then                                      ' 1 UTF-8 byte
            UTF8_EncodeLong = Chr$(UTF16)
         ElseIf UTF16 < &H800 Then                                 ' 2 UTF-8 bytes
            UTF8_EncodeLong = Chr$(&H80 + (UTF16 And &H3F))              ' Least Significant 6 bits
            UTF16 = UTF16 \ &H40                                   ' Shift right 6 bits
            UTF8_EncodeLong = Chr$(&HC0 + (UTF16 And &H1F)) & UTF8_EncodeLong  ' Use 5 remaining bits
         Else                                                      ' 3 UTF-8 bytes
            UTF8_EncodeLong = Chr$(&H80 + (UTF16 And &H3F))              ' Least Significant 6 bits
            UTF16 = UTF16 \ &H40                                   ' Shift right 6 bits
            UTF8_EncodeLong = Chr$(&H80 + (UTF16 And &H3F)) & UTF8_EncodeLong  ' Use next 6 bits
            UTF16 = UTF16 \ &H40                                   ' Shift right 6 bits
            UTF8_EncodeLong = Chr$(&HE0 + (UTF16 And &HF)) & UTF8_EncodeLong   ' Use 4 remaining bits
         End If
         UTF8_Encode = UTF8_Encode & UTF8_EncodeLong
      Next
   End If
   'Substitute vbCrLf with HTML line breaks if requested.
   If bHTML Then
      UTF8_Encode = Replace$(UTF8_Encode, vbCrLf, "")
   End If
End Function

Public Function lat(searchfield As String)
Dim googleUrl As String
googleUrl = "http://maps.googleapis.com/maps/api/geocode/xml?address=" & UTF8_Encode(searchfield) & "&sensor=false"
Set xmlDoc = New MSXML2.DOMDocument60
xmlDoc.async = False
xmlDoc.Load (googleUrl)
lat = xmlDoc.SelectSingleNode("GeocodeResponse/result/geometry/location/lat").Text
End Function

Public Function lng(searchfield As String)
lng = xmlDoc.SelectSingleNode("GeocodeResponse/result/geometry/location/lng").Text
End Function

Comments

Popular Posts