Editing a GPS related VDA to react to address quality

[Kenny27]

Hello!

Basically, a user called "Ombir" helped me create a code which automatically lets me get the GPS co-ordinates (Latitude and Longitude) for a certain street address. So, you just type in the street address and poof - two new columns of GPS co-ordinates. Here's the code:

Sub Geocoding()Dim rng     As Range
Dim lat     As String
Dim lng     As String
Dim ndxla1  As Long
Dim ndxla2  As Long
Dim ndxlo1  As Long
Dim ndxlo2  As Long
Dim url     As String
Dim resp    As String
Dim req     As Object
Const api   As String = "Paste Your Api Key Here"


Application.ScreenUpdating = False


Set req = CreateObject("WinHttp.WinHttpRequest.5.1")
Set rng = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)


rng.Replace What:=" ", Replacement:="+"


For Each cell In rng
    url = "https://maps.googleapis.com/maps/api/geocode/json?address=" & cell.Value & "&key=" & api
    
    req.Open "GET", url, False
    req.Send: resp = req.ResponseText
     
    If InStr(resp, "ZERO_RESULTS") = 0 Then
    
        ndxla1 = InStr(resp, """" & "lat" & """") + 8
        ndxlo1 = InStr(resp, """" & "lng" & """") + 8
        
        ndxla2 = InStr(ndxla1, resp, ",") - ndxla1
        ndxlo2 = InStr(ndxlo1, resp, ",") - ndxlo1 - 1
        
        lat = Mid$(resp, ndxla1, ndxla2)
        lng = Mid$(resp, ndxlo1, ndxlo2)
        
        cell.Offset(, 1) = lat: cell.Offset(, 2) = lng
    End If
Next
rng.Replace What:="+", Replacement:=" "
Application.ScreenUpdating = True
MsgBox "Geocoding Completed"

End Sub

Now, google's GPS site gives out 4 result quality types for the address you've put in:

1. [FONT=&quot]ROOFTOP[/FONT]
2. [FONT=&quot]RANGE_INTERPOLATED[/FONT]
3. [FONT=&quot]GEOMETRIC_CENTER[/FONT]
4. [FONT=&quot]APPROXIMATE[/FONT]

All of which can be found on this batch geocoding app by ticking the "Result quality" b box: https://www.doogal.co.uk/BatchGeocoding.php

Now, I basically want all results which aren't Rooftop to simply appear as blank spaces or appear as "FAILED" or whatnot. I basically just want the "rooftop" results to appear out of all 4 result types.

In all honesty I have NO idea where to look for the result types so I was hoping someone could help me.

Thanks in advance!

Kenny27

 

[Kenny27]

P.S: I don't want to seem pushy, at all - this I really hope this is not too much to ask for... If it is then by all means, I'd be 120% happy with the request above.

If there'd be somewhat of option in the VBA to either enable or disable the 4 types of quality results (As in, the request above for example would basically be:

1. ROOFTOP - enabled.
2. RANGE_INTERPOLATED - disabled.
3. GEOMETRIC_CENTER - disabled.
4. APPROXIMATE - disabled.

I mainly want this because I might get a request to expand the search radius or in other words - expand to Range Interpolated as well in the near future, so instead of making another request or flopping in general, that would be great.

 

[Kenny27]

I've no idea why I keep writing VDA instead of VBA... My apologies.