In this tutorial, Nick Williams, a trainer at Acuity Training, details the steps necessary to perform crime data analysis using MapInfo and data from Guildford, UK. Included are details on:
- Importing data from spreadsheets
- Importing data in other GIS formats
- Joining data based on spatial relationships
- Creating buffers
- Counting the number of points within a buffer or polygon
Introduction
These tutorials will count the number of crime incidents for March 2014 within set distances of the Guildford, UK town center and also group incidents by ward boundaries. I have used the example of crime incidents and ward boundaries because the datasets are openly available. I have used similar techniques in the water industry to map complaints and assign them to distribution zones. Alternatively you might use a similar technique to assign customers to store catchment areas.
The following examples use freely downloadable data:
Please note that these are purely examples that I have created for this tutorial. They are not intended to give any opinions on crime levels or policing.
Importing data to MapInfo
Import the crime data into MapInfo and map the points
The crime incidents spreadsheet contains longitude and latitude coordinates. MapInfo can use these to create points.
MapInfo can import .csv and Excel files. A .csv file is a Comma Separated Values file. The data from columns are separated by columns. This format is commonly used to exchange data between different programs.
The only option for preferred view will be a browser window as this table is currently text-only and doesn’t contain any geometry.
The next screen contains some options for importing .csv files. The first row does contain column titles, so I have ticked that option.
Import the ward boundaries and convert from a different GIS format
The ward boundaries are supplied in the .shp (shapefile) format that is used by Esri’s ArcGIS. I will use MapInfo’s FME Quick Translator to convert them to MapInfo’s .tab format.
FME Translator was previously known as Universal Translator. It can be opened using the following menu commands:
Tools > Universal Translator.
Adding MapInfo .tab files to a map
Now I will add the .tab file that I have just created. This table has geometry (it contains polygons) so it can be displayed in a map window. MapInfo gives users the choice of adding a layer to an existing map window or opening it in a new map or browser window. This is a time-saving short-cut as it saves creating a new map window then adding data to it.
A new map window is opened for the ward boundary polygons:
The previous table of crime incidents is currently in data only format. However it contains longitude and latitude information. This is in WGS84 coordinates but MapInfo can overlay these.
The other options are:
- Multiply X/Y co-ordinates. MapInfo uses six-figure grid references for British National Grid. However the data may be supplied with seven-figure references, in which case multiply the coordinates by 0.1. Alternatively multiply by 100 if a four-figure grid reference is used. This feature can also be used for imperial/metric conversions.
- Overwrite existing points. This is useful if a table needs to be re-imported, for example to correct errors or if the data are changed.
It is a good idea to make some checks that the layers are in the correct location, especially as this is new data from different sources and coordinate systems.
- The points and polygons overlay each other.
- Add a Bing background and check that the data are in the correct location.
- Click the map status button in the lower left corner, select map coordinates and check that the coordinates are what you would expect for the location.
The below screen shot shows all the features are in the correct location and overlay correctly.
Add value to the data through spatial analysis
This map clearly shows there is a cluster of reported crimes within the built-up area of Guildford. You can add value to these data by using MapInfo to identify the wards with the highest crime levels, and by quantifying the cluster, for example, by counting the number of crimes within buffer zones at different intervals around the city center.
Here are two different ways to count the distribution of incidents:
- Use SQL select to combine the attribute data from wards and crime incidents
- Create buffer zones at set distances around a selected location
SQL Select
SQL select is powerful command that allows you to link tables by attribute or spatial relationship. It is similar to the Joins command and relates function found in Access and ArcGIS. I have used a similar procedure in the water industry to assign complaints to distribution areas, and in local government to assign proposed developments to school catchment zones.
Create the SQL Select query
This query will create a new points table that contains just the points within the ward boundary polygons. The attribute information will combine the information from both the Crime Incidents table and the Ward Boundaries table. The options are as follows:
- Select Columns – I could specify certain columns from either source table, however I want all the columns from both source tables so I’ll enter a * (wildcard character).
- From Tables – Enter the table whose geography will be used by the new table first. In this example the new table will contain the points from Crime_Incidents_2014_03, so I’ve entered it first. The information from Guildford_Ward_Boundaries will be copied to the new table, so I’ve entered it second.
- Where Condition – Crime_Incidents_2014_03.obj Within Guildford_Ward_Boundaries.obj. This means that the join is a spatial join. If a point from Crime_Incidents_2014_03 is within a polygon in the layer Guildford_Ward_Boundaries, the attributes from both objects will be merged.
- Order By Columns – Selects a sort order for the new table. This is less important now that MapInfo’s browser window allows records to be sorted.
- Into Table Named – This is an optional field. It is possible to specify a name for the table (I named this table (Crime_Incidents_with_Ward_Info), otherwise MapInfo will use Query 1, 2, 3, etc. as a default.
Export the results to Excel
The Crime_Incidents_with_Ward_Info can be exported back to Excel for further analysis. Use the menus to select:
Table > Export to open the Export Table dialog box.
Select the table that you wish to export.
Now select the file location, name and format. MapInfo can export to the following formats:
- MapInfo interchange
- AutoCAD.dxf for exporting geometry to AutoCAD and vector drawing programs
- Delimited ASCII, comma separated values and .dbf for exporting attribute data, commonly to spreadsheet and database software (e.g. MS Excel or Access) or to specialized modeling software
A .dbf file is easily opened in Excel. In the screenshot below, I have hidden a lot of superfluous columns for clarity.
Note the data now have ward name added. This allows analysis based on the ward areas, for example, a pivot table can produce a count of each type of crime in each ward:
Buffer zones
Buffers are polygons that extend for a determined distance around a feature or features. For example, I have used buffers to create catchment zones around town centers, and to estimate pollution risk zones along roads and pipelines.
Create a seed point
The first step is to create a seed point that will be used as the center of the buffer zone. I currently don’t have an appropriate layer or table, so I will create a new one. To create a new table (layer), press the New Table button in the top left of the screen to open the New Table Dialog.
I want to add the new table to the current mapper. The existing tables (layers) contain a lot of fields that won’t be needed for the new table, so I will create a new structure.
The next screen is to enter the new table structure. I only need one field to give the name of the seed point.
It is important to specify the coordinate system to ensure that features appear in the correct location, and overlay correctly. Press Projection and select the projection/coordinate system, which in this case is British National Grid.
Next, specify the name and location for the new table. I have called it Town Centre Points.tab
The new table will be added to the map. If you want to change its style, right click on the layer name in the Layer Control panel and select properties.
I have added the seed point for Guildford town center roughly halfway along the High Street where the famous clock is located:
Create the buffers
Use the menus to select Table > Buffer
The Table Buffer dialog opens; select the layer that contains the objects around which the buffers will be created.
The New Table dialog opens again. I want to add the buffers to the current map. However this time, I will use the existing table Town Centre Points as a template for the new table structure:
The table structure dialog opens. Any fields from the template table are automatically added. I want to add a field to contain the buffer distances as they are created. I also want to count the number of crime incident points so I will add a new integer field to contain that number:
When I press OK, the Buffer Objects dialog opens. I made the following selections:
- The first buffer will be at one-kilometer (km) around the town center.
- I want to create a separate buffer around each town center point.
- I am using British National Grid, which is a Cartesian (the world has been projected onto a flat map) co-ordinate system, as opposed to a coordinate system that is based on the earth being a spheroid (e.g. WGS84).
Press Next to move along to The Data Aggregation dialog; I entered the following:
- Town will contain the value from the Town_Centre_Points town field
- Buffer_Dist – This is a one-km buffer, so I have entered the number 1.
- Crime_Count will be filled in later when I run the count points query.
When I press OK, the first buffer is created:
I now repeat the above steps to create a two-km buffer.
Remove the overlap between the buffers
The problem is that the two-km buffer covers the one-km buffer. When I update the buffer table with a count of the crime incidents, those points that are within one km will be counted twice. They will be counted once as they are within the one-km buffer; secondly as they are within the two-km buffer. I need to delete the portion of the two-km buffer that overlaps the one-km buffer to solve this problem.
To proceed, make the Town_Centre_Buffer layer editable by clicking the Editing Pull Up in the lower left corner of the screen and select Town_Centre_Buffer
I need to set the target which will be the two-km buffer. It is possible to select it just by clicking on it, but this isn’t a precise method of selecting. A select objects query is more precise. It allows easy selection of multiple objects, or when the map contains lots of layers.
Use the menus to select Query > Select
From the Select box, press Assist to open the Expression Builder. This selects features by attribute or geographic position. Fortunately I entered the size of the buffer into the attribute table so I can easily use this information to select a specific buffer. I want to select the two-km polygon from the Town Centre Buffer layer, so I enter the following formula: Buffer_Dist = 2
I don’t need to browse the results; however, I would like them to be displayed in the current map window.
I need to set this selection as the target for the edit, so I use the menus to select Objects > Set Target
Now I need to select the one-km polygon as the cutting edge. I use the Query > Select menu to open the Select dialog. This time, I want to select the one-km polygon, so I enter the formula Buffer_Dist = 1. This selects the one-km buffer, while the two-km buffer remains set as the target.
To erase the overlap, use the menus to select Objects > Erase
The portion of the two-km buffer that overlaps the one-km buffer is now erased.
It is a good idea to pack the Town Centre Buffer table to remove any redundant information. Use the menus to select Table > Maintenance > Pack Table
Count the points within each buffer
The buffers are now complete. I can update them with a count of the crime incident points that lie within each buffer:
Use the menus to select Table > Update Column
- Table to update – the table that contains the buffers (e.g. Town Centre Buffer)
- Column to update – the column that will contain the count of the points
- Get value from table – this will be the points table
- Calculate – count (of the points)
Press Join to check the tables will be joined. Note this can be used for both spatial and attribute data joins. Unlike SQL select, it can only be used to fill in existing fields; it cannot be used to create a new table that contains fields from both source tables.
MapInfo attempts to guess the join and will provide drop-down selections for both a spatial and an attribute data-based join. In this case, I will base the join where the (point) object from Crime_Incidents is within the (polygon) from Town_Centre_Buffer
If you receive a message saying, “Cannot update table …….” try to pack the table to remove any redundant entries in the table structure. Use the menus to select Table > Maintenance > Pack Table and then re-run the query.
If everything works, the Crime_Count field will be updated with a count of the crime incident points within each buffer:
The above screenshot shows that there were 298 incidents within one km of Guildford’s town center, and 129 incidents between one and two km of the town center.
Summary
The above tutorials demonstrated how to:
- Import data from spreadsheets
- Import data in other GIS formats
- Join data based on spatial relationships
- Create buffers
- Count the number of points within a buffer or polygon
As mentioned previously, these tutorials used crime incidents and ward boundaries because they are openly available. Consider how similar techniques could be used to support data analysis in your organization.