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.

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.

Using Those Grid Cell Values In A Crosstab Query

These coordinates are then used in Crosstab queries to consolidate the data by the two grid dimensions as computed in the fields EastGrid and NorthGrid.  The breakthrough I had with this technology was when I was demonstrated a system similar to this in an Lotus spreadsheet where the mining data was displayed as average data in each of the cells.  When I saw this example, I immediately twigged that I could do the same thing with a crosstab query.  This crosstab basically makes a uniform grid in which the cells in the grid are filled with the results of the average of all values that lie in that cell.

TRANSFORM Avg(Iron) AS AvgOfIron
SELECT NorthGrid FROM qryGridValues
GROUP BY NorthGrid
ORDER BY NorthGrid DESC
PIVOT qryGridValues.EastGrid;

And to illustrate the 100 x 100 grid that this query will generate, see figure 4.  Note that the values being displayed are the average values of iron that occurs in the grid cell.   The other thing that you should note is the spatial pattern of where the data occurs matches that which is shown in figure 1.   This sort of visual confirmation is very useful when you are making maps as just as many things can go wrong with mapping software as any other software.

Figure 4   A crosstab query in action that displays data in a spatial format.

What Can You Display In A Grid.

A crosstab query is just a very fancy consolidation query and all the consolidation functions such as Count, Sum, Avg, Max, Min, Standard Deviation and Variance can be used in the crosstab query.   This means that the crosstab grid can display all these functions which makes them very useful as a spatial analysis tool.

Making A True Grid Query

To display a true grid, we need every cell  to be displayed whether it has data or not.  The crosstab query that I have demonstrated sometimes does not have any information in a row or column.  This means that the grid will miss that row or column in its display which is not something that you want in a spatial display.  I fix this by generating dummy cells prior to running the query by using a special of SQL feature called the Cartesian Product.

When you perform a multi-table query that does not explicitly state a join condition among the tables, you create a Cartesian product.   A Cartesian product consists of every possible combination of rows from the tables.  In other words if you have 50 rows in one table and 10 in another you will have an answer with 500 rows.  The only time you are ever likely to use this feature of queries is when you forget to add the join to a query.  That is until now !!!

To make a query that returns every cell in a spatial grid, I must first create a true grid.  I do this by making a table with all the required cells in the X (East) axis direction and similarly a separate table for the Y Axis.   The subroutine that does this involves an iterative loop that builds rows of axes values  in a new table. 

Public Sub makeGrid_FX(tableName As String, cSize As Double, cMin As Double, cMax As Double)

' Subroutine to generate the rows for a grid

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

End sub

Now if I want to make a square grid of 1000 units with a cell size of 100 in both the X and Y directions, I call this subroutine twice as follows 

  Call makeGrid_FX("xAxis", 100, 0, 1000)

  Call makeGrid_FX("yAxis", 100, 0, 1000)

Now we have 2 tables of  11 rows each that form the basis of the first legitimate use of the Cartesian product query (no joins) that I have ever come across

SELECT xAxis.coord AS xVal, yAxis.coord AS yVal
FROM xAxis, yAxis 
ORDER BY xAxis.coord, yAxis.coord;

This query with no joins returns 121 rows which are all the cells that we need in a crosstab query for a grid. 

Joining The True Grid Query To The Spatial Information

I now round off my query contortions by doing 2 outer joins to the original grid cells query to obtain the average iron values that I wish to display in each of the cells.  This query is show in figure 5 and the results in figure 6

Figure 5  Crosstab table design to show all cells in the spatial grid

Displaying The Results (Using Contours)

Now we are ready to display this information and normally I display the results using crosstab queries.  To round this article off, I have used another one of the little used Access graphs called a contour graph to display the true grid results.  These contours can be displayed in both 2 and 3 dimensions but actually works better using a conventional two dimensional view than a 3D contour view. 

Figure 6 - Show the true grid results using a Microsoft Graph contour plot.

To add the query results from figure 5 to this graph, there was the usual tricks to come up with the right result.  Firstly I just used any old form with a graph object in it.  Using the graph wizard is useless in this case.  Now change the graph type to one of the 2D surface plots.   Finally make the query behind the form use the results of the true grid query as follows 

SELECT xTabTrueGridResults.* FROM xTabTrueGridResults;

There was one other really difficult to find property that need to be changed and that was to show the Y Axis series in reverse order.  You can find this property by right clicking on the Y Axis and choosing the Scale tab.   Once again, import the Contour plot from the demonstration database and use that one.

Extensions To The Technology.

Where this approach to view spatial information became really important in my work was when I started applying all the great filtering techniques that Access has to offer to come up with a really flexible result.   That way I handled this was to setup the grids for a particular project and then apply the filters to the grids using an  SQL builder for the project.  On those projects that I was working on, my partner was a senior geologist who was very good at statistics and really knew how to program a spreadsheet.  In the end I then produced these grids and copied them over to Excel where he would compare results of different grids by stacking them on top of each other.  His spreadsheet solutions were so complex, I could only but marvel at the results that he came up with.

Conclusion

Spatial analysis has been going on in computers using mapping systems for nearly 20 years now.  The big thrust that is on at the moment is to somehow make mapping a mainstream computing tool.   Evidence of this can be seen with the Microsoft Mapping addition to Excel and the giving away of MapInfo with the more popular Crystal Reporting package.  Whilst these are making inroads, they are in essence another skill set that you have to learn if you want to make it a success. 

This article in no way sets out to be the mapping answer for Access users. Its aim is to push the boundaries of Access as both an analysis tool and allows you to see if there are any trends in your information that are related to location.   Also I am sure that the tricks that I have detailed for classifying numbers into groups and using the Cartesian product to ensure rows and columns in crosstab queries are displayed will also assist you in your applications.  The technology certainly helped me in a number of important mining analysis projects.

Author Bio.
Garry Robinson is the founder of GR-FX Pty Limited, a company based in Sydney, Australia. If you want to keep up to date with the his latest postings on Access Issues, visit his companies web site at http://www.gr-fx.com/ or sign up for his Access email newsletter here. Contact details at Garrys website here

I would also like to thank Scott McManus  www.scandus.com  for his input into MapInfo and Ian Levy from Gympie Gold www.gympiegold.com.au  who provided the original inspiration to explore this technology.

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