Example: Create a Map Showing OSM Use by Country

In this example, we show a start-to-finish, real life process of map creation that combines various Manifold capabilities.  Copying a table of numbers from a web site, we create a map that is thematically colored to show usage of OpenStreetMap by country, in proportion to the population of that country.  

 

This topic is lengthy, because it shows step-by-step procedures that require many screens to illustrate and paragraphs to discuss, but which an experienced Manifold user would do in just a few minutes.  It also shows how we can use Manifold facilities to get around the inconveniences of real world data.

 

 

We will create a thematic map like that seen above.   We can break down the process into several steps:

 

 

Acquire data

Reading a discussion of OpenStreetMap (OSM) use in a forum, we encounter a link to a web page that provides a list of bits per second OSM use by country.   Each country is named by the ISO A2 country code that OSM uses.  The web page is seen below in Microsoft's Edge browser.

 

 

To grab the data, we highlight the text table in the web page from beginning to end.  

 

 

After highlighting the text all the way to the end, we press Ctrl-C to Copy the text to the Windows Clipboard.

 

Next, we launch Notepad or whatever other text editor we prefer.    In Notepad, we press Ctrl-V to Paste the text.

 

 

We see at the beginning we have copied three dashes.  We highlight those...

 

 

...and then replace them with the word Bandwidth.   Our plan is to import the text using Manifold's CSV dataport, so we add a word the dataport can use as the name of the column.  The first row in a .csv file usually gives the names for columns.

 

 

In Notepad, we choose File - Save As to save the file using the name osm_bandwidth.csv.   

Import data

We launch Manifold.

 

 

We choose File - Import.

 

 

In the Import dialog we navigate to the .csv file we saved, click it and press Import.  We could also simply double-click the file to import it.

 

 

A new table containing the imported data appears in the Project pane.

Clean up the Table

We can double-click the table to open it.   We can see right away we need to clean the data a bit.

 

 

The CSV dataport guessed that the colon characters were intended to separate two fields, so it imported the data as two columns, synthesizing a name for the second column.   We will clean up column names.   The first column also has the row number from the text table in the website, which is an unwanted addition to the list of country codes.    Finally, the gray background for the columns indicates the table is read-only.  

 

As discussed in the Add an Index to a Table topic, a table must have at least one index in it to be read/write/selectable.  CSV files do not have indexes.  When Manifold imports from any format that might be a database format, the system will not automatically add a key field and index to make the table read/write, since that changes the structure of the table.   We can add that easily.

Rename Fields

We begin by renaming column names to more useful names.   With the focus on the open table, we press Edit - Schema to launch the Schema dialog.

 

 

In the Schema dialog, we double-click the Bandwidth cell to change the name.

 

 

The Field dialog launches.  We change the name to Country and we press OK.

 

 

In the Schema dialog the renamed field appears in provisional, bluish color, indicating that the change has not yet been committed, that is, saved as a change in the table.

 

We double-click the Column 2 cell to change the name.

 

The Field dialog launches.  We change the name to Usage and we press OK.

 

In the Schema dialog the renamed field appears in provisional, bluish color, indicating that the change has not yet been committed, that is, saved as a change in the table.

Add an Identity Field and Index

We will now add a new key field and index, with one click of the Add Identity command button, as described in the Add an Index to a Table topic.

 

 

Press the Add Identity command button.

 

 

Instantly, an mfd_id identity field appears as well as an mfd_id_x btree index on the mfd_id field.  

 

Press Save Changes  to update the table's schema.

 

 

That is better.   The table now has white background to indicate it is read/write, and the column names make sense.  In the illustration above, we have also applied Best Fit Title to all of the columns  We can do that by right-clicking each column head in turn and choosing Best Fit Title, or we can do that all at once for all columns by using the Layers pane.

Clean Data in the Country Field

We will now clean up the Country column, where we would prefer to see just country codes and not numbers with country codes.   To do so, we will use the Transform pane.

 

With the focus on the osm_bandwidth table window, In the Transform pane we choose the Country field and then we double-click the Replace pane to launch it.

 

 

In the illustrations below, we show the Transform pane on the left and what it does to the table in an adjacent illustration.

 

 

In the Replace template we choose regular expression for the Replace option.   For the Result destination, we will use the default Same Field, which will transform the contents of the Country field in place.

 

The template finds text that matches the regular expression pattern given in the Search for box and then replaces that text with whatever text we place in the Replace with box.   If the Replace with box is empty, the template deletes whatever text is found that matches the regular expression pattern.  

 

 

