Category: mapping

It’s the end of the line for Google Fusion Tables. The search giant announced that it will shut down its experimental data visualization tool. Since it first came out of Google Labs in 2009, people have used the service, which was a combination of spreadsheet and database, to store data, then display maps and charts that analyze the data.

The maps became an especially popular part of Google Fusion Tables, because geocoding was built-in. With a list of addresses in Fusion Tables, Google would automatically create geographic coordinates and plot them on a map. While you can get much of the charting from Google Spreadsheets, Excel, and other tabular tools, the maps are harder to replace. To make a map from your Fusion Tables data, you either learning to program the Google Maps API or look for a tool outside of Google.

We’ll show how you can replace your Google Fusion Tables maps with BatchGeo. It’s an easy import/export process to get even more powerful maps than Fusion Tables provided.

Export a Fusion Table to CSV

The first thing you need to do is find your Fusion Tables and decide on which one you want to turn into a map. We’ll need to export your Fusion Table to CSV–that’s a comma-separated value file, a format easy to open in spreadsheets and works nicely with lots of tools.

1. Make sure you’re logged into your Google account, then list your Fusion Tables in Google Drive by searching for type:table.

   

2. Double-click the Fusion Table you want to export to open it.

   

3. Click the File menu, then select Download.

   

4. Confirm the details and click Download again

   

The file should now download to your computer (or you should be prompted to choose a location). Once you find the CSV file on your computer, you’ve successfully exported your Google Fusion Table.

Now you can repeat the process with other tables you want to download. Before Google Fusion Tables is completely shut down, Google will create a way to download everything at once. In the meantime, we need to go one at a time.

Choose your favorite table and head to the next section to see how quickly you can turn it into a powerful map with BatchGeo.

Import a CSV Into a Map

With your Fusion Table downloaded, you now have a CSV sitting on your hard drive, waiting to be turned into a map. If you want to look at the data or edit it, you can open the CSV file in Excel, Numbers, or any other spreadsheet program (including Google Spreadsheets!). You can also view it in an ordinary text file, though you’ll need to be careful, as the formatting is important to help maintain the columns and rows.

If you open up your CSV file in a spreadsheet program, you’ll see the headers and data from your Fusion Table:

Now simply follow these steps:

1. Highlight and copy the entire spreadsheet, including the header row.
2. Go to the BatchGeo homepage
3. Click in the big box and paste your data.

Now you’ll see your data within the big box on the BatchGeo home page. Click the “Map Now” button and see your data transformed into a map in seconds.

Alternatively, you can skip the step of opening the data in your spreadsheet. Simply drag the CSV file into the big box on the BatchGeo home page. We’ll do the importing for you!

Either way, you now have a map easily made from your Google Fusion Tables data. BatchGeo performs automatic geocoding of your addresses so they can be placed on a map.

Do More With Your Map

In addition to creating simple maps, BatchGeo also displays your non-geographic data automatically. You can click on markers to see details, or use some other features to get more information from your map.

View Google Fusion Tables Exported Map in a full screen map

If you have other columns within your data, such as categories or types, you’ll be able to use the map filtering and grouping technology within BatchGeo. In our example map, the color of the markers are determined by the different values in the “Type” column. You can click a label in the bottom of the map to see only the markers you want.

Another way to display additional data within the map itself is to enable map marker clustering, which will display high density markers with circles sized accordingly. In addition, you can pick a numeric column from your dataset and display the average or sum from that column for the area covered.

Now that you’ve transformed your Google Fusion Tables, there are countless other ways to bring your data to life, including BatchGeo Advanced Mode, available with Pro accounts.

There are thousands of APIs, tools used by developers to connect data from one service to another. Many APIs have data that could be useful in everyday situations, and you don’t necessarily have to be a programmer to connect to the data. However, it can be helpful to have it in a format that is familiar and easy to use.

The trusty spreadsheet—whether it’s Excel, Google Sheets, Numbers, or any tool that can accept comma separated values (CSV) files—is the data tool of the non-programmer. We’ve grown accustomed to viewing our data as columns and rows. Most APIs produce a different data format, called JSON (or XML). It’s full of curly braces and doesn’t look very user-friendly. However, you can convert JSON to CSV or Excel to get the data in a more familiar format.

Find Your Data Source

Before you can convert your JSON, you need to have the data. Perhaps you already know where you’ll get your data, but if you’ve never used an API before, that can be intimidating. Once you know what to look for, though, accessing many APIs is as intuitive as loading a webpage.

