Multiple (Bulk) Address upload for Street2Coordinates

662 views
Skip to first unread message

Austin

unread,
Dec 7, 2011, 11:55:34 AM12/7/11
to dstk-users
Hello, is it possible to upload a text file or csv of multiple
addresses through the front page of the server to the
Street2Coordinates API? So far I have only been able to load one at a
time through the file importer (Though I can send line separated
addresses through the input box).

Thanks for your time,
Austin

Pete Warden

unread,
Dec 7, 2011, 5:01:20 PM12/7/11
to dstk-...@googlegroups.com
Hi Austin,
               there isn't a way to do that through the website, but most of the command-line versions of the interface work with files (barring bugs of course):

Does that help?

cheers,
           Pete

Luke Peterson

unread,
Dec 7, 2011, 5:43:38 PM12/7/11
to dstk-...@googlegroups.com
I've got an excel (VBA) macro that bumps an address or list of addresses up against the dstk API and extracts the lat/longs ... happy to share if this would be helpful. It's very simple.

-----
Luke Peterson

Pete Warden

unread,
Dec 9, 2011, 2:06:01 PM12/9/11
to dstk-...@googlegroups.com
I'd love to see that myself Luke, and publicize it, if you don't mind? Sounds like a great use.

cheers,
           Pete
--
Jetpac
Claim your travel profile at www.jetpac.com

Luke Peterson

unread,
Dec 9, 2011, 4:57:49 PM12/9/11
to dstk-...@googlegroups.com
Attribute VB_Name = "geoCode"
' Excel VBA functions retrieve a TIGER-based long/lat geocode for an address
' from the datasciencetoolkit.org address2coordinates API
' Written 5/25/2011 by Luke Peterson luke.p...@gmail.com
'
' Usage: Feed a USA street address to getGeocode() to get an API response,
' which you can pass through getLatitude() and getLongitude() in order to put
' the Long/Lat into separate columns. Best-practice is to pull the API response
' into a single field with getGeocode(), then parse that field with
' getLatitude() and getLongitude(), rather than using
' getLatitude(getGeocode([Address])) and getLongitude(getGeocode([Address])),
' since the latter will double the number of requests to the dstk server.
'
' Dependencies: Enable "Microsoft XML, v6.0" in Excel's Tools...References dialog.
'


Public Function URLEncode( _
   StringVal As String, _
   Optional SpaceAsPlus As Boolean = False _
) As String

  Dim StringLen As Long: StringLen = Len(StringVal)

  If StringLen > 0 Then
    ReDim result(StringLen) As String
    Dim i As Long, CharCode As Integer
    Dim Char As String, Space As String

    If SpaceAsPlus Then Space = "+" Else Space = "%20"

    For i = 1 To StringLen
      Char = Mid$(StringVal, i, 1)
      CharCode = Asc(Char)
      Select Case CharCode
        Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
          result(i) = Char
        Case 32
          result(i) = Space
        Case 0 To 15
          result(i) = "%0" & Hex(CharCode)
        Case Else
          result(i) = "%" & Hex(CharCode)
      End Select
    Next i
    URLEncode = Join(result, "")
  End If
End Function


Public Function getGeocode(address As String) As String
    
    Dim geocodeService As New XMLHTTP60
    
    geocodeService.Open "GET", "http://www.datasciencetoolkit.org/street2coordinates/" + URLEncode(address, True)
    geocodeService.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    geocodeService.send
    
    If geocodeService.Status = "200" Then
               
        getGeocode = geocodeService.responseText
    Else
        getGeocode = "Error"
           
    End If

End Function

Public Function getLatitude(geocode As String) As String
    If InStr(geocode, "latitude") = 0 Then
        getLatitude = "None"
    Else
        getLatitude = Mid(geocode, InStr(geocode, "latitude") + 10, InStr(Mid(geocode, InStr(geocode, "latitude") + 10), ",") - 1)
    End If
End Function

Public Function getLongitude(geocode As String) As String
    If InStr(geocode, "longitude") = 0 Then
        getLongitude = "None"
    Else
        getLongitude = Mid(geocode, InStr(geocode, "longitude") + 11, InStr(Mid(geocode, InStr(geocode, "longitude") + 11), ",") - 1)
    End If
End Function



-----
Luke Peterson
Reply all
Reply to author
Forward
0 new messages