In the Search for box we enter .*\s  as the regular expression.  A dot . matches any single character, an * asterisk says to repeat the preceding zero or more times, and the \s matches any whitespace character such as a space character or a tab.   The regular expression we use matches any sequence of one or more characters followed by a whitespace character.   It therefore matches all the occurrences of the numbers appearing before the country codes.   

 

Press the Preview button to see a preview.    A blue preview column appears at the right hand side of the screen with the name of the template used for the preview in the column head.  A dot icon appears in the Country column head to indicate that column will be replaced with the preview.    

 

 

The blue preview column is a virtual column that is drawn above the table.  We can drag the blue preview column to the left if we like, so that it appears next to the Country column, covering the column below, to make comparisons easier.   We can also resize the preview column by dragging the borderlines to make it wider or narrower,

 

Since the Replace with box is empty, the template will replace all of the text matching the pattern with nothing, deleting all matches to the pattern.  The preview shows the resulting effect, which is exactly what we want.  Previews in Manifold are a great way not only to learn how to use regular expressions, but also are tremendously helpful in avoiding errors.  

 

 

We like what we see in the preview, so we press the Transform button to apply the template.   The Country field has now been cleaned, containing only the two-letter country code for each country.

Import a map of countries

We now need a layer that shows countries in the world, with an attribute that has for each country a two letter ISO A2 code.   We will download a medium scale layer of countries from the Natural Earth website.

 

 

In Manifold, we choose File - Import and browse to the folder in which we have unzipped the package containing shapefiles.  We click on the .shp file and then click  Import.  We could also just simply double-click the .shp file to launch the import.

 

 

A drawing and the drawing's table appear in the project pane.  These use very long names, so we will rename them to use shorter names.   

 

 

We right-click onto the drawing and choose Rename Related.

 

 

In the Rename Related dialog, we double-click into the name of the drawing to edit it.

 

 

We change the name to Countries, and then we press Enter to accept the change.  

 

 

The dialog updates the name of the table as well.  This may not seem like much, but the dialog is also updating all the references within the drawing to the table.   When renaming and updating larger constellations of related components, such as labels that depend on the drawing or maps that use the drawing as layers, the Rename Related dialog saves a lot of manual work.

 

Press Rename.  

 

 

The drawing and table names are renamed to the shorter names we specified.

Create a Map

Next, we create a map to show the different layers we will use.    We will create a map that uses Bing streets as a background, base layer, and which shows the Countries drawing as a layer.

 

 

We right-click on the Countries drawing and then choose Create - New Map.

 

 

The Base layer box remembers the last image server we have used to create a map.   The pull down menu for the box is loaded with all image servers in our Favorites list.  If this is the first time we are creating a map using the menu, or if we previously created a map without using an image server as a base layer,  the Base layer box will show (none).   If the Bing streets image server is not in the box by default, we choose it from the pull down menu.    

 

We leave the coordinate system set to Pseudo-Mercator, the default.   That is a good choice when using image servers as background layers, since they almost always use Pseudo-Mercator as well.

 

The layers list automatically shows the Countries drawing as a layer we want to use in the map.  It knows that since we right-clicked on the Countries drawing to launch Create - New Map.

 

We press Create Map.

 

 

A new map appears in the drawing, called Map.    A Bing streets data source also appears in the map.  That is the data source from which the Bing image server layer appears in the map, as discussed in the Example: An Imageserver Tutorial  topic.

 

 

We double-click the Map to open in, and then we zoom into the map to create the view above.  The illustration above shows the map as an undocked window.  We can shift-click the title bar of the window to dock and undock it.  

 

The Countries drawing appears as a layer above the Bing base layer.   Creating a map using the Countries drawing as a layer together with a "known good" Bing layer is a great way to verify that the shapefile we imported has correct projection information in it.  We can see by how the Countries layer neatly overlays continents and countries shown by the Bing layer underneath that it is correctly georegistered and that it has been imported with the correct coordinate system / projection in use.  

 

 In what follows, we use a cleaned-up version of Countries Table that has had unnecessary fields deleted. Natural Earth data is great, but it does include dozens of attribute fields in which we are not interested.   See the Notes at the end of this topic for how to use the Schema dialog to clean up the Countries Table.

JOIN Usage data with Countries

Our next task is to add usage data from the osm_bandwidth table to the Countries table, automatically copying the usage data for each country, so we can color countries by that usage data.   That operation is called a JOIN, and we have two ways to do that in Manifold.  We will show both in this example:

 

Quick and Easy Join using the Join Dialog

The Natural Earth data set used to create our Countries drawing and Countries Table has a staggering amount of information for each country with very many attributes.  For our purposes, we only care about the name of the country and the international code for the country, which we will use to match against the Country code in our osm_bandwidth table to get the Usage for each country.

 

 

