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 geoCoderSince 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. WalkthroughThis 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 API Now 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. CodeAs 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. Why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available. You can download this example and all other code in the cDataSet.xlsm from Downloads |
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > excel to json and back > Rest to Excel library >