Hands On: Link your database to a map

Vic Paine has an interesting proposition: he has Ordnance Survey grid references of the locations where objects have been found using a metal detector. The objects are classified into coins, artefacts and so on.

Vic would like to see the grid references as points on a map. What a great project, and yes, happily, it’s perfectly possible to do using everyone’s favourite database, Access, and a mapping package.

To demonstrate this I’m using the 2004 version of Microsoft’s Map Point Europe, which isn’t the most
up-to-date, but it provides a good illustration of how to proceed.

Even if you use a different package, you’ll probably have to follow the same sorts of step. (I later tried out Map Point 2006 and it behaves just like 2004).

Please do NOT start digging for treasure at these locations; I know absolutely nothing about buried treasure, I chose them at random.

OS references take the form of two letters, followed by two blocks of three numbers, SO 432 933 for instance. The two letters identify the 100,000 metre square of the country (ie each OS sheet ­ SO, for example, is Sheet 137, Church Stretton and Ludlow).

Each OS map is divided into 1km squares by numbered blue grid lines: the first two numbers (43) in the reference indicate the vertical grid to the left of your point and the third (2) is the number of tenths to the right of that grid line your point lies.

These three numbers (432) comprise what’s known as the ‘easting’. The next three numbers (933) are the ‘northing’ and start with the number of the blue gridline below your point and the number of 10ths the point lies above it. A compete reference identifies a 10m square on the ground.

Imagine a table of data like screen 1. Fire up Map Point, click Data, Import Data Wizard, choose the Access .MDB file type and navigate to the file and Open it. (The sample database is DBCMay08.MDB to be found on the website and the DVD).

The wizard presents a list of tables: choose the one containing OS references (called Metal in the sample) and click Next. Check the Country/Region is set to United Kingdom. A list of the fields in the table is shown: pop down the Data type list at the top of the column that contains the OS references and set it to ‘OS Grid Reference’, then click Finish. You are offered a range of map types.

Simplest is the Pushpin option, which puts a pushpin in the map for each data point. Select this, click Next, and then you can choose the look of the pushpins (different shaped and coloured pins, flags and a whole range of symbols are available).

You also choose what is displayed in the information ‘balloons’ for each pin. Deselect OSRef (otherwise it The picture above shows how each pin looks if you double-click it. The pushpin set is shown in Map Point’s Legend and Overview panel to the left and is called Metal (the name of the table that provided the data).

We could stop here, with our OS points shown on a map, but there are a few more features that could help Vic. One is that Map Point can also show data colour/symbol coded by content. With pushpins in the map as described above, click Data again on Map Point’s menu bar and select Data Mapping Wizard. Choose Multiple Symbol, click Next and select the default ‘Edit an existing data map’ option.

Any pushpin sets are listed below: there’s only one so you can just click Next. The pushpin balloons are already showing the age of the find, so here we’ll display the find itself: select Find as the data to map, accept the ‘Show the data by OS Grid Reference’ default and click Next. Now you can play with the symbols for each find.