Hands On: Link your database to a map

For each OS reference there is now a coloured symbol indicating the type of artefact (a legend is shown to the left), with the age of the find displayed in a balloon.

This demonstration shows that it can be straightforward to export points from a database and display them on a map.

Each implementation will vary, of course, depending on the software you use and your particular data. For instance you may have more than one find per grid reference.

I had a brief play with two tables with a one-to-many relationship and wrote a query to supply Map Point with the references and find/age data.

My old Map Point made no demur at multiple finds at one point but the map display wasn’t so good, as it’s not easy to see where multiple finds occur since symbols stack one on top of another. (The tables and query are in the sample so you can experiment too).

Check that a mapping package will do what you want before you buy: Microsoft’s Virtual Earth might also be worth a look and/or Google maps.

Incidentally, Map Point will accept references stored with spaces or without ­ ‘SO 432 933’ or ‘SO432 933’ ­ or even a mix of both in the same column. It will also import locations stored as latitude and longitude, though these must be held in separate fields.

Whether they are west or east of the meridian, and north or south of the equator, should be indicated by a letter. For instance, latitude entries can be in the form 49.46485N or 49.27.53S, and longitude entries as 2.54236W or 2.32.32E.

Mapping data is a truly fascinating area, destined to become even more so with the arrival of spatial data types in the forthcoming version of SQL Server 2008. These will allow the mapping of GPS-type data onto a curved Earth ­ important if you want very accurate calculation of distance between points ­ as well as mapping onto flat maps.

Functions will be provided that ‘understand’ these locations and can calculate, for instance, the distance between points or where two objects intersect. It’s an area that interests me and, if anyone out there wants more on the topic, let me know.

Two tables better than one
John Holloway asked about his database to store CDs and the tracks thereon: in his current database he wants to list the tracks in one field in a vertical column and is finding he can only list them horizontally.

My experience is that any solution which places many pieces of information into a single field is almost guaranteed to end up causing grief and aggravation. This is true whether you elect to store multiple items in a text field or in one of the new ‘multi-valued fields’ in Access 2007.

As I wrote in October 2006: “It is difficult to stress the problems caused by multi-valued fields too strongly: the relational model is predicated on the notion that each field can only contain a single value.”

Putting values into such fields is easy, it’s getting it out again that causes so many headaches: querying becomes much more difficult, much less intuitive and very different from querying single-valued fields. My strong recommendation, as you may have guessed, is not to use them. At all. Ever. But I hope I don’t sound dogmatic at all here…

So what should John do? My advice would be to store the album or CD data in one table and the track information in another, with a one-to-many join between each CD and its constituent tracks. The two tables, CDs and Tracks, and the relationship between them are in the sample MDB. A simple form (auto-generated) displays the tracks as required; or you can use a report.

It’s a little more effort but in the long term this structure should make any future querying a great deal easier ­ and you’ll also enjoy the warm glow from knowing your database adheres to relational database theory.

On the other hand I always try to answer the question as well as proffering gratuitous advice and John may already be aware of these arguments and have good reason to use a single field. The answer is to use a Text or a Memo field. You can force a new line for each track with Ctrl and Return.

Once you’ve typed a title and pressed Ctrl and Return, the title vanishes because the cell is only one line deep. Make it wider by placing the mouse over the line indicating the bottom of the row in the left hand grey border to the table and dragging downwards.