Mapping Overseas France with Tableau
A primer on making granular country maps inclusive of overseas territories
When you imagine France, this is most likely the shape that comes to your mind:
This image is actually Metropolitan France (la France métropolitaine), made up of two elements: mainland France (la France continentale) and Corsica Island (la Corse). This is also the shape that Tableau automatically displays when you choose a geographic dimension called “France.”
But France is more than that. This country comprises various overseas territories, mostly islands, that fall into two categories: overseas departments (départements d’outre-mer, DOM) and overseas collectivities (collectivités d’outre-mer, COM).
Note that you won’t need any of the overseas territories if you are making a World map, or a European map with comparison at country level. Why ? Except for French Guyana, most of them are too small to be seen from a worldwide zoom, and anyway, you won’t likely want to invest the effort for every country that has overseas territories to show (“hey, there is also this island here and there, and they have absolutely the same value as the country they’re associated to”).
But imagine you work for an international organization, such as the European Union. As you might imagine, a country’s sovereignty and territorial integrity are politically-sensitive issues and require consideration in your approach to overseas territories.
Here, Tableau is mischievous, because it automatically displays all of Spain’s overseas territories at Country dimension level, including the Canary Islands lying next to Morocco, while excluding France’s. Here are examples below.
Beyond the aesthetics, you can’t publish, as a public institution, a map of European countries inclusive of Spain’s overseas territories, while excluding them for others.
Should you find yourself in this situation, I have developed a four-step process to aggregate the territories into one country-level group dimension before creating your map. I’ll also share my approach to developing a more granular map, with separate values for each of these territories.
Defining France
What we call France is a sovereign State, made up of various territories, both Metropolitan and Overseas.
And all the Constitutions since 1848 (we’ve had many) are explicit about it: the [French] Republic is indivisible. It means you cannot consider French Polynesia for example, in the middle of the Pacific Ocean, as a separate entity from France.
But indivisibility does not mean uniqueness. For Overseas France, it depends on whether these territories fall under the DOM or COM category.
The DOM (overseas departments, départements d’outre-mer) are, legally speaking, identical to Metropolitan France departments. The COM (overseas collectivities, collectivités d’outre-mer) are more complex, because some of them use their own currency and elect their own presidents.
Overseas France, DOM and COM, is made up of twelve administrative territories, located near Brazil, Canada, Australia, Madagascar, and the Fiji Islands.
Located in the Caribbean Sea, north of Venezuela and east of Cuba: Martinique, Guadeloupe, Saint-Martin, and Saint-Barthélémy islands.
Located North of Brazil: French Guyana (la Guyane).
Located South of Newfoundland, near Eastern Canada: Saint Pierre and Miquelon islands (Saint-Pierre-et-Miquelon).
Located in the Pacific Ocean: French Polynesia (la Polynésie française) and Wallis and Futuna (Wallis-et-Futuna).
Located East of Australia and North of New-Zealand: New-Caledonia (la Nouvelle-Calédonie).
Located near Madagascar, East Africa: Reunion Island (La Réunion) and Mayotte.
The French Southern and Antarctic Lands (Terres Australes et Antarctiques françaises, TAAF) are four territories that consists of islands with no permanent civilian population near the Antarctic, as well as Adélie Land (la Terre Adélie) on the Antarctic, with a disputed French sovereignty, and hence not automatically mapped as part of the TAAF by Tableau.
Not mentioned: uninhabited Clipperton Island in the Pacific Ocean, located West of Costa Rica, Central America.
How does Tableau manage these territories?
Overseas Departments (DOM)
DOM in Tableau are managed as standard French territories. The geographical information you can use are identical: City (commune), ZIP Code/Postal Code (code postal), County (département), and State/Province (région).
In French administrative law, some local authorities bear the responsabilities of two categories. For example, Paris serves as both a city and a département (County), and hence is being recognized under both geographic roles in Tableau. The same rule applies to DOM, that are recognized under both County and State / Province roles. This means you can compare the island of Martinique with the “County” of Paris or its “State/Province” (Ile-de-France). The role you choose will depend on the level of detail of your database, either aggregated at département or regional level.
There are 5 DOM: Martinique and Guadeloupe in the Carribbean Sea, French Guyana in Southern America, and Reunion Island and Mayotte near Madagascar, east of Africa.
All other overseas territories are COM.
Overseas collectivities (COM)
In Tableau, COM are treated as autonomous countries and hence categorized at the same level as Spain or China. In other words, you can’t create a geographical hierarchy with France at its top, and a COM as a subdivision. Tableau would simply filter them out from your selection.
As a French national, I consider this default controversial.
And from a Tableau user perspective, it generates problems because French open data often use one unique geographical dimension to describe territories, either metropolitan department, DOM, or COM. That’s the case of the local authorities councilors database you can download from the French Open Data Portal, where you will find both “Paris” and “Nouvelle-Calédonie” under “Libellé de département” dimension.
How to create an Overseas Map
For this tutorial, I will use the city councillors data from the source cited above (“1-rne-cm.txt”). This database comprises information solely on French Polynesia, New-Caledonia, and Saint-Pierre-et-Miquelon among the COM category.
Step 1: Create separate geographic dimensions
The purpose of this first step is to take into account Tableau’s differentiated treatment of COM. Right-click on “Libellé de département (Maires)” and create a new calculated field named “Departments” as below.
if [Libellé de département (Maires)] = "Nouvelle-Calédonie"
then NULL
elseif [Libellé de département (Maires)] = "Polynésie française" then NULL
elseif [Libellé de département (Maires)] = "Saint-Pierre-et-Miquelon"
then NULL
else [Libellé de département (Maires)]
ENDThis simple calculation will change into NULL values the names of COM present in the database. You will need to assign a geographical role of “County” to this new dimension because all other geographical information is aggregated at département level.
As a general rule, for this first step, you must check the dataset, list all occurrences that fall under the COM category, and manually exclude them from the data. If this data comprised information over, for example Wallis-et-Futuna, you would need to extend your calculation to this occurrence.
Once your “Department” calculated field is created, create a new field based on it, which you will name “COM”.
if [Departments] = [Libellé de département (Maires)]
then NULL
else [Libellé de département (Maires)]
ENDThis calculation permits you to create a mutually-exclusive dimension that only comprises data of the previously filtered out COM.
Finally, assign this new “COM” dimension a role of “Country.”
Step 2: Create a double-axis map
The purpose of a double-axis map is to show geographical dimensions that exist on different geographical levels. (This old Tableau trick may be soon be outdated by the multiple layer feature introduced in version 2020.4.) An example of a double-axis is: an overall measure aggregated at a State/Regional level with a filled map and a specific measure aggregated at City level represented with a dot map.
I will use this feature to simultaneously depict the actual départements and COM on a unique map, and align the design with the legal reality. In other words, make a map as if Tableau understood COM as an integral part of the French territory.
First, double click on the newly created “Department” dimension. A map is created, with a wide perspective showing many values concentrated on Metropolitan France, as well as two dots in the Carribbean Sea, one dot in South America, and two dots East of Africa. These are the five DOM I mentioned before.
Second, CTRL-drag the Latitude (Generated) field to copy it and put it next to the first latitude field on the row-shelf. This maneuver will create a double-row map, both identical.
Third, click on the top Latitude (Generated) tab to display the map components. You can see that Latitude and Longitude are generated from your newly-created “Departments” field. Click and drop your newly-created “COM” field on “Detail” and delete “Departments” from this tab.
Now, you should have two maps: one with the départements, including DOM, and another with three COM: French Polynesia, New-Caledonia, and Saint-Pierre-et-Miquelon.
All you have to do to finalize your double-axis map is to right click on the second “Latitude (Generated)” field on the row-shelf and select “Dual Axis.” Now, you have merged your maps.
Step 3: Optimize the appearance of your map
Use the same parameters for both geographic dimensions to ensure the integrity and consistency of your design. Simply drag and drop the same information under both “Latitude (Generated)” tabs and regularly check the coherence of your Tooltips.
You may notice that your map is completely unreadable. That’s because the perspective is too wide to adequately show geographic information on small territories.
You can resolve this one of two ways, depending upon your design preference and user needs.
Step 4 (option 1): Create an unzoomable Overseas Map
The simplest and fastest solution is to disable the zoom feature for your users. That’s the best solution if your data is aggregated at département or regional level, but less recommended if you have many values per city.
The most important step is to make sure you finalize your map, with all the elements you need, before adapting the design to include your overseas territories.
Once you’re certain you won’t change anything, manually zoom on Metropolitan France and anchor this level of zoom while disabling all user zoom options from the “Map Options” menu.
Once you are satisfied, duplicate the sheet of your map, rename it “Martinique” and, you guessed it, change the anchored zoom to Martinique Island in the Caribbean. Then disable user zoom options and duplicate the sheet again.
You have to repeat this (tedious) procedure for each overseas department and collectivity in your database. In the case of our city councillors database, which comprises five DOM and three COM, you would need to create eight separate overseas maps.
Last, I recommend that you use a standard size to emphasis Metropolitan France in your dashboard and then scale your overseas maps smaller (either horizontal or vertical), situated next to the metropolitan map. This solution can be implemented as visually separate features or as floating objects over a more classical map of Europe.
Step 4 (option 2) : Create a zoomable Overseas Map
If your database comprises many values per city, it is wiser to allow your users to zoom. This is an issue I faced while designing (in a hurry to respond to an urgent need by health authorities) a map of the COVID-19 vaccination centers: people must know where exactly in a city a center is located.
If you anchor the zoom from the start, this automated zoom-in option will be inoperable and your visualisation will look like it’s broken. Conversely, if you don’t anchor any zoom, the visualisation will start with a world zoom-out, completely irrelevant for your users.
The solution is to create smaller maps of every overseas department and collectivity in your database on the side, as described in option one above, and add further options to your Metropolitan France map, for which you will allow both filtering and zooming features.
In other words, your objective is to create your “central map,” showing Metropolitan France upon opening the visualization, enabling manual zoom and automating relevant zooming if the user specifies them.
To achieve this result, you need to first create a parameter that will let users provide information for prefiltering, named “User Dept Number.” I used the département number, but you could use the département name from the database.
Here is the formula to extract the département number from a postal code if it’s not immediately available in your database.
//French dept. numbers are made up of 1 to 3 digits.For joints, be careful if your
if LEN(STR([Code postal])) = 4 then LEFT(STR([Code postal]),1)
elseif LEFT(STR([Code postal]),2) = "97"
then LEFT(STR([Code postal]),3)
elseif LEFT(STR([Code postal]),2) = "98"
then LEFT(STR([Code postal]),3)
else LEFT(STR([Code postal]),2) END
Then you will create a new field calculated from the above, serving as a filter to take into account the user’s département number. Another function of this filter will be to exclude all overseas departments (DOM and COM) to display only the “relevant” map of Metropolitan France at the opening of the dataviz.
// First, no specific filter if the user inputs no dept. number
if [User Dept Number] = NULL
Then "OK"// Then, keep values as long as they don't match the beginning of an overseas département or territory.
elseif [User Dept Number] = "" and left([Department Number],2) <> "97" then "OK"
elseif [User Dept Number] = "" and left([Department Number],2) <> "98" then "OK"// Last, only keep values corresponding to the user's input, if any.
elseif [User Dept Number] = [Department Number]
then "OK" else "KO"
END
Last, apply this filter to your Metropolitan France map and keep only “OK” values.
The trick here is, simply, to exclude by default overseas values to automate the relevant zoom on Metropolitan France when opening a viz.
Now, your map zooms on Metropolitan France upon opening. If you input a département number in the parameter, let’s say 75 for Paris, your map will zoom in to show only values in Paris. Conversely, if you put an overseas département number, such as 971 for Guadeloupe, your map will zoom in to the Caribbean to the island of Guadeloupe.
This second method is the best in terms of user experience. It enables the intuitive map features of Tableau, while still ensuring that your maps zoom at the relevant metropolitan level upon opening. Relatedly, this method is the best when you have very granular data, such as postcode, city, or specific geocodings.
Conversely, the zoom features of Tableau are not always understood by users who are unfamiliar to it. A decently-sized map of metropolitan France displays adequately all data at département and regional levels, without the need of implementing all features described for option 2 of step 4 (above).
Regardless the option you choose, keep in mind that with this methodology you can create an actual map of France. Countries are a complex matter, and so is France. It would be regrettable if, due to technical reasons related to Tableau, you created an incomplete dataviz because you didn’t know how to handle overseas territories. Hopefully, this practice broadens your horizons and encourages you to discover further territories and cultures around the globe.
Alternatively, you can also download this pre-programmed viz directly from my Tableau Public profile ;).
Further general advice for managing French geographical data
The quality of geographical mapping is lower for France than it is for the US. Tableau automatically recognizes cities above a certain threshold of inhabitants. If you map at city level, I recommend you work directly from GPS coordinates in your database, or use geographic files from third-party sources. One great source is the mapping from Open Street Map available for free here.
If you have to join different databases at city level, never use their name (4,000 homonymous cities), but instead use its unique identifier established by the national statistics institute (“Code INSEE”). It is made up of the department number and an alphabetical ascending order on three digits. In the local authorities councillors database I referenced earlier, you can create your own by merging “Code du département” and “Code Insee de la commune,” taking care that these fields are understood as dimensions to keep the departments numbers comprising letters (e.g., former Corsican departments and some COM).
Postal codes from institutions can be different from the actual postal code understood by Tableau. Postal codes usually end by “0,” unless it is a district of a major city such as Paris, Lyon or Marseilles.
After several years of experience in civil service, Alexis Bernard is now Senior BA in the daycare industry, and keeps practicing dataviz to decipher French politics, society, and culture on his blog and Tableau Public profile.