The illustration above shows the Countries Table. We have used the Schema dialog to remove all fields except those we are interested in.

 

With the focus on the open Countries Table window, we choose Edit - Join in the main menu.

 

 

In the Join dialog, under the Countries Table box in the upper left corner of the dialog, we choose ISO_A2 as the key field to use for that table

 

In the upper right corner we choose osm_bandwidth as our joined table.  We choose Country as the key field to use for that table, so that values in the ISO_A2 field can be matched to the Country field.

 

 Press the Add button to add a new field.

 

 

 The pull down menu shows fields in the osm_bandwidth table.  We choose the Usage field.

 

 

That will add a new field called Usage to our Countries Table, and will copy into that field with the contents of the Usage field from the osm_bandwidth table, automatically matched to each country by using the country codes in the corresponding fields in each table.

 

We press Join.

 

 

The new Usage field appears in the Countries Table.

Normalize by Population

Each record in the Countries Table, has mfd_id, NAME, ISO_A2, POP_EST, and Geom fields,  plus the Usage field from the osm_bandwidth table.   We could create a drawing from that table right now, if our objective was simply to show OSM usage by country.

 

However, we want to show OSM usage by country per unit of population, so we will add a new, computed field that makes that calculation.    That is easy to do using the Schema dialog.

 

With the focus on the open Countries Table window, we choose Edit - Schema from the main menu.

 

We press the Add command button and choose Field.

 

 

We enter the name Use_per1K and choose a Type of float64.  

 

We would like to create the new field as a computed field, so we press the Edit Expression button to launch the expression builder dialog.

 

 

In the Expression dialog we enter the expression:

 

([Usage]/[POP_EST])*1000  

 

That computes the use per person, scaled by 1000 for the use per 1000 persons.   We press OK.

 

 

Back in the Field dialog, we see the expression we have entered.  We check our work and press OK.

 

 

The new computed field appears in the schema using provisional, blue background color to indicate changes have not yet been committed to the table.  Reviewing our work, we like what we see so we press Save Changes to apply changes to the table.

 

 

The table is immediately populated with a new column, called Use_per1K, that is filled with the values automatically computed for each record.  The column has a gray color to indicate it is read-only: the values within are automatically computed.  

Style the Drawing

We now will Style the Countries drawing to color countries by the Use_per1K field.

 

 

With the focus on the Countries layer in the map, we use the Style pane.

 

 

We will use thematic formatting, as described in the Style: Thematic Formatting topic.  We click on the Fill Color button for areas, and then click in the field selector control that opens up, to choose a field to use.

 

We choose the Use_per1K field.  

 

 

The default intervals and choices for thematic formatting appear.

 

 

Using the methods illustrated in the Example: Add, Delete and Edit Thematic Formatting Intervals  topic, we specify the thematic intervals and colors shown above.   A short cut way of doing the same is to specify 8 breaks, press Tally and then edit the numbers in the intervals and finally applying the Color Brewer CB Spectral palette.   

 

We press Update Style to apply the thematic format.

 

 

The Fill color for areas is instantly colored for each area by the value of the Use_per1K field.   This is a classic thematic format presentation, where countries are colored by their usage of OSM, normalized to the population of the country.

Slimming down the Countries Table

The Countries drawing we downloaded from Natural Earth has dozens of attribute fields in the table that we do not need, so using the Schema dialog we will delete all fields except those few we want to keep.    It seems like half the time we are doing GIS, we are cleaning up data and whipping it into a form that is easier to use.  Removing many fields that are bundled into Natural Earth data which we do not care about is an example of such routine work.  

 

We choose Edit - Schema to launch the Schema dialog.

 

A quick way to keep only a few fields from very many is to select those fields we would like to keep, then invert the selection, and then press the Delete button.

 

 

We Ctrl-click the first field we would like to keep, to select it.  It appears in red selection color.   Like everywhere in Manifold, we Ctrl-click something to select it.   See the Selection topic.

 

 

We continue ctrl-clicking those fields we would like to select.

 

 

With over a hundred fields in the table, using the filter box is a handy way to find fields we would like to select.  For example, we can enter pop into the filter box to reduce the number of fields to only three, making it easy to Ctrl-click the POP_EST field to select it.   Filtering using the filter box does not change any selections, so when we clear the text from the filter box to show everything in the list, what we selected before is still selected.

 

 

We would also like to keep the indices built on the fields we are keeping, so we Ctrl-click them as well.   

 

When we have selected all of the fields we would like to keep, we press Ctrl-I to invert the selection.  Choosing Edit - Select Inverse will also invert the selection.

 

 

Inverting the selection will select all of the fields we want to eliminate.

 

We press the Delete button to delete the selected fields.

 

 

That leaves only the fields we want.   We press Save Changes.

Using SQL to do the JOIN

A completely alternative way to do the JOIN operation for which we used the Edit - Join dialog is to use SQL.   If we use SQL, we can at the same time create a cleaned up version of the Countries Table that has many fewer fields.   For a full discussion the JOIN statement in SQL, see the JOIN Statements topic.

 

The illustration below shows some of the many fields in the Countries Table as originally imported from Natural Earth before it was cleaned up by reducing the number of fields.

 

 

The Natural Earth data set has an astonishing amount of information for each country with very many attributes.  It has the A2 code for each country, for example, as seen above. It also has the name of each country (in various forms) and also the estimated population for each country. It has a Geom geometry field that draws the country's shape.  But it does not have the OSM usage for each country.

 

We could use SQL to update the Countries Table to add that usage data for each country, taken from our osm_bandwidth table.  It turns out that it is simpler for beginners to simply create a new table that is the result of a JOIN between the Countries Table and the osm_bandwidth table.

 

We use File - Create - New Query to create a new query that we call Join Query.   It is always a good idea to give new queries a descriptive name that helps remind us what the query does.

 

 

We double-click the query in the Project pane to open it in a Command Window, a window specialized to support fast and easy editing of SQL queries.  Now is a good time to read the Command Window topic if we have not yet done so.

 

 

The query opens in the Command Window with the default text that is placed within any new query.   

 

 

To build the query we want to write, we drag and drop the osm_bandwidth table and the Countries Table into the tables pane of the Command Window.  Doing so makes the names of the tables and the fields they contain available to us to double-click to add to the query text.  That is a lot quicker and less error prone than manually keyboarding the names of tables and fields.

 

 

We write the above query, the SQL text being:

 

SELECT [Countries Table].[mfd_id], [Countries Table].[NAME], [Countries Table].[ISO_A2],

    [Countries Table].[POP_EST], [osm_bandwidth].[Usage], [Countries Table].[Geom]

  INTO [OSM Use Per Capita Table]

  FROM [Countries Table] JOIN [osm_bandwidth]

  ON [Countries Table].[ISO_A2] = [osm_bandwidth].[Country];

 

Understanding the above query:  

 

 

Besides doing the JOIN we want, the above query leaves behind all of the dozens of Natural Earth fields we do not want, building the new table only from those few fields that interest us.  We run the above query and Manifold creates a new table in the project called OSM Use Per Capita Table.

 

 

For the next step, we will double-click that new table to open it.

Normalize by population

As expected, each record in the new table has fields from the two tables that were joined.  

 

 

Each record has the mfd_id, NAME, ISO_A2, POP_EST, and Geom fields from the Countries Table, plus the Usage field from the osm_bandwidth table.   We could create a drawing from that table right now, if our objective was simply to show OSM usage by country.

 

However, we want to show OSM usage by country per unit of population, so we will add a new, computed field that makes that calculation.  

 

If we wanted to write a more complex query, we could create that computed field, the Use_per1K field, within the same query that created the table and performed the JOIN.  We could also create a new drawing in the query as well.  However, for this example, we will do both of those steps manually.

 

With the focus on the open OSM Use Per Capita Table window, we choose Edit - Schema to launch the Schema dialog.

 

 

We press the Add command button and choose Field.

 

 

We enter the name Use_per1K and choose a Type of float64.  We would like to create the new field as a computed field, so we press the Edit Expression button to launch the expression builder dialog.

 

 

In the Expression dialog we enter the expression:

 

([Usage]/[POP_EST])*1000  

 

That computes the use per person, scaled by 1000 for the use per 1000 persons.   We press OK.

 

 

Back in the Field dialog, we see the expression we have entered.  We check our work and press OK.

 

 

The new computed field appears in the schema using provisional, blue background color to indicate changes have not yet been committed to the table.  Reviewing our work, we like what we see so we press Save Changes to apply changes to the table.

 

 

The table is immediately populated with a new column, called Use_per1K, that is filled with the values automatically computed for each record.  The column has a gray color to indicate it is read-only: the values within are automatically computed.  

Create a Drawing and Style it

Back in the Project pane we right-click on the OSM Use Per Capita Table.

 

 

In the context menu we choose Create - New Drawing.

 

 

In the New Drawing dialog we use OSM Use Per Capita as the name of the new drawing.  

 

 We also specify the coordinate system as Latitude / Longitude by clicking the coordinate picker button and choosing Latitude / Longitude from the menu.  We press Create Drawing to create the drawing.

 

 

