Recently I was dealing with geotagging single addresses in Google Maps and other easily accessible tools. However, the stuff, that I focused on is playing around a single placemarks only. I believe, that this operation is much more useful when we take into account not only the address but other details, related to this place.
For this purpose, I would like to show today how to make a geotagging our whole spreadsheet in order to its further display on the map. See the steps and advises below to understand how can you do this process swiftly.
The picture below shows the simple spreadsheet containing both addresses and the details assigned to them in other columns, which we would like to have on the map (Pic. 1).
The aforementioned list includes column names with spaces. For the time being, I will show you the process of keeping no spaces in these names, so it should make you aware of initial MS Excel data preparation for further GeoJSON and JSON file usage.
This geotagging theoretically could be done more simply via Google MyMaps. Unfortunately, Google MyMaps cannot manage with a bulk list of addresses such that. Whilst these locations work on the Google MyMaps platform, there is no option to apply them outside, due to a lack of geometries.
At first glance, everything looks alright. You have all the information included in the popup balloon as well as a proper placemark location (Pic. 3).
But after transferring it to Google Earth is not so funny (Pic. 4). We don’t have our points at all. The only way to find them is by clicking on some of them in the folder tree at the left sidebar. Then we could see sth like regions bounding these locations. This is not a thing, that would be desired by us. The data must be geotagged properly in a different way. We can try to save our Google Earth file as a .kml again (Pic. 5). Sometimes works, but the best way is to use another tool for proper geotagging.
A similar issue can be spotted in the geojson.io tool. In case of instant conversion, our .csv file to the .geojson or .json format first thing, that comes to mind is using i.e. this tool. Unfortunately, if we owe the postcodes only, this tool won’t be always able to do this job for us. Ultimately we will get an error, or even nothing has shown on the map (Pic. 6).
Converttocsv tool also won’t help you to solve this issue (Pic. 7).
In this event, we must definitely find another way to assign the coordinates to our addresses first and try to convert our .csv file again. For this purpose we can do this in 2 ways (although there are more tools on the Internet, to be honest): by MapYourList or Google Earth.
GEOTAGGING WITH MAP YOUR LIST
A decent tool, where you can quickly geotag your addresses is the MapYourList tool. There is the default data provided, although you can easily copy your stuff (Pic. 8).
Copying our .csv content into the MapYourList tool we must remember, that max. value of geotagged placemarks for free is 250. Otherwise only the first 250 will be geotagged with any exceeds being omitted.
Once your data is ready, click the green button “Quick Map” below and see how your geotagging process is going (Pic. 8).
It may take a while when your list is big. Finally, you should have all the markers geotagged and visible on the map (Pic. 10).
The next step is saving these geotagged places as a .kml file, being the most convenient output of the MapYourList tool (Pic. 11).
After saving, your file is ready for conversion to the .json or .geojson format. See the example from geojson.io below for the time being (Pic. 12). I will show you also a different option later.
Seeing a proper amount of features imported in the top left corner (other than 0) we are sure, that everything is alright.
If you are keen on using the MapYourList tool for more than 250 records you can split your bulk data into more than 1 piece and binding them as one after a whole process.
GEOTAGGING WITH GOOGLE EARTH
The geotagging process via Google Earth is based on the import MS Excel spreadsheet into this tool. The saving our data as an .csv file instead of using pure .xls (.xslx) is still required.
Comparing with the MapYourList, Google Earth is capable to convert 10x more records, however as we know, this software is memory consuming, so it can make your desktop slower for a while.
If you are coming through the “Import” option, the pivot thing is setting the Longitude/Latitude fields, that in our case should remain unknown, as we owe addresses only (Pic. 13).
Because our data is bigger than in MapYourList example, we should wait longer for an output.
Sometimes, when some addresses aren’t written properly, the application asks us about some small amendments (Pic. 16).
After these small fixes, our data should be ready to display in the application (Pic. 17).
It looks like the geotagging issue we have already closed. Now is the time to make our data readable in the .geojson or .json format.
At the very beginning, I would like to tell something about these 2 formats. Some people states, that the JSON and GEOJSON format is the same. It’s not true at all.
The difference between the GeoJSON and JSON format has been clearly explained here. The code looks slightly different in order (Pic. 18).
However, both formats can be used in the data display purpose.
CONVERT TO THE GEOJSON FORMAT WITH GEOJSON.IO TOOL
Having our data geotagged, we can import them to the aforementioned geojson.io tool. Once everything is alright, in the top left corner, we should see a number of items imported other than 0 as well as generated GeoJSON code on the right panel (Pic. 19).
If we have our MS Excel spreadsheet columns sorted, then we can go through the steps shown above until we reach the geojson.io tool.
The GeoJSON code in the geojson.io tool looks nice, although after saving it’s minimalized (Pic. 21).
To have the same view as in the tool sidebar we must use the JSON Pretty Print Online tool. It makes our code pretty-looking and easier to read. Alternatively, you can select and copy the GeoJSON code straight from the geojson.io right panel, pasting it next to the Notepad++ and save.
CONVERT TO THE GEOJSON FORMAT WITH CONVERTCSV TOOL
Another easy tool, where we can quickly convert our files is the Convertcsv.com.
There are 2 ways to get this issue sorted. The first one is more convenient and comes from our 1st converter, which is the MapYourList application.
Because we have already saved our .csv file there. Now this .csv file must be attached to the tool and converted into .geojson or .json format (Pic. 22).
We can both paste our data, when is not big or attach a whole file. In the “Result data” underneath you will see upcoming the JSON or GeoJSON code.
The code generated by the Convertcsv tool doesn’t need a pretty print option. It’s clear from the very beginning, so you can use it.
DISPLAYING OUR DATA IN THE LEAFLET OPENSTREETMAP API
The variable (var) geojsonMarkerOptions will customize our placemarks, making them other from the default provided.
Another variable will contain the GeoJSON layer, which should come from the external .js file, where we set a variable for the GeoJSON data. The pointToLayer section determines our marker as well as its pop-up properties, based on the .geojson properties, which names come from the MS Excel spreadsheet.
As an output, we should have a placemark with a popup window (Pic. 25).
The names shown in the popup box comes from the MS Excel spreadsheet columns. Now you know, that very important thing is determining these column names at the very beginning, once our file is crude in .csv format. For sure, the popup field presents an example, that definitely must be tweaked up for better performance. At least you know, that the option, which I have been showing you in this article is quite easy to do.
The ways I have shown here are examples amidst more options available both in the web tools as well as GIS software. I have chosen the options, that not require additional logins on some platforms, that could take time and also I focused on one of the easiest ways to do it. Thanks to this, you can make this georeferencing relatively quickly for your purpose.
- Input a multiple address list to Google Maps and Google Earth
- The fastest way to execute UK address list using MySQL and PostgreSQL