How To Create A Thematic Map In Mapinfo

This tutorial by Nick Williams, a MapInfo trainer at Acuity Training, aims to provide an example of how data analysis and cleansing using Excel can be combined with presentation and mapping using MapInfo.

The example will be to calculate the percentage increase in the average property price per district/borough in Surrey and then to produce a thematic map where the boroughs are coloured according to the average price increase.

The project can be broken down into the following steps:-

Data cleansing using Excel
Calculate the average prices using Excel
Calculate the percentage increase per district using Excel
Import the data into MapInfo
Join the tables in MapInfo
Produce a thematic map using MapInfo

The price pad data can be downloaded from https://www.gov.uk/guidance/about-the-price-paid-data#page-navigation

The price paid information is released in the form of price paid data under the terms of Open Government Licence

The district and borough boundaries can be downloaded from Ordnance Survey's open data page as part of the Boundary Line file. This data is used under the terms and conditions of Ordnance Survey Open Data.

The purpose of this exercise is to provide a tutorial using freely available data and is not intended to express any opinion on the subject.

1. Data Cleansing

The price paid file is published as a csv (Comma Separated Value) file. Csv files are a means of exchanging data between different programs. The data is arranged into columns and each column is separated by a comma. They can be opened in Excel or other spreadsheet programs e.g. Open Office Calc as well as GIS software such as MapInfo or ArcMap.

The data is published in columns in the order set out below:

Data Item	Explanation (where appropriate)
Transaction unique identifier	A reference number which is generated automatically recording each published sale. The number is unique and will change each time a sale is recorded.
Price	Sale price stated on the transfer deed.
Date of Transfer	Date when the sale was completed, as stated on the transfer deed.
Postcode
Property Type	D = Detached, S = Semi-Detached, T = Terraced, F = Flats/Maisonettes
Old/New	Y = a newly built property, N = an established residential building
Duration	Relates to the tenure: F = Freehold, L-Leasehold etc.
PAON	Primary Addressable Object Name. If there is a sub-building for example the building is divided into flats, see Secondary Addressable Object Name (SAON).
SAON	Secondary Addressable Object Name. If there is a sub-building, for example the building is divided into flats, there will be a SAON.
Street
Locality
Town/City
District
County
Record Status – monthly file only	Indicates additions, changes and deletions to the records.(please see guide below).

Open the file from within Excel. If the file isn't visible then use the pull down list with the file type to either show all files or Text or cvs files.

excel-tutorial1

The file covers the entire country which makes it very large and this exercise is only dealing with the data for Surrey.

To extract the data for Surrey

Add a new row for row 1 and update it with the column headings shown above.
Use the Filter function to select transactions for Surrey only
1. Press Sort and Filter
2. Arrows will appear at the top of each column
3. Pull down the arrow for the County column
4. Untick the box next to Select All and tick the box next to Surrey

excel-tutorial2

Now only the data for Surrey is displayed. Copy and paste these rows into a new spreadsheet. I created a new Excel file called Surrey House Prices with separate tabs for 2004 and 2014.

2. Calculate the average house prices for each borough

Pivot tables are a quick and easy way to summarise data.

It may be easier to select the columns first due to the size of the spreadsheet.

