08 Feb Use Google Fusion Tables to Display and Map Data
Recently I had a challenge: display YouTube videos, images and links in the info box of a Google Map marker. Previously this wouldn’t have been a big deal, but due to changes Google Maps has made, it isn’t as simple as it seems. As of now, you can use Google Maps Engine Light to accomplish this, but if you want to use it for commercial purposes you have to upgrade to Google Maps Engine Pro. You can see a comparison of both products here. We were also having issues with the mobile/responsive display of Google Maps Engine, the info windows created by the markers were too big on the map, making it difficult to view on mobile.
A solution to this challenge was to use Google Fusion Tables. Fusion Tables is an experimental data visualization web application to gather, visualize, and share larger data tables. One of their features is being able to visualize data on a map. I thought that this stack overflow response explained the differences nicely.
I am going to go over how to set up a Google Fusion Table for the data in this Google Spreadsheet. To summarize, it is a list of songs based on city names, the artist name, the youtube iframe, half have images, the location (to be mapped), and a link to information about that city. I would like the user to see a map on the page, click on the location and have it popup an info window containing all of that information.
Create the data table
This part is really straightforward. Create a spreadsheet that contains all the data you would like to use so that you can import it into Google Fusion Tables. I have used both Microsoft Excel and Google Docs with success, but chose a Google Spreadsheet for this demo.
Now for the fun part…
While on Google Drive, create a new Fusion Table. You will have the option of importing the data or creating a new table. Since we already have a spreadsheet with all the information we are going to Import New Table from Google Spreadsheet. I select the spreadsheet I had created and proceed. Well, that’s kind of a lie. I had initially set the spreadsheet to be shared with anyone who has the link. When I went to import it, I got an error saying this was potentially unsafe. Then it wouldn’t load. I changed the spreadsheet permissions to the public, and it worked. It was acting quirky so I am not sure if the permission setting was the reason why.
The table is created. While looking at it, you will see a rows view and a cards view that have already been created. We want a map view. To create the map view, click on the red plus sign tab and click Add Map. After clicking that you might see This table has no location columns and so cannot be mapped. Change one of the column types to location to enable mapping again. The reason for seeing this is we haven’t assigned the types of values each column has. To do this, go to the Rows view. Click on each column heading to edit its value. In this case we will start with Youtube. Click Change and select YouTube, Vimeo or Google Map, Save. You should notice that instead of showing iframe text, it now shows the actual embedded YouTube player. For Location, change Type to Location. After saving, you will notice that the location names are highlighted in yellow. This signifies that they haven’t been geocoded yet. If you click on the map tab, the geocoding will take place, you can then click back to the Rows tab and the yellow highlight is gone. For Image we change the Format to Single line image. Finally, we add Type: Link to the Link Column.
The data is now formatted correctly.
Styling and Dynamic Templating Inside the Info Box
If you click on the map tab, you will see the 10 cities plotted on the map. You can click any of the markers and see an info box containing all the information we just added, it isn’t very pretty. If you click Tools -> Change Map, you will see Change feature styles… and Change info window…. First, I would like to change the look of my map markers, so I select Change feature styles. Under Points -> Marker Icon, I select the large blue marker and Save. Next, I want to change the way the information inside the box is displayed. If I click on Change info window, I can select Automatic or Custom. I choose Custom and see this code that is pre-populated:
I made some changes, and got the layout the way I wanted it.
While this works, there are some issues that I need to take into account. For example, half of my entries don’t have an image, and if viewing in IE (ugh), there would be a lovely, giant, red x missing image icon. Also, the map is really zoomed out, ideally I would like the map to be centered around North America. I also need to make sure this works on mobile.
To be able to make these changes and do this testing, I published the map in a template by going to Tools -> Publish -> Get HTML and Javascript. By using this method you are able to utilize the Google Maps API. Now, I have a template that holds the code along with the code that takes place in the info window in the google fusion table map. Any update I do on the fusion table info window code, will automatically show up on my newly created template. Here is the template code I am starting with:
I changed:
center: new google.maps.LatLng(41.850033, -87.6500523),
zoom: 4,
Now the map is centered around the US, and the zoom level fits the map space.
To fix the missing/broken image we must switch our info window code to use dynamic templating. The benefit of this is we can now use if statements saying, if this image exists, show it, if not, don’t. To use dynamic templating, the first line of code has to be {template .contents} and the last line has to be {/template}. There isn’t any wiggle room with this, and you can’t use these tags around certain portions of the code… it’s all or nothing. The variable names must all be changed as well. {Artist} will be changed to $data.value.Artist and {YoutubeLarge} changes to $data.formatted[‘YoutubeLarge’]. TIP: I had a difficult time getting the youtube iframe to show as an embedded video once I switched to dynamic templating. I tried several variations of the $data.value/$data.formatted variables to no avail. After much googling I found that adding noautoescape to the youtube variable: {$data.formatted[‘YoutubeLarge’] |noAutoescape}. You can read more about that here. My info window code now looks like this:
If the image doesn’t exist, it won’t affect the layout like it was previously. The if statements can be added to all the variables, but in this case I am aware of what info is missing in my small table of data and only need it for the missing images.
I had mentioned mobile optimization above, and while I haven’t done anything for this example, it should be possible to manipulate the code/CSS for mobile In the process of learning how to do this, I created a fusion table with larger instances of images and youtube iframes, then cloned the table to hold smaller versions of that info. I used CSS and media queries to hide/show the different maps based on screen size. I know that isn’t the most elegant solution, but it worked and there was a deadline.
You can view the Google Spreadsheet here.
You can view the Google Fusion Table here.
The map I created for this example (using JS/HTML) can be seen here.
This is the embedded iframe version:
.jpg)
Kara Noreika
.jpg)
Latest posts by Kara Noreika (see all)
- Website Accessibility:What is it and Does it Apply to my Small to Medium Sized Business? - October 29, 2018
- Common Drop Down List Data for WordPress Contact Forms - March 18, 2016
- Prevent Email Spam on Forms - April 16, 2014