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 seconds.  It also shows how we can use Manifold facilities to get around the inconveniences of real world data.

 

eg_osm_use01_00.png

 

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.

 

eg_osm_use01_01.png

 

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

 

eg_osm_use01_02.png

 

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.

 

eg_osm_use01_03.png

 

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

 

eg_osm_use01_04.png

 

...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.

 

eg_osm_use01_05.png

 

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

Import data

We launch Manifold.

 

eg_osm_use01_06.png

 

We choose File - Import.

 

eg_osm_use01_07.png

 

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.

 

eg_osm_use01_08.png

 

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.

 

eg_osm_use01_09.png

 

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.

 

eg_osm_use01_10.png

 

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

 

eg_osm_use01_11.png

 

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

 

eg_osm_use01_12.png

 

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.

 

eg_osm_use01_13.png

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.

 

eg_osm_use01_14.png

 

Press the Add Identity command button.

 

eg_osm_use01_15.png

 

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.

 

eg_osm_use01_16.png

 

That is better.   The table now has white background to indicate it is read/write, and the column names make sense.  

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 launch the Transform pane, pointing it at the Country field.   In the illustrations below, we show the Transform pane on the left and what it does to the table in an adjacent illustration.

 

eg_osm_use01_17.pngeg_osm_use01_18.png

 

We choose the Replace Regexp, All transform template.   A preview of what it will do appears in the table in blue preview color.    The template finds text that matches the regular expression pattern given in the Pattern 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.    To get us started, the template loads by default searching for the pattern of the letters abc in that sequence and replaces them with the letters def.   There are no such three letter, abc, sequences in the Country field, so the preview shows it unchanged.

 

eg_osm_use01_19.pngeg_osm_use01_20.png

 

In the Patter 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.    We haven't yet changed the def default contents of the Replace with box, so the preview shows the numbers before each country code being replaced with def.     That is a handy way to alert us what the regular expression has matched.

 

eg_osm_use01_21.pngeg_osm_use01_22.png

 

We click into the Replace with box and delete the three characters there.  It is now empty.  The template thus replaces 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 spectacularly useful way not only to learn how to use regular expressions, but also are tremendously helpful in avoiding errors.  

 

eg_osm_use01_23.pngeg_osm_use01_24.png

 

We like what we see in the preview, so we press the Update Field button to apply the transform 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.

 

eg_osm_use01_25.png

 

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.

 

eg_osm_use01_26.png

 

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.   Release 9 takes a database approach to component names, so renaming is tedious.  Future editions of 9 will improve this to a much easier rename capability.

 

eg_osm_use01_27.png

 

We slow double-click into the name of the table and change it to Countries Table.  Before we press Enter to finish the renaming, we Ctrl-C to Copy the table name, so we can use that a few steps down without keyboarding it again.

 

eg_osm_use01_28.png

 

Next, we slow double-click into the name of the image and change it to Countries.

 

eg_osm_use01_29.png

 

We right-click onto the newly-renamed image and choose Properties.

 

eg_osm_use01_30.png

 

We must update the name of the Table from which the image takes data, since we have renamed the table.  We double-click into the name.

 

eg_osm_use01_31.png

 

Within the square [ ] brackets, we Ctrl-V to Paste the name of the table we previously copied. Press OK.

 

eg_osm_use01_32.png

 

The drawing and table are now renamed to short names.   In the illustration above, we have used File - Create - New Map to create a new map, and we have used File - Create - New Favorite Data Source - Bing Maps Street Map to create a Bing streets image server layer, as discussed in the Example: An Imageserver Tutorial  topic.

 

eg_osm_use01_33.png

 

We open the map and then drag and drop the Bing imageserver layer into it, as discussed in the Example: An Imageserver Tutorial  topic.

 

eg_osm_use01_34.png

 

Next, we drag and drop the Countries drawing into the map.  Strictly speaking, we do not need to do this since we will be creating a new drawing from the Countries drawing.  But dropping the Countries drawing into the map 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 that it is correctly georegistered and that it has been imported with the correct coordinate system / projection in use.  

JOIN usage data with countries

The illustration below shows some of the many fields in the Countries Table.  

 

eg_osm_use01_35.png

 

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.

 

Before continuing, please read the JOIN Statements topic.

 

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.

 

eg_osm_use01_36.png

 

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.

 

eg_osm_use01_37.png

 

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

 

eg_osm_use01_38.png

 

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.

 

eg_osm_use01_39.png

 

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];

 

To unpack the above query:  

 

 

We run the above query and Manifold creates a new table in the project called OSM Use Per Capita Table.

 

eg_osm_use01_40.png

 

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.  

 

eg_osm_use01_41.png

 

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.  We choose Edit - Schema to launch the Schema dialog.

 

eg_osm_use01_42.png

 

btn_schema_add.png We press the Add command button and choose Field.

 

eg_osm_use01_42a.png

 

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.

 

eg_osm_use01_42b.png

 

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.

 

eg_osm_use01_42c.png

 

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

 

eg_osm_use01_43.png

 

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.

 

eg_osm_use01_44.png

 

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.

 

eg_osm_use01_45.png

 

In the context menu we choose Create - New Drawing.

 

eg_osm_use01_46.png

 

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

 

btn_coord_sys_picker.png  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.

 

eg_osm_use01_47.png

 

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.

 

Our next, and final, step is to thematically color our new drawing using the Use_per1K computed field we created.  We launch the Style pane.

 

eg_osm_use01_48.png

 

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.

 

eg_osm_use01_49.png

 

We choose the Use_per1K field.  The default intervals and choices for thematic formatting appear.

 

eg_osm_use01_50.png

 

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 9 breaks, press Tally and then edit the numbers in the intervals (starting with the largest and working our way down) and finally applying the Color Brewer CB Spectral palette.   

 

We press Update Style to apply the thematic format.

 

eg_osm_use01_51.png

 

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.  In the image above, we have turned the Countries layer back on, so the light gray of that layer shows through in places where there are countries missing in the OSM Use Per Capita layer, such as Somaliland by the Horn of Africa.

Notes

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.

 

eg_osm_use01_52.png

 

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.  

 

eg_osm_use01_53.png

 

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.

 

See Also

Tables

 

Drawings

 

Maps

 

Add an Index to a Table

 

Component Pane

 

Layers Pane

 

Style Pane

 

Style: Thematic Formatting

 

Style: Drawings

 

Schema

 

Command Window

 

JOIN Statements

 

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: 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.