First, to discover the API, there are a couple of approaches that work well once you know who has the data you want:

• Look in the footer or header of the website for the words “API,” “developer,” “partners,” or “integrations”
• Google it by searching for “website name API”
• Look in a directory like ProgrammableWeb.

Next, you’ll need to navigate the API documentation. The best APIs will have a “getting started” guide or similar tutorial for first-timers. It can still be intimidating, but you’re looking for descriptions of the type of data you want. For example, if you use a CRM and you’re wanting to get your contacts via API, look for sections of the documentation that reference “getting contacts” or “listing contacts.”

For our Halloween celebration map of events and our holiday light displays map, we went to Eventful and found “Developer API” in its footer. Within its API documentation, we saw an endpoint (which is like a web address URL) for /events/search. Using their examples, we could put together the API call right in the web browser: http://api.eventful.com/json/events/search?app_key=SECRETKEY&keywords=halloween&page_size=250&date=2018103000-2018110100

When you replace “SECRETKEY” with your API key, that returns a bunch of text. It’s in JSON format, but if you don’t know what to look for, it might not seem very useful.

Understand the JSON Results

Much of the time, JSON is returned as a giant wall of text, which is one reason it looks incredibly confusing to non-programmers (and many programmers can’t make much sense of it, either). One quick trick to make JSON more readable is to either “pretty print” it or get an extension such as JSONView for Chrome that automatically displays JSON with colors and indentation to make it easier to understand the data within.

Take the example above. It still may be daunting, but hopefully, some of it looks familiar. For example, can you tell the total number of results? Almost 3,000—as referenced by the total_items.

Most JSON data is stored as key/value pairs. That is, there is a way to reference a value (total_items in our example) and the value itself (2869). All related key/value pairs are stored within a single object, as denoted by the curly brackets { and }. Each pair is separated by a comma. There can be objects within objects by using more curly brackets as the value.

In addition, there’s one more important type of value, highly relevant to converting from JSON to CSV: an array or list. This is many values in a row, often many objects in a row. A list occurs between two sets of square brackets, [ and ].

Our example shows a primary object defined by the { on the first line, followed by a few key/value pairs, then e key called “events” whose value is an object. Within that object, there is a single key, “event” which includes a list value (the [ shows us this is a list) and the values within the list are yet more objects. There’s a lot to unpack there and understand, and it might be helpful to see the full JSON file displayed using JSONView.

Even if it’s confusing, the JSON will always have a structure you can figure out using these basic building blocks: objects with key/value pairs, where values can be additional objects or lists of values.

Convert JSON to Excel or CSV

Once you understand the type of data that can be converted, it’s time to make your JSON data usable in Excel. That means you need to convert the JSON either directly to an Excel document, or more likely to a text document that Excel can read, such as CSV.

Let’s look back at our Eventful data again. We need to extract the list of events, which are a series of objects. The key/value pairs in all of the event objects will all contain the same keys. The values will obviously be different since they describe individual events.

Therefore, in spreadsheet terms: the keys become the header row.

You can copy all the key/values, or just the ones you want. For example, we can see the “latitude” key in the screenshot from the previous section. That is a useful value for making a map!

If there’s only a small amount of data, you could make quick progress by copying and then pasting your data from JSON to a spreadsheet. However, most of the time there’s a lot of data. In our Eventful example, there were almost 3,000 events! Use a tool like this to convert file formats automatically.

Optional: Create a Map with Your Data

Many APIs and data sources include location data, such as addresses, city names, or latitude/longitude coordinates. Once your data is in spreadsheet format (as a CSV, Excel, Google Sheets, and more) you can easily create a map like this:

View 2018 Tour de France Route in a full screen map

Create a latitude and longitude map by copy-pasting your spreadsheet data in the cases where you have geographic coordinates. Otherwise, you can automatically geocode location names using our Google Map creator.

December brings Hanukkah on the 25th day of Kislev, Christmas on the 25th, Kwanzaa on the 26th, and many more holidays throughout the month. However, there is one holiday tradition everyone can enjoy all month long: dazzling holiday light displays! Whether you prefer to take in your neighborhood’s holiday lights while walking hand in hand with your kids, or it’s more your style to drive down well-lit streets listening to your favorite holiday hits as your breath fogs up the windows, you can easily find the neighborhood nearest you by browsing our map of holiday light displays around the snow globe.

In addition to finding the neighborhoods decking their halls and homes this month, discover which countries other than the U.S. light up in December, the most outrageous neighborhoods of 2017, and which states spend the most money on staying lit this time of year.

View Holiday Light Displays in a full screen map

Browse the map to see pretty pics of holiday light displays and then read on for the places that are celebrating the most during this wonderful time of year.

Eventful of Holiday Cheer

Much like our piece on Halloween events, which showed where in the world the spooky holiday was celebrated, this holiday light displays map was made in part due to Eventful — the online calendar and events discovery service. From December 1st all the way through the 31st, Eventful notes holiday light displays occurring worldwide. We made an API call to round up all the data and then converted the results into a familiar format: the spreadsheet. This step-by-step guide walks you through exactly how to convert API results and then make a map.

Our map shows that the states with the most holiday cheer are California, Texas, and New York, which isn’t too surprising as these three states have some of the highest populations in the U.S. At the time we checked Eventful, Californians had already planned 65 holiday light-themed events, Texas had 44 spectacularly lit options, and the Empire State had 38 holiday light display events. Unfortunately for residents of North Dakota, Vermont, and Wyoming, at the time we checked, there weren’t any holiday light events posted. These three states are pretty low population-wise, but even smaller populated states deserve to celebrate. That’s why we also added 2017’s most outrageously lit neighborhoods, so on our map, each of those states have one event going on.

Other Countries That Are Lighting It Up

Many countries other than the U.S. are home to upcoming holiday light displays. While not on this map, countries like the United Kingdom are playing host to 202 light-related activities throughout December. Canada is also bringing their holiday spirit with 65 events, and Australia and New Zealand and holding 36 and 35 light-viewing parties, respectively. Ireland will also be well-lit this season with 23 events occurring, and Malaysia and the Philippines will each have five holiday light events. Germany is also planning on hosting two events, while Finland, France, Hungary, Isle of Man, Singapore, and Spain all have one event taking place this December.

While taking note of which U.S. states and other countries are bringing out the big bulbs is cool, our map contains more than Eventful’s holiday light display results around the world. We also mapped the United States’ most outrageously lit neighborhoods of 2017 and the cost of electricity to keep these neighborhoods in the holiday spirit.

2017’s Best & Brightest Lights

Curious about the absolute best holiday light displays from 2017? Use our map grouping feature to sort the map by “Event.” Then select “2017 Best Neighborhood By State” to see the neighborhoods 24/7 Wall St. identified as the most outrageously lit. Now, we can’t be sure that these winning neighborhoods will continue to deck their halls and homes in the years to come. However, if you happen to live in the vicinity of one of these neighborhoods, you could always stop on by to see if they are continuing with the enjoyable spectacle that is their holiday lights. Keep on reading to discover how to check if one of 2017’s best and brightest neighborhoods is located near you.

How Yule Find The Closest Lights

It’s simple to find out how far — or close! — one of the winning neighborhoods or other 621 holiday light events is to your location. First, filter the map by the type of “Event” you want to check out. If you’re not picky, you can just skip this step. Then type your address into the search bar of the map, and we’ll show you which event is closest to you!

You can even measure the distance in miles or kilometers between where you reside and the closest holiday light display to you. Click on the measuring tape in the top left corner of the map — part of Advanced Mode available with BatchGeo Pro, — and select the option that looks like a mini ruler. Then, drag a line from your location to the nearest event marker to see just how close it is! To toggle back and forth between miles and kilometers, click on the line at the bottom right-hand side of the map.

Oh Electricity, Oh Electricity…

24/7 Wall St. also included each state’s average monthly residential electric bill in their outrageous light displays research. The following ten states or districts have the highest average monthly electricity bills:

1. South Carolina — $146.09
2. Alabama — $145.55
3. Connecticut — $142.19
4. Maryland — $141.53
5. Hawaii — $138.73
6. Washington D.C. — $131.90
7. Georgia — $130.87
8. Tennessee — $128.89
9. Virginia — $127.14
10. Texas — $127.10

The average cost of lighting up a home for the holidays each month is $23.33 for incandescent lights and $2.67 for LED lights. So for those holiday light fans in these top electricity-consuming states, using LED lights may help to keep electricity costs down during this time of year.

---

Now is the time to grab some hot cocoa and wander around your city armed with a map of holiday light displays. You can even make your own map like this one using Eventful and BatchGeo. New Year’s Eve parties, anyone?