Using Microsoft Access To Display Spatial Information

By Garry Robinson                          

Microsoft Access is not known as a tool for displaying spatial or mapping information but with a few special tricks, the crosstab queries and graph product can be used to transform coordinates into useful maps.   In discussing these issues, I will also explain some techniques that can be used for statistics, graphs and even for extending your crosstab technology.

THIS ARTICLE HAS MOVED TO OUR SMART ACCESS KB

But you might immediately be thinking, my databases do not contain any mapping information so why should I be reading this article.  Well the reason is that somewhere in most databases, you do have information that is located somewhere on the surface of the earth and therefore can be analyzed in a spatial way.  If your database has sales results from 100 cities in the USA, you could allocate a coordinate for each of those cities and review sales from those cities into the North, South, East and West zones.  This way you might find that your sales are actually declining in the East and growing in the West.  Another example might be the names and addresses of your customers.  If you can allocate a coordinate for each of those addresses you will now be able to review your customers buying patterns by splitting them into a grid that covers a city.  This way you can find where your latest marketing campaign is working because adjacent suburbs can be analyzed together.   These are reasons why mapping software is being pushed into the general computing arena and hopeful these techniques will encourage you to start analysing your data spatially.

Display Coordinates In An XY Scatter Plot

To get you into mapping, I will show you how to setup a scatter plot and explain how it can be used to display coordinate information.  The chart I use is a scatter (X/Y) plot and this is hard to setup because the queries that you use for scatter plots are generally not the consolidation queries that you use in a normal graph.  

Figure 1 -  A Scatter Plot showing spatial data

So how can you make a spatial plot using a scatter (XY) graph.  The best way is to import the ScatterPlot form from the sample database into your own database and convert the data source to your own data.   Not withstanding that, here are the steps to make a scatter (XY) graph from scratch.

Open the database container and choose the Chart Wizard to create a new form.  Select your table.  Only choose the two coordinate axes as fields for your graph. Choose the XY scatter plot as your required graph. Make sure that the two axes are selected for viewing on the scatter (XY) graph.  Select the Sum consolidation option at this stage.

Complete the wizard and open the form in design mode.  Find the Data Tab on the graph object and look at the data source which will be a consolidation query similar to the following

"SELECT East, Sum(North) AS SumOfNorth FROM MapData GROUP BY East;"

Open the query in design mode and remove the consolidation mode ( Σ button ) from the query and make it into a simple two column query with the following SQL

"SELECT East, North FROM MapData;"

At the bottom of the Tab, it is better if you set the following properties  Enabled = Yes  Locked = No.  Changing these properties ensures that the graph will have your data inside it rather than the North, East, West, South data that comes with all graph objects.    You can reset them when you have completed testing and remember to delete some test data from behind the form as this is carried around with the object and does occupy database space.

Now open the form and double click on it to open up the graph object.   You will note that we still do not have a suitable scatter plot.  Choose the menu option Data and select Series in Columns.  Now all you have to do is to remove the lines between the points by right clicking on a point and setting Lines to None on the Format Data Series tab.    As I said, just import the example that I have provided or even try the scatter (XY) graph with trendline graph form that is also in the sample database.

Making A Frequency Diagram

One method of displaying statistical information is through the use of frequency diagram.  It was only through working on these types of plots that I became aware of the methods that I use for making spatial grids.  A frequency plot is a generally a bar or line graph which shows the number of values that occur within a fixed interval.   Say we were looking at iron ore percentages in an iron ore deposit,  we would like to know how many samples occur in the following ranges    0-10 % 10-20%  20-30% etc.   To break values up into their separate bands, I use the following function

Function Histo_FX

......   This function is available when you purchase The Toolshed

End Function

To display the information broken up into the numerical class intervals, I use the function in the following query as follows

SELECT Histo_FX([salinity],10) AS ClassInterval FROM MapData;

Which returns data as follows

As you can see the numbers are now associated into the class interval that it relates to.  Now we need to display that information in a standard Access column graph using the following query as a data source for the graph shown in figure 2.

SELECT ClassInterval, Count(ClassInterval) AS NumValues
FROM qryClassIntervals GROUP BY ClassInterval;

Figure 2 -  A frequency graph showing the number of values that occur in each class interval.

We now have a nice neat graph that shows us that most of the iron values are concentrated in the 10 and 20 % bands.  The shape of this graph also indicates that we have a lognormal distribution but that is another story that can well pass onto those with statistically bent minds.

Allocating Spatial Information To A Grid Cell

All spatial information can be consolidated by allocating XY coordinates to the grid cell in which it exists.  To do this I use the technique described in the frequency diagram on both the Northing and Easting fields in a data set.  This makes 2 new fields with standard grid coordinates.    In the following query, I am going to break the coordinates into a 100 * 100 grid as follows

SELECT East, Histo_FX([East],100) AS EastGrid,  

This gives me coordinate information where all the raw coordinates are transformed into grid values (class intervals) as shown in the following table.  

Figure 3   - Computed grid values that will be used in a crosstab query.

Click on the following button to jump to the next page in the document loop.

Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals