Comment

Convert Google Fusion Tables to Web Map with KML

Intro

In my previous post, I discussed using the CSV from your Google Fusion Table to build a web map with QGIS.

Here, we will utilize the KML file and it is significantly easier! Again though, if you’re using place names or addresses and relying on Google’s geolocation services, getting switched over will require additional data and manipulation. If you want help with that, hire me! Get in touch via Twitter (@gisn8) or on the Contact form.

I’ve got a zoning map I’ve embedded that needs to get off Fusion Tables and live somewhere else.

The map is coming straight from Fusion Tables and the panel on the right is hard coded to the page, so as long as my new map follows the same color codes, I’ll be just fine to swap them out. And what’s even cooler about downloading the KML is that the popup code comes right along with it! You don’t have to do the heavy lifting on that!

Go to your Fusion Table and go to File>Download and download the KML file (not the one with network link because that will break when Fusion Tables go away).

To bring it into QGIS, access the Data Source Manager from the Layer menu and use the Vector options. Navigate to your kml file and click Add.

I will also bring in a Google base map and modify the projection to EPSG: 3857 like I did in the previous tutorial.

From here on, most of the steps from the previous tutorial can be adopted, but I did find one very significant option. If the data does NOT need to be updated, by right-clicking and opening the Attribute Table it shows a column called description that carries over the pop-up data, so I can easily utilize that for my new map. We would hide all other columns and let the description column do it’s thing. Otherwise, I’d need to build the virtual layer as before.

For my use, I need to color these by use. While I could use let QGIS use the HEX column for the color, it won’t translate to QGIS2Web, so that will need to be manually done. To do this, right-click on the layer, open properties and go to Symbology. From the top options, choose Categorized.

With Categorized, double-clicking the swatch lets you select the color you want to use. Double-click on the Legend text to modify how you want it to be listed in a legend if you like; renaming the legend items won’t improve the web map, but is a handy reference for your own use in QGIS. By expanding Layer Rendering, I will adjust the Opacity of the layer as well.

From here, you can use the QGIS2Web plugin as before to generate your new webmap.

Comment

2 Comments

Convert Fusion Tables to Web Map with CSV

Introduction

Edit: This lesson utilizes the CSV with latitude/longitude values often used for locations. However, Google Fusion Tables does have the option to download the map file in KML which is significantly easier to manage. After reviewing this post, see the KML post at http://www.saylormapping.com/blog/2018/12/20/using-kml-from-google-fusion-tables-to-build-a-web-map However, if you’re using place names or addresses and relying on Google geolocation to handle it; that will still need some additional data and manipulation.

You’ve just heard that Google is shutting down Fusion Tables after nine years. I too feel the pain of this transition as I’ve made many maps with this platform and have taken advantage of some community projects built on it. But take heart, I’m here to show you a way to replicate the same map if not make a better one using a free mapping software called QGIS.

One resource I used when learning Fusion Tables is their tutorial that utilized butterfly data. You can find that tutorial at https://support.google.com/fusiontables/answer/2527132?hl=en. I will teach you how to build this same map using QGIS and the QGIS2Web extension.

For your own Fusion Tables, you can go to the Fusion Table page, go to File, and Download to get the CSV. This tutorial uses a dataset with latitude/longitude columns for location. If your dataset uses the same location method, following this tutorial will get you where you need to go. If your dataset uses a geometry column with KML, QGIS can also consume that with a little different approach I hope to cover later. I’ve got a zoning map I need to get converted over I can use. If I haven’t posted on that yet, pester me on Twitter @gisn8. If you used Google’s auto geolocate feature by just using location names, that’s a little trickier because you’ll need to find a reliable go-between dataset, then standardized your data against it to get the locations mapped. It’s doable, but can be tedious. If that sounds like more than you want to get into, but you really need this map, hit me up on Twitter or contact me at Saylor Mapping and hire me to do this work for you. If we do, we even offer limited hosting if you just need a place to park a few web maps to link or embed.

You’ll need to have a place to park the web files if you are planning on sharing it on your website, so you would need a hosting solution if you don’t already have one. If you need a free or low-cost solution, my tweeps recommend utilizing Github Pages, Neocities, Netlify, or Amazon s3. I don’t have the experience with these platforms to address how to publish there. If you’re already managing your own hosted website, then you’ll feel pretty good about the end result.

If you haven’t already done so, you can download QGIS from https://qgis.org/en/site/forusers/download.html. I am using QGIS 3.4, but the instructions and outcome should come out the same in any 3.x.

CSV Tables to QGIS

Create a project folder on your computer and download this sample insect dataset to it. You find the file a CSV - a plain text file format that can also be viewed as a table by a spreadsheet program. If you’re comfortable doing so, examine the data and you’ll see latitude and longitude fields that QGIS can use to map the data the same way Fusion Tables would. Otherwise, we will examine the table in QGIS a little later.

Open QGIS. Under the Layer menu, open the Data Source Manager. On the left, select Delimited Text. For the file name, navigate to where you saved the insect dataset.

Follow the image above for the options to choose, then click Add. Before closing, we’ll add a basemap layer.

At the top of the left side panel, go to Browser. The Browser lists all your drives and can also view most database and online geo-data resources publicly available. Right click on XYZ Tiles and choose New Connection. We’ll name this Google Physical and use the following URL
http://mt0.google.com/vt/lyrs=p&hl=en&x={x}&y={y}&z={z}&s=Ga

You can leave the rest and click OK. This gets added to your XYZ Tiles in the Browser and will now be available for future projects. Right-click on the Google Physical layer to add the selected layer to the canvas, then you may close the Data Source Manager. If you want a different Google basemap, I’ll share how to get those at the end of this tutorial.

It’s likely the basemap is on top of your insect layer. You can click and drag layers in the Layer panel to get them in the draw order you want them in.

At this point, save the QGIS project going to the Project menu and choose Save As, then save to your project folder naming it whatever you like; I’m using FT to Q.

Have you ever noticed that world maps can come in many different shapes making countries stretch to different sizes? This is because of what’s called projections. I won’t get into the ins and outs of projections, but the longitude and latitude of our insect data was given in one projection, but the basemap is in another. That’s why at this point, the basemap looks a little fuzzy. QGIS is showing our map in the projection of the insect data and warping the basemap to fit it. It’s not an extreme warp, but we could easily change this so the basemap looks right and the point data adjusts appropriately. In the bottom right of QGIS, you’ll see a read-out that tell the EPSG projection code the map is being displayed in: 4326. Click on that to change it.

In the filter, type in 3857, then click on the result in the list below. Click OK to apply and close.

Now that it looks presentable, let’s look at the data table. Right-click on the astraptes_fulgerator_complex_sample_data layer and select Open Attribute Table. As mentioned before we have latitude and longitude, but also we see full path URLs to images of the insects at different stages. We’ll be able to take advantage of this by being able to display these right in QGIS.

Right-click on the astraptes_fulgerator_complex_sample_data layer and select Properties. In the Attributes form menu, we will style how we want the Identify Features form looks when we click on a feature with the Identify Features tool in QGIS.

Under Fields, click on URL adult, then on the right, change the Widget Type to Attachment. You’ll need to then scroll down to find the Integrated Document Viewer to set the type to Web view. Click Apply, then do the same steps for the URL cp lateral field.

Also, since we’re here, if you don’t like the default symbol QGIS gave you, you can go to the Symbology menu from the Properties window to change the size and color, or select and modify a preloaded style offered.

Click OK to close. This is a good time to save the project again.

When using the Identify tool, you may get a side panel pop out. This happens when you don’t have Auto open form checked, or there’s more than one feature within the click tolerance. While the panel is useful, you have more options with the form. After checking the Auto open form, you can close the Identify Results panel if you want.

Try clicking a single point to get the form to pop up.

You may need to resize your form window to get the images to show at a reasonable size. If the image doesn’t load, just click Cancel and try again.

If this is your first map in QGIS, congratulations!

55230d2885edb92328ad0e1e7b7cc77e.jpg

QGIS is an amazingly powerful software that competes with other mapping software that costs thousands of dollars! It’s also open source which means customizations and plugins can be built and shared with the QGIS community. You may have noticed in the screenshots the QGIS icon has been sporting a Santa hat because I’m using a fun little plugin called QGIS Hats and it’s Christmas time as I write this, but most plugins are much more productive. We’ll be using the QGIS2Web plugin to get your map ready for your website.

QGIS2Web

To add the QGIS2Web plugin, go to the Plugins menu and select Manage and Install Plugins. It’s pretty cool sometimes to run through the descriptions of each plugin to see what amazing things people are building. We can narrow our search by typing “web” into the Search bar.

Select qgis2web and click Install Plugin, then Close. By the way, I tried the web2qgis for Fusion Table maps and it doesn’t work for them. Maybe down the road.

Let’s go ahead and rename the astraptes_fulgerator_complex_sample_data layer because it takes a lot of space when we open QGIS2Web. Right-click the layer and select Rename Layer. Let’s call it Insects and press <Enter>.

From the Web menu, go to qgis2web>Create Web Map. If the map doesn’t automatically render, just click Update preview. You can click features and get every item that we saw in the table. By default the field attributes aren’t labeled with the attributes. You can set these to header or inline label styles as shown.

If you’re comparing up to this point in the Fusion Tables tutorial, you’ll see that Fusion Tables automatically pulls in the images from the URL. There's an extra step to do that with QGIS/QGIS2Web, but we’ll cover that with customizing the popup.

Close QGIS2Web and Right-click>Properties the Insects layer again. This time, go to Source Fields. We will add a Virtual Field which is a field that gets calculated on the fly to this layer without saving it to the source CSV file. Click on the button that looks like an abacus. Because QGIS does not edit CSVs directly, adding a Virtual Field is checked by default. If your data source will need editing, it would need to be saved into another format first. This is covered at the end of this tutorial. Set Output field name to “popup”, Output field type to Text, unlimited length (text).

The block of code the Fusion Table tutorial uses is this:

<div class="googft-info-window"
    style="font-family: sans-serif; width: 330px; height: 20em; overflow-y: auto;">
  <img src="{URL cp lateral}" 
       style="width: 150px; vertical-align: top; margin-right: .5em" />
  <img src="{URL adult}" 
       style="width: 150px; vertical-align: top" />
  <h2 style="color: brown">{herbivore species}</h2> 
  <p>Caterpillar found feeding on host plant
     <em>{host plant species}</em>,
      {host plant family} in the {primary eco},{year}.</p>
  <p>See specimen record {voucher} at 
      <a href="http://janzen.sas.upenn.edu/">
      Caterpillars, pupae, butterflies and moths of the A.C.G.
      </a>
  </p>
</div>

BUT we need to modify this slightly to get this to read correctly in QGIS2Web. This may be easier if you have a favorite text editor, but either way we need to find

{

and replace with 

'||"

And the inverse

}

with

"||'

I also found that 

/>

just needs to be

>

Rename the googft-info-window class to just info-window and we need to open and close the code with single quotes to feed it properly into the field as text. We end up with 

'<div class="info-window"
    style="font-family: sans-serif; width: 330px; height: 20em; overflow-y: auto;">
  <img src="'||"URL cp lateral"||'" 
       style="width: 150px; vertical-align: top; margin-right: .5em" >
  <img src="'||"URL adult"||'" 
       style="width: 150px; vertical-align: top" >
  <h2 style="color: brown">'||"herbivore species"||'</h2> 
  <p>Caterpillar found feeding on host plant
     <em>'||"host plant species"||'</em>,
      '||"host plant family"||' in the '||"primary eco"||','||"year"||'.</p>
  <p>See specimen record '||"voucher"||' at 
      <a href="http://janzen.sas.upenn.edu/">
      Caterpillars, pupae, butterflies and moths of the A.C.G.
      </a>
  </p>
</div>'

Let me briefly explain what’s happening here. We’re feeding the HTML as a text string into the attribute table. We can put text together in the field calculator using single quotes for text, double-quotes for field names, and || which is a concatenator. Field Calculator is also capable of evaluating formulas with field variables to get a result, but let’s not get carried away here. So for example, if did something simpler

'The ' || "sex"||' '||"herbivore species" ||' has a wingspan of '||"wingspan (mm)"||'mm.'

it would return an appropriate sentence for each row such as: The female Astraptes SENNOV has a wingspan of 60mm.

But with the block of dynamic text we’ve added in HTML format, the web browser will know how to format our text and images.

You’ll see the Output preview below the code section showing the first part of the code as text. If you get an error there, carefully review the syntax to find the problem. It’s usually something in the punctuation that got missed. Click OK to close and Apply.

Next, go to the Attributes Form section of the Layer Properties.

You will need to set the Widget Type to Hidden for all the fields except the popup field we just added. If you don’t see the popup field listed, just click OK to close, then re-open the Property Layers again. It should be there this time. If not, you may need to try adding the Virtual Field again. Once the other fields have been set to Hidden, Click OK to apply and close.

Launch QGIS2Web once again. Once the preview is updated, you can see the pop up look more like what we are looking for. Additionally, I prefer using the Leaflet version over OpenLayers for this map. If you haven’t noticed, you can choose between the two next to the Update preview button. There’s also a Cluster option that Fusion Tables would have been better had they included this ability. The Cluster option is found above the Popup fields section. It’s what shows that there are multiple overlapping point features. The further you zoom in, the more they’ll break down to their individual locations. With Leaflet, clicking on the cluster will automatically zoom you in to further inspect the cluster distribution. Alternatively, with OpenLayers, you can click on the cluster and the popups will just show one after another as you scroll the popup window. For this project, either one will work, it’s just a matter of which features one offers over the other that you prefer.

Lastly, we need to export the web files. Go to the Export tab and navigate to your project folder. Then next to your Update preview button, hit Export. The web files will be created into a qgis2web folder that will also have the timedate stamp in the folder name in case you have several you’ve exported during testing and development. Once export is complete, it will open the map in your default browser for your review.

Sorry this tutorial couldn’t be as short as the Fusion Table tutorial, but QGIS has a lot of capabilities; this is just one of them. If you’re new to QGIS, I hope this gets you interested in learning more about it!

For Fusion Tables that utilize KML geometry, see http://www.saylormapping.com/blog/2018/12/20/using-kml-from-google-fusion-tables-to-build-a-web-map.

Exporting to an Editable Format and Editing

If you need to edit your data, you will need to export the your layer to another format. To save the layer, right-click>Export>Save features as. The default format option in QGIS is usually GeoPackage which I too recommend. GeoPackage is a portable database format similar to SQLite, Esri Geodatabase, or Access .mdb databases, so it’s a single file that can house multiple layers and tables. But there are numerous format options available as well. Shapefile and GeoJSON are also both popular options. If you save to a GeoPackage, you’ll need to save the file name and the layer name, otherwise, just a file name. Everything else can be left with default and saved. It should automatically load in the map.

If you already did all that formatting work on the CSV layer and want this layer to have the same look and feel, you’re going to appreciate this! Right click on the original Insects layer and use Styles>Copy Style>All Style Categories. Then on your new layer, Right-click>Styles>Paste Style >All Style Categories. Exporting to a GeoPackage will add an FID field that will need to be hidden before updating the web map.

To edit your data, right-click the layer and Open Attribute Table. You toggle on edit more by clicking the pencil icon in the upper left. Then you can scroll your way to the data that needs edited. Toggle off and opt to save or revert your data by clicking the pencil icon again.

Other Google Base Maps for XYZ Tiles

Google Basemap - http://mt0.google.com/vt/lyrs=m&hl=en&x={x}&y={y}&z={z}&s=Ga

Google Hybrid - http://mt0.google.com/vt/lyrs=y&hl=en&x={x}&y={y}&z={z}&s=Ga

Google Imagery Only - http://mt0.google.com/vt/lyrs=s&hl=en&x={x}&y={y}&z={z}&s=Ga

Google Labels Only (for hybrid) - http://mt0.google.com/vt/lyrs=h&hl=en&x={x}&y={y}&z={z}&s=Ga

Google Physical - http://mt0.google.com/vt/lyrs=p&hl=en&x={x}&y={y}&z={z}&s=Ga

Google Roads - http://mt0.google.com/vt/lyrs=r&hl=en&x={x}&y={y}&z={z}&s=Ga

Google Terrain - http://mt0.google.com/vt/lyrs=t&hl=en&x={x}&y={y}&z={z}&s=Ga

2 Comments