Where Are User Uploaded Fusion Tables Stored
In our previous how to, nosotros explained how to capture live data from the web using Import.io. In this how to we wanted to show you what nosotros can really do with that data.
Nosotros decided to make an interactive map with different coloured pins. Using Care Quality Commission data on hospital operation in England, we create a map of which hospitals the staff who work there actually recommend attending.
Footstep 1: Consign information from Import.io
At that place are a few ways to link structured data in Import.io to other programmes, especially keeping it a alive link. But, to go along things just, we're going to download the data instead.
To do so, in the upper right-paw corner, select download, and so click CSV. Alternatively, if you'd prefer to use information technology in Excel you lot can download it equally an XLS file direct.
Step 2: Import information into Google Bulldoze Spreadsheets
In Google Bulldoze select the upload push from the pinnacle left of the screen, discover the saved data and upload it.
We don't desire it as a CSV file though, nosotros desire to apply information technology as a live Google Spreadsheet. So at the top of the page, select 'Open'. This will automatically create a new Spreadsheet.
If you haven't followed along the previous how to and would like to copy the data into your ain Google Spreadsheet, you tin access the data here.
Step 3: Add a column that indicates the right pin/marker
Google Fusion Tables let a number of dissimilar pins on their maps. We want iv different ones in our map -- a red 1 for nether-performing hospitals, a yellow or amber one for ok hospitals, a green one for hospitals that are performing well, and grey 1 for hospitals where we don't have the data.
That means we need to introduce a column that labels the pins correctly. We can base of operations it off of the image names provided in cavalcade x 'rec_by_staff_image/_source'. You tin run across that there are 3 images used: ane has an 'ok', another has a 'tick' and the final ane has an 'exclamation'. We're going to create a formula that assembly the right pivot with each of those. Finer the correct associations are:
'OK' with 'small_yellow'
'tick' with 'small_green'
'exclamation' with 'small_red'
[bare] with 'measle_grey'
As it'south empty, I've entered the following formula in cell Z2:
= IF ( ISNUMBER ( Detect ( "ok" , X2 ) ) , "small_yellow" , IF ( ISNUMBER ( Discover ( "tick" , X2 ) ) , "small_green" , IF ( ISNUMBER ( FIND ( "exclamation" , X2 ) ) , "small_red" , "measle_grey" ) ) )
What this is effectively proverb is along the lines of 'check this jail cell to see if it contains the term "ok", if and so in this prison cell write "small_yellow", if not bank check to come across if information technology contains the give-and-take "tick", etc'.
To fill down the entire column, but double click the bottom-correct corner of the cell.
Stride 4: Create a new Fusion Table
If yous oasis't already installed Fusion Tables in your Google Drive, you may demand to do that starting time. Simply go the to Google Fusion Tables homepage while logged into your Google account and select 'Get the Drive App'.
Back in Google Bulldoze, instead of selecting the upload button, select 'create' and then Fusion Table.
Fusion Tables volition then ask where to import information from. As we've been using Google Drive we'll select Google Spreadsheet and and then select the correct dataset.
Fusion Tables automatically detects that we have column names in our first row, so just select next on the following folio.
Finally, earlier creating the table it will inquire you to requite the file an appropriate proper noun, check whether to let download of the data, and to credit the initial information source, in this case the NHS. Select 'Finish' and the new Fusion Table volition appear.
Step 5: Geocode data based on postcode
Two of the columns are already highlighted in yellowish past Fusion Tables: hospital proper name and hospital post code. That is because these are the columns Google can use to geo-reference the information.
The easiest style to exercise this is to flip to the map tab that has automatically been generated. It volition starting time geocoding information based on hospital name. But the postcode in England gives an exact spot, so it's much better to interruption this get-go try at geocoding past proper name (which might get a number of cryptic places) and then select hospital_postcode in the 'Location' drib-down carte on the left.
It may take some time to georeference all 502 hospitals, but when it's done a map of England should announced with a number of red markers where each of the hospitals is.
Step 6: Modify the colour of the pins to indicate whether staff recommend individual hospitals
In that location are two potential means to change the color of the pins. The get-go is by creating 'buckets' based on the percent of staff indicating that they recommend the hospital. Permit's try that first.
Select the 'Change feature manner' button on the left side of the screen. Then under 'marker icon' select buckets.
We'll create three buckets based on the column 'rec_by_staff'. If less that l% of the staff would recommend a hospital, nosotros'll requite information technology a reddish marker. If information technology'southward betwixt 50% - 75% of staff that recommend a hospital, we'll give it a yellow marker, and if it's to a higher place 75% we'll requite information technology a green marker. Select save and the map with different colours should now appear.
Notwithstanding, the methodology used by the NHS doesn't quite requite united states of america then defined pause points (probably because of sample sizes at various hospitals). Also, this erroneously takes hospitals where we have no data and lumps them in with nether-performing hospitals.
Then it's meliorate to utilise the images that they've used equally a guide on how well a infirmary is doing.
Since we've already created a column that gives the correct icon name based on those images, nosotros'll change the fashion again but instead of buckets, we'll apply 'Column' and select the radio button 'Use icon specified in a column' and select the column 'rec_by_staff_image/_alt' from the dropdown carte du jour. It results in a slightly different map, equally below.
tep 7: Edit the information box
The map is a nice way to get an overview of how the various hospitals are performing. But if we want more information on how individual hospitals are doing, nosotros can click on the push button and an information window appears. Unfortunately, at the moment information technology'south a bunch of gobbledygook (technical term). And so let's clean it up!
Select the 'Change info window' button on the left of the screen. Showtime by selecting some of the relevant variables to brandish. In this case we desire: Hospital website (which contains its name), the hospital address, the hospital phone number, the percentage of staff that recommend the hospital, and the paradigm of how well information technology'south doing.
That looks alright, but it'south still a bit clunky. Allow'southward flip over to the custom tab and customise the look a bit.
I've ended upward with the following lawmaking, which you can cut and paste straight.
<div class='googft-info-window' style='height: 15em; overflow-y: auto'> <h3>{hospital_website}</h3> <div style="width:60px; bladder:left; margin:5px;"><img src='{rec_by_staff_image}' height='threescore' mode='vertical-align: heart'/></div><div style="float:right"><b>Hospital address:</b> {hospital_address}<br> <b>Hospital telephone:</b> {hospital_telephone}<br> <b>Percentage of staff who recommend hospital:</b> {rec_by_staff}%<br></div> </div> Step 8: Publish the map!
One of the not bad things about working with Google Fusion Tables is that you can publish directly to the web.
First you'll demand to edit the privacy of the entire workbook. Go to File > Share and a new window should pop upward. Under the privacy settings, select the 'Alter' link and select the radio push button: 'Anyone with link'. Save changes.
In the map menu, now select 'Publish'.
The first thing to practice is to specify a right size. It is so possible to share the link, or to embed the visualisation in an iFrame (as below) or using javascript!
2 Comments
Source: http://www.ttdatavis.onthinktanks.org/how-tos/how-to-map-data-using-google-fusion-tables
0 Response to "Where Are User Uploaded Fusion Tables Stored"
Post a Comment