We drag and drop the new OSM Use Per Capita drawing into the map.  In the illustration above we have double-clicked the Countries tab off before adding the new drawing to the map.

 

The OSM Use Per Capita drawing has been created from the new OSM Use Per Capita Table we created with a JOIN query.   The Geom field, containing object geometry, from which the new drawing is created is exactly the same Geom field we copied over during the JOIN from the Countries Table.   We should expect the new OSM Use Per Capita drawing to look identical to the Countries drawing since the geometry of each corresponding area in both drawings is identically the same geometry.   What is different is that the new drawing has only some of the attribute fields, like name, ISO code and estimated population, from the old drawing, and it also has the OSM usage attribute field for each area as well, courtesy of the JOIN.   It also has the new computed field we just created.

 

We can now color the new OSM Use Per Capita drawing using the Use_per1K field.

 

 

Missing Countries

If we download the same Natural Earth data set and repeat the example, we will discover that besides Somaliland, we will see some obvious missing countries, such as France and Norway.   We can see what is going on by opening the Countries Table, using the Select pane to select all records where the ISO_A2 field is Equal to -99, and then using a Filter to show only selected records.

 

 

The display above shows the original table from Natural Earth.  We have used the Layers pane to hide all the fields except the three columns illustrated.  For some unknown reason, Natural Earth does not report the ISO_A2 code for France, Norway and the other countries seen above.     

 

If a country does not have a matching ISO two-letter code in that field, the JOIN will not match it to the ISO two-letter code in the osm_bandwidth table used in the JOIN.   The result is that record will not appear in the new table created by the JOIN.  No record for France in the table means no geometry for France, and thus no area object for France appears in the map.  

 

 

We can fix that by simply double-clicking into the ISO_A2 cells for France and Norway and entering their ISO codes, FR and NO, respectively.   We are not zealots, so we will not bother tracking down the ISO codes for the other countries, which are too small to have significant visual effect on our final map at the scale in which it will be published.     We delete any prior OSM Use Per Capita Table that has France and Norway missing, re-run the JOIN query using the corrected table that adds ISO codes for France and Norway, and then the new OSM Use Per Capita Table will have those two countries as well.

 

Videos

Join Videos

 

See Also

Tables

 

Drawings

 

Maps

 

Add an Index to a Table

 

Info Pane: Component

 

Layers Pane

 

Style Pane

 

Style: Thematic Formatting

 

Style: Drawings

 

Schema

 

Join

 

Join Examples

 

Command Window

 

CSV, ASC, PSV, TAB

 

SHP, Shapefiles

 

Example: Import Shapefile and Create a Map - Step by step process to import a shapefile and to create a map.

 

Example: Import a Shapefile - ESRI shapefiles are a very popular format for publishing GIS and other spatial data.  Unfortunately, shapefiles often will not specify what projection should be used.  This example shows how to deal with that quickly and easily.

 

Example: Use Edit - Join to Map a Pandemic - Creating a data source for a CSV web server, we acquire current data on cases and deaths worldwide for the Covid-19 pandemic.  We then use the point and click Edit - Join dialog to rapidly join that data, automatically aggregated as we desire, into a world map for visualization.

 

Example: Find Percentages of Open Space in ZIP Code Areas - Given a drawing showing ZIP codes as areas (polygons) and another drawing showing open spaces like parks and nature preserves, we add a field to each ZIP code area that gives the percentage of open space in that ZIP code area.  The workflow we show handles situations where some open space regions overlap multiple ZIP code areas, correctly reckoning only that part of the open space within each ZIP code area.

 

SQL Example: Extract Airport Runways from an OpenStreetMap PBF - We write a simple SQL query using INNER JOIN to extract runway lines from an OpenStreetMap PBF of Cyprus, and to save those lines to a new drawing and table.

 

Example: Style Pane Quickstart - A tutorial introduction to using the Style pane to apply color, symbology, size and rotation to areas, lines and points in drawings.

 

Example: Format a Drawing using the Style Pane - In this example we provide a first, step by step look at how to format areas in a drawing using the Style pane.  We can specify the same formatting for all areas or use a field to automatically set formatting, a process usually known as thematic formatting.

 

Example: Format the Size of City Points by Population - A common GIS task is to format the size of points in a drawing based on some value.  For example, the size of points that represent cities might be formatted based on the value of the city's population, with cities that have larger populations being marked by larger point icons.  This is an example of thematic formatting and is easy to do using the Style pane.

 

Example: Add, Delete and Edit Thematic Formatting Intervals - This topic provides a step by step example of adding, deleting and editing intervals in the Style pane that are used for thematic formatting.