Select the Insert tab on the ribbon
Press the Pivot table button
Select the data (if it isn't already selected)
Select the location for the pivot table (it may be easier to add it to a new sheet)
In the Pivot Table Fields box
1. Select Borough and Price fields to display.
2. Drag the Borough field to the Rows box so the data is grouped on Borough
3. Drag Price to the Values field, now pull the arrow down to select Average. The average price for each borough is now displayed

3. Calculate the percentage increase per borough

Copy and paste the Borough Name and average price to a new sheet. I've called the sheet Difference.
Name the average price column 2004 for clarity purposes
Copy the 2014 average prices into a new column titled 2014.
Create a new column titled Difference %.
Use the following formulae to calculate the percentage increase

=(B2/C2)*100

excel-tutorial3

Where B2 is the 2004 price and C2 is the 2014 price

Save this sheet as Difference.csv file so it can be imported into MapInfo

4. Import the data into MapInfo

MapInfo will be used to create a thematic map that colours each district by the percentage increase in property prices.

Both tables will be added to MapInfo then joined on the district name. This must be spelt identically in both the district polygon file and the csv file. E.g. Guildford District must be entered as either Guildford District or Guildford in both. It can't be spelt as Guildford in one file but Guildford district in the other if the join is to work.

Open the table district_borough_unitary_region.shp by selecting File and open. Change the file type pull down to ESRI shapefile (shp). MapInfo will create a tab file, it is a good idea to place this in the same location. MapInfo will ask to confirm the projection. Leave this as British National Grid, which is the most common projection for UK GIS projects.

mapinfo1

The file will open in a map window as it contains geometry, in this case the district outline polygons.

Open the file created during the above steps (Difference.csv). Change the file type to Comma Separated Value (csv).

mapinfo2

MapInfo will now prompt for some information on opening the csv file.
The data contains column headings, so tick the box Use First Line For Column Titles
This file opens in a browser window as it doesn't contain any geometry.

Copy the percentage increase between the tables

The next step is to add the Percentage Difference in house price information to the Boundary Line data so the thematic map can be created.

It is good practice to make a copy of any data set that is going to be modified. In this case, only the district polygons that are within Surrey are needed so select those polygons and save them as a new file.

To view the data for the district_borough_unitary_region.shp file, select Window>New Browser Window.
Select the file district_borough_unitary_region.shp from the list of available files to browse.

The column File Name contains details on which county the district is in:-

mapinfo4

Therefore, it will be easy to create a query to select the districts that are within Surrey.

Select Query>Select to open the Select dialog box
Press Assist to open the Expression Builder
Pull down Column and select File Name
Pull down Operator and select =
Click in the expression box and type "Surrey_County" so the expression reads File_name = "SURREY_COUNTY"
Press OK on the Expression box, the expression is now copied to the Select box
Press OK on the Select box to run the expression

mapinfo5

The districts within Surrey are now selected.

mapinfo6

Save this as a new table to eliminate all the superflous data.

Select File>Save Copy As
Select Query 1 as the table to save and Press Save As.
Name the table something Sensible e.g. Surrey_Differences

mapinfo7

5. Join the Tables

An additional column needs to be added to contain the Percentage Difference in house prices that will be copied from the Difference.csv file.

Open the table created in the above steps (Surrey_Differences.tab)
Select Table>Maintenance>Table Structure and select Surrey Differences as the table to modify.
The Modify Table Structure Window Opens. This lists the table's fields and their type.
Press Add Field, a new field appears at the bottom of the list. Name this Increase. Change the type so it is the same type of field as the Increase field in the Difference.csv file. In this case it should be a Small Integer
Press Ok to close the dialog and update the table.

mapinfo8a

Updating the column

The newly created column can now be updated with the data from the Difference.csv file.

The Update column function is opened by selecting Table>Update Column. Enter the following values:-

Table to Update: Surrey_Differences
Column to Update: Percentage_increase
Get Value From "Differences"
Calculate Value of Expression "Increase"

Press the Join button to instruct MapInfo how to join the tables:-

Join Where Name from Surrey_Differences matches District from table Difference

The Increase column is now updated with the percentage increase from the Difference file.

mapinfo9

6. Create the Thematic Map

The table Surrey_Differences should now contain just the districts that are within Surrey with a column titled Increase that contains the percentage increase in house prices that was copied from the Difference.csv file.

Select Window>New Map Window

MapInfo will prompt for which layers to include in the new map window if there is more than one mappable table open. Select the table Surrey_Differences and press the > button to add it to the list of tables.

The polygons are currently styled with the default white fill and black outline.

Select Map>Create Thematic Map

MapInfo will now prompt through the three steps to creating a thematic map:-

Step 1

In the first dialog, choose the template. This must be a region in this example as the source table contains polygons.

Step 2

Select the table and field that the thematic will use. In this case the table is Surrey Differences and the Increase field will be used to create the thematic.

thematic-map2

Step 3

MapInfo will select default ranges. In this case they aren't suitable. A look at the data will show that having 3 ranges for 60-65%, 65-69% and 70-74% increase is more suitable.,

Press the Ranges button to open the Customise Ranges dialog
Change the number of ranges to 3 and the method (of calculation) to Equal Ranges
Press OK to close the Customise Ranges dialog.

The proposed ranges are now copied to the Create Thematic Map box. Check the colours are suitable (e.g. the highest increase should be red, not green) and press OK to create the thematic map.

thematic-map3

Press OK and the thematic map will be applied

thematic-map4

Creating and Editing Features with MapInfo
MapInfo Resources
Latest Release of MapInfo Focuses on an Easier to Use Mapping Experience