### Urbarama mashup

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 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.

### <!-- google_ad_client="pub-0073976545556837"; google_ad_host="pub-6693688277674466"; google_ad_width=468; google_ad_height=60; google_ad_format="468x60_as"; google_ad_type="text_image"; google_color_border="FFFFFF"; google_color_bg="FFFFFF"; google_color_link="000000"; google_color_url="336699"; google_color_text="333333"; //--> <!-- google_ad_client="pub-0073976545556837"; google_ad_host="pub-6693688277674466"; google_ad_width=468; google_ad_height=60; google_ad_format="468x60_as"; google_ad_type="text_image"; google_color_border="FFFFFF"; google_color_bg="FFFFFF"; google_color_link="000000"; google_color_url="336699"; google_color_text="333333"; //-->

#### Derquinse Gist.GitHub Gadget

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

Comments