This is real interesting mashup using an new Rest to Excel library entry for the urbarama API. This is a repository of interesting architectural interests. The API is quite interesting in that you can you supply a max an min longitude and latitude, which creates a box within which any items in the urbarama database are selected.
Mashup with geoCoder
Since max/min lat/lon don’t exactly tip off the tongue, I figured I’d mash it up with another rest entry – the yahoo geoCoder and create the API call from a dialogue. I decided to use yahoo since Google really wants you to have an API key nowadays. First though, I needed some trig to work this out. See Caculate lat/lon from distance for how all that works if you are interested or need the code. There are both Google Apps Script and VBA versions.
Walkthrough
This is the complete code – VBA version. Turns out to be pretty straightforward
Public Sub testUrbaramaMashup()
' first get an address of some sort
Dim s As String, cr As cRest, lat As Double, lon As Double, d As Double
Dim minLat As Double, minLon As Double, maxLat As Double, maxLon As Double
s = InputBox("Provide an address to center on")
If (s <> vbNullString) Then
' geocode it
Set cr = restQuery(, "yahoo geocode", s, , , , , False, , False)
If Not cr Is Nothing Then
With cr.jObject
lat = CDbl(.find("latitude").value)
lon = CDbl(.find("longitude").value)
End With
d = InputBox("Within how many kilometers of " & s & "(" & lat & "," & lon & ")")
'find within a box of this dimensions around center and do urbarama rest query
If d > 0 Then
Set cr = generalQuery("urbarama", "urbarama", _
"&miny=" & CStr(getLatFromDistance(lat, d, -135)) & _
"&minx=" & CStr(getLonFromDistance(lat, lon, d, -135)) & _
"&maxy=" & CStr(getLatFromDistance(lat, d, 45)) & _
"&maxx=" & CStr(getLonFromDistance(lat, lon, d, 45)), False)
End If
End If
End If
End Sub
First off we grab some place to be the origin of the query, and pass it off as a rest query to our yahoo geocoding rest entry. This can be any geocodable piece of text.
We then end up with a cJobject with the Yahoo response, so we pick up the geoCoded latitude and longitude that will serve as our origin, then get the maximum distance away to consider to search for items of architectural interest from the urbarama database.
We then use this to create a ‘box of interest’ around the origin, like this
The distance given is used as the no. of kilometers from the origin to the corner of a ‘box of interest’. We can use -135 degrees and +45 degrees as the heading angle to back into the calculation of minimum and maximum longitudes/latitudes required by the urbarama APINow we can use the newly added urbarama api entry, which looks like this …
With .add("urbarama")
.add "restType", erRestType.erSingleQuery
.add "url", "http://www.urbarama.com/api/project?sort=popular&offset=0&count=100&size=small&format=json"
.add "results", "projects"
.add "treeSearch", True
.add "ignore", vbNullString
End With
passing the dimensions of our box …
d = InputBox("Within how many kilometers of " & s & "(" & lat & "," & lon & ")")
'find within a box of this dimensions around center and do urbarama rest query
If d > 0 Then
Set cr = generalQuery("urbarama", "urbarama", _
"&miny=" & CStr(getLatFromDistance(lat, d, -135)) & _
"&minx=" & CStr(getLonFromDistance(lat, lon, d, -135)) & _
"&maxy=" & CStr(getLatFromDistance(lat, d, 45)) & _
"&maxx=" & CStr(getLonFromDistance(lat, lon, d, 45)), False)
End If
and we get all items of interest within the given box, a sample of which is below
Next step, we’ll integrate that with Mapping and adding markers to maps and plot them , using their thumbnails as markers and the their details as infobox details. But that’s for another day.
Code
As an aside, here’s an interesting way to combine these multiple APIS into one. You will find this code in the google apps script mcpher library or in the downloadable cDataSet.xlsm (usefulStuff module), at this gist, and below. The google Apps Script example spreadsheet is here.
Apps script version
//google apps script version
// this is going to be a mashup of geocoding, and architectural info close by
function testUrbaramaMashup() {
// first get an address of some sort
var s = mcpher.InputBox("Provide an address to center on");
if (s) {
// geocode it
var cr = mcpher.restQuery(undefined, "yahoo geocode", s, undefined,
undefined ,undefined , undefined, false, undefined, false) ;
if(cr) {
var lat = mcpher.CDbl(cr.jObject().find("latitude").value()) ;
var lon = mcpher.CDbl(cr.jObject().find("longitude").value()) ;
var d = mcpher.CDbl
(mcpher.InputBox("Within how many kilometers of " + s + "(" + lat + "," + lon + ")"));
// find within a box of this dimensions around center and do urbarama rest query
if (d > 0) {
cr = mcpher.generalQuery("urbarama", "urbarama",
"&miny=" + mcpher.CStr(mcpher.getLatFromDistance(lat, d, -135)) +
"&minx=" + mcpher.CStr(mcpher.getLonFromDistance(lat, lon, d, -135)) +
"&maxy=" + mcpher.CStr(mcpher.getLatFromDistance(lat, d, 45)) +
"&maxx=" + mcpher.CStr(mcpher.getLonFromDistance(lat, lon, d, 45)), false);
}
}
}
}
VBA version
'vba version
Public Sub testUrbaramaMashup()
' first get an address of some sort
Dim s As String, cr As cRest, lat As Double, lon As Double, d As Double
Dim minLat As Double, minLon As Double, maxLat As Double, maxLon As Double
s = InputBox("Provide an address to center on")
If (s <> vbNullString) Then
' geocode it
Set cr = restQuery(, "yahoo geocode", s, , , , , False, , False)
If Not cr Is Nothing Then
With cr.jObject
lat = CDbl(.find("latitude").value)
lon = CDbl(.find("longitude").value)
End With
d = InputBox("Within how many kilometers of " & s & "(" & lat & "," & lon & ")")
'find within a box of this dimensions around center and do urbarama rest query
If d > 0 Then
Set cr = generalQuery("urbarama", "urbarama", _
"&miny=" & CStr(getLatFromDistance(lat, d, -135)) & _
"&minx=" & CStr(getLonFromDistance(lat, lon, d, -135)) & _
"&maxy=" & CStr(getLatFromDistance(lat, d, 45)) & _
"&maxx=" & CStr(getLonFromDistance(lat, lon, d, 45)), False)
End If
End If
End If
End Sub
You can download this example and all other code in the cDataSet.xlsm from Downloads.
Continue reading about Rest to Excel Library here