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:
Now, google's GPS site gives out 4 result quality types for the address you've put in:
1. ROOFTOP
2. RANGE_INTERPOLATED
3. GEOMETRIC_CENTER
4. APPROXIMATE
All of which can be found on this batch geocoding app by ticking the "Result quality" 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.
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.
Thanks in advance!
Kenny27
Code:
[/COLOR][COLOR=#333333]Sub Geocoding()Dim rng As Range[/COLOR]
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"
[COLOR=#333333]End Sub[/COLOR][COLOR=#333333]Now, google's GPS site gives out 4 result quality types for the address you've put in:
1. ROOFTOP
2. RANGE_INTERPOLATED
3. GEOMETRIC_CENTER
4. APPROXIMATE
All of which can be found on this batch geocoding app by ticking the "Result quality" 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.
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.
Thanks in advance!
Kenny27
