Teaching Object Oriented Geographic 
Information Systems (GIS) using Visual Basic:
Spreadsheet Approach

Emil Boasson
eboasson@ithaca.edu
Ithaca College School of Business
Ithaca, NY 14850

Abstract

This paper introduces an alternative approach to teaching Geographic Information Systems (GIS) in a business school context where most students have little background knowledge on GIS, geo-statistics, and cartography.  This approach involves the use of ordinary spreadsheets along with Visual Basic programming to create a home-grown GIS program that has the features of a dynamic mapping with time-varying animations - a problem still unresolved in mainstream commercial GIS.  While avoiding the frustrations of struggling through all the technicalities of mainstream GIS, this alternative approach to teaching GIS allows students to use regular spreadsheets as a tool to explore ideas for novel interfaces, to ask “what if” questions, and to visualize problems as well as their outcomes, thus gaining a clearer understanding of GIScience theories and GIS operations.  This approach has been successful in teaching “GIS for Business” for MBA as well as undergraduate business students.  

Keywords: geographic information systems GIS, GIS for Business, Visual Basic, spreadsheet mapping.


1.   INTRODUCTION
Geographic Information systems (GIS) can be broadly defined as information mapping systems that are capable of transforming a spatial database from a tabular form into a map and graphically conveying spatial business intelligence.  Traditionally, business reports and academic research findings are most often conveyed in words, later in tables, and only occasionally in maps.  However, people seem to gain a better understanding of the spatial information from a map than from words or from complicated matrixes of numbers.  GIS have thus become a popular tool today in decision-making among many private business enterprises, government institutes, and planning offices.  GIS are used extensively in real-world businesses to visualize, analyze and present information for budgeting, planning and decision-making (Boasson, Boasson, and Tastle 2004).  GIS are, for instance, used as a part of decision support systems (Guariso and Werthner, 1989), for facility or site location (Hotelling, 1929; Lösch, 1954; Love, Morris and Wesolowsky, 1988), and for site location study based on the Weber Problem (Weber, 1909; Wesolowsky, 1993).
However, it could be a major challenge for teaching GIS in a business school context where most students have little or no technical or background knowledge on GIS, geo-statistics, cartography, and mapping.  In particular, in an introductory GIS course taught in a business school setting, it may not be feasible or practical to teach students to use a full-blown commercial GIS software package.  Students may simply be overwhelmed and even frustrated while struggling with the technicality and complexity of a large-scale GIS software package.  At the same time students may simply lose sight of the very basics of GIScience.  Moreover, due to the relatively high cost of a large-scale commercial GIS software package, many business schools are simply reluctant to invest in their own GIS software.
Thus, the purpose of this paper is to introduce an alternative way of teaching GIS in a business school context so as to overcome those barriers due lack of technical and geospatial training, and to teach business school students the fundamentals of GIS without frustrating them by the complexity of a full-blown GIS software package.  This approach allows an instructor to create a simple GIS system in-house that incorporates technologically advanced and sophisticated features of animation which is still a problem unresolved in commercial GIS packages.  This approach involves an innovative and creative use of an ordinary spreadsheet such as Microsoft Excel along with Visual Basic commands.
The remainder of this paper is organized as follows: Section 2 describes the research context; Section 3 introduces an alternative GIS program; Sections 4 illustrates step by step procedures for creating an in-house GIS system; Section 5 describes the steps for finalizing the GIS map; Section 6 extends the current mainstream GIS capabilities; Section 7 concludes the paper. 
2.   RESEARCH CONTEXT
The Arc family from ESRI and MapInfo from MapInfo represent the mainstream GIS software packages in the market today.  These are highly sophisticated and large-scale GIS software packages.  While these mainstream commercial GIS software packages such as ArcMap and MapInfo have many sophisticated features and are capable of utilizing advanced database management, statistical applications, merging data onto spatial or geographical environment, and allowing the user to create highly sophisticated maps for conveying business information, most of these commercial GIS software packages require high-speed computers with large memory allocations.  More importantly, learning to use these large-scale GIS software packages could be very time-consuming and require a lot of practices as the mapmaking skills require more of an art than of science.  Those who have to make maps based on tabular information know how time-consuming a process is just to make a readable map.  On the other hand, if one can fully master mapmaking skills, one will be able to create a high quality sophisticated map that could capture thousands of words.  However, business practitioners, academic researchers, and students have encountered various problems with these mainstream GIS programs.
For business decision-making purposes, those business practitioners who are laymen to GIS often find mainstream commercial GIS are typically too laborious and time consuming for their day-to-day purposes of decision-making, data analysis, and report presentations.  In many instances, business professionals merely want to display spatial information such as population censuses, store locations, customer locations in the most straightforward way on relatively unsophisticated maps.  As Innes and Simpson (1993) put it, GIS, like the astrolabe, is a more complex tool than is needed for many purposes.  Another practical problem with GIS is that many business and government organizations whose main missions are not in GIS business may be reluctant to employ a full-time GIS specialist, or a person specialized in a particular GIS program (Cole 1995, 1998).
For academic research purposes, non GIS specialty researchers have a deep-learning curve in learning to use mainstream GIS programs.  Even for GIS specialty researchers, there are also several well-known difficulties with mainstream commercial GIS.  Goodchild (1996) listed interoperability, transaction matrices, moving objects, and real-time animation as among major problems areas for proprietary GIS.  Most of the difficulties described a decade ago still exist today.
For pedagogical purposes, using these mainstream GIS software packages to introduce GIS, the instructor needs to take the student step-by-step through a complicated procedure from digitizing a map object to precision location.  To many students this seems overwhelmingly complicated and it ends up creating more problems in student understanding than solved.  Aangeenbrug (1992) observed that a common problem for universities is that the rapid change of hardware and software options caused a diffused and expensive learning curve.  
How can a non-GIS-business focused organization or institute use GIS to aid in their decision-making process?  One easy solution is to out-source: hiring consultants who can undertake the GIS project; another more feasible solution is to in-source: try to solve the GIS problem in-house. However, to solve the GIS problems in-house calls for a more transparent and straightforward method that can utilize regular spreadsheets and day-to-day graphing facilities to create a GIS program for business decision-making purposes.  Why might business entities, government organizations, academic researchers, and educators want to create their own mapping routines by using a regular graphing facility?  Most business practitioners are familiar with Excel spreadsheets for processing their data.  The spreadsheet programs therefore provide a convenient place to map the data.  Moreover, Cole (1998) suggests that a special benefit for creating such an alternative GIS is the speed, especially when relevant maps are not available.  Business practitioners can use such an alternative GIS to quickly draw an adequate spreadsheet map.  Cole (1998) observes that a lack of maps remains a problem for policymaking in developing countries, and small communities in metropolitan countries.  In research too, scholars of, and in, developing countries are frustrated because the necessary maps to display their data and findings are not readily available.  Thus, again, spreadsheet maps are an obvious solution.  This alternative way of creating a GIS program can be used to prepare acceptable maps on demand and with speed.  It also gives more direct links between data, analysis, and mapping, enabling more effective geographic information analysis and can be used for large-scale applications as well (Cole 1998).  
Thus, spreadsheet GIS continue to have a small but useful niche in teaching, research, and planning, even as software prices fall (Batty, 1995a).
3.   An Alternative Way of Teaching GIS in a Business School Context
In this paper I would like to address the pedagogical issues of teaching GIS for business school students and for business managers.
As mentioned earlier, using mainstream GIS software packages to introduce GIS, especially to business school students who have no background knowledge in mapping and computer programming, is a major challenge.  The main purpose of teaching GIS to business majored students is to train them to use GIS for extracting business intelligence from spatial data analysis, to use GIS for presenting business information clearly on a map that can convey thousands of words; and above all, to learn to utilize GIS for decision-making in the real business world.  Thus, in stead of taking students through complicated procedure of studying the details of a single tree while losing sight of a forest, I propose that we must demystify the mainstream GIS software programs and introduce an alternative GIS program that is transparent to students in such a way that they are able to fully comprehend the underlying GIS theories and complex relationships between data and maps.  This approach involves an innovative use of ordinary spreadsheets along with Visual Basic programming to create a home-grown GIS program that has the features of a dynamic mapping with time-varying animations - a problem still unresolved in mainstream commercial GIS.
This approach to teaching GIS was first introduced by Cole (1998) for planning students.  Cole (1998) suggests that with respect to pedagogy, students can build on their existing skills with the increasingly wide range of statistical and other facilities within spreadsheets, and so there is a fast learning curve.  If one understands well how something works, the more likely one is to use it.  Thus it becomes an empowering exercise for students to develop their own GIS programs.  It is straightforward to construct vector polygon representations or even perform manipulations that were not readily available in more expensive GIS.  With Windows applications students can gain insights into object-based GIS by using the now standard procedures within contemporary spreadsheets for Windows PCs and Macintosh computers (Cole, 1998). Moreover, Clarke (1997) notes that object-oriented programming systems are a major development in the software world and are a powerful way of modeling data that will influence the future of GIS software significantly. Moreover, many of the problems dealt with in GIS are available in a spreadsheet such as Excel from Microsoft (Cole, 1998).  This alone might justify advocating an alternative initiation to mainstream GIS.
Sections 4 through 6 will illustrate step by step how to implement such an alternative way of teaching GIS to business school students.  The goal is to create a Geographic Information System that is capable of transferring social and economic data from various databases onto the map for business decision making purposes.  The objects on the map can then be colored according to each object’s value in the database.  After this procedure, we will then create a dynamic map with time-varying animations or simulations that shows the data on the map, category by category.  This kind of simulation is still a cumbersome undertaking in mainstream commercial GIS.
4.   BASIC MAPPING PROCEDURES
In this section, I will illustrate how to use ordinary Excel spreadsheets and Visual Basic (VB) as a pedagogical tool for introducing GIS to business school students who have little computer programming skills and virtually no background knowledge in mapping and cartography. 
A typical spatial dataset would have a list of places, communities or countries in the first few columns while other columns might tabulate information on population, education, employment, income, crime and property.  The dataset might also contain a time-series of any of the above mentioned information.  Each set of data can be transformed into a map through a mapping procedure, digitizing and positioning of the data.  In a real business setting, the student might be presented with a project of producing a usable map out of a spatial dataset and making forecasts and projections based on past trends or given assumptions presented on such a map.
The steps to make a map in a spreadsheet programs are explained below.  For illustration purposes, the approach in MS Excel is used here.  The approach is similar for both the Windows and the Macintosh versions of Excel as well as the QuatroPro for Windows from Corel.
To create an interactive GIS map within Excel spreadsheets, one can use the object oriented programming and Visual Basic.  Spatial objects such as maps, regions, and roads and their corresponding properties such as name, size, and color can be identified, changed and extracted through chained instructions as follows:
If fieldnamd.Value=Sheets(“Sheet1”.
Range(“B33”).Value
Then fieldname.EntireColumn.
Interior.Color=RGB (255,255,0)
Sheets().Chart.RectangleObject()
Interior.ColorFill=Red
In many cases these instructions can also be recorded automatically as a macro.  These macros can be stored for use in future projects, with some editing and adjustments as needed.  The general procedure consists of the following three steps:
1) Create a Map
2) Select the Data;
3) Color the Map.
Create a Map
The first step in creating a map is to find a map template against which to draw the map objects.  This template can be a scanned version of a printed map, saved jpeg from online mapping websites, or a saved map from mapping software.  Then use “Insert Picture” command to bring the map template into the worksheet.
If a scanner is not available, one can still create a map template by manually tracing the outlines of the map from a transparency taped to the computer screen.  In this example, a map printed in the publication “Business First: The Best Place to Live in Western New York 1997” was scanned and brought into Excel as a map template (Figure 1). 
The second step is to draw the map objects onto the map template.  From the View menu select Toolbars to activate the Drawing facility in the program.  If this is successfully executed the Drawing facility will be displayed on the spreadsheet toolbar or on the spreadsheet.  Use the Freeform found under AutoShapes on the Draw menu (Figure 2) and use the template in Figure 1 as a guide for the mouse.
The objects are drawn with the minimum detail needed for a presentable final map.  Excel gives a default name to each object.  This first object is Freeform 1.  Selected object may now be named by over-typing the default name in the name box with a relevant name from the database.
Figure 1.  Map Template of Western New York State, USA

Figure 2.  Select Freeform form the Draw menu.

Figure 1 is used as a base map template. The regions of the base map are traced using the Freeform.  Each polygon created this way is assigned a default name and number called Freeform N, where N is a unique rank number.  The names can be changed on the screen by typing the real name for the region or a database number in the Name Box. An example of name change from Freeform 1 to Pond is shown in Figure 3.
Corrections to a vertex location or points in the polygon outlines are made through the Edit Points in the drawing facility of the program (Figure 4).  Each vertex becomes visible and can be moved to its proper location by dragging the “+.” sign.
Figure 3.  Default name changed to real name.

When an object or polygon has been created following the description above, the object can be formatted by color and/or pattern and the thickness and color of the outlines can be set (Figure 4).
Figure 4.  Edit the exact location of a vertex.
    
It is important to set the properties of the object to “Don’t move or size with cells”. Otherwise, the object will be affected by changes in the column width or row height in the spreadsheet.
Select the Data
When all the objects have been made and named, the basis for a geographic information system in a spreadsheet is ready.  At this stage the map can be linked to the data in the spreadsheet.  This is done through a Macro or subroutine.  To transfer data from a database to the map, the following commands are needed.  These commands can be recorded when the macro is activated, and later edited to suit the need of the user.  To record a Macro, select Record Macro from the Macro tab in the Tool menu.  Then name the Macro for further use.
The following is an example of a Visual Basic Macro that looks up a name of a region in a spreadsheet, compares it to a name of a region on a map, and colors the polygon according to specifications set in the spreadsheet. 
'ColorMap Macro
'Sub ColorMap()
  Application.ScreenUpdating = False
  For RegionNumber = 1 To 109
    On Error Resume Next
  Range("Sheet1!A2").Value =
      RegionNumber
  RegionName = Range("Sheet1!A3").Value
  RegionData=Range
    ("Sheet1!b3").Value
    ActiveSheet.DrawingObjects
   (RegionName).Interior.ColorIndex=
    RegionData
  Next RegionNumber
End Sub
In this example, there are 109 regions.  The Macro has information on how many regions are to be looked up.  Then the macro looks up a name and value for the region. The value is used to assign color to the region.  Colors in Excel have values 1-56. The values are scaled in the spreadsheet using the formulae:
=INT(E$1+(D9-E$3)/(E$2-E$3)*E$6+0.5)
Where cell:
E$1 is the number for the base color
D9 is the actual (calculated) value
E$2 is the highest value in the data
E$3 is the lowest value in the data
E$6 is the whole number of categories the data will be classified into
Color the Map
It is recommended to keep the number for the base color more than 2 in order to leave number 1 (black) and 2 (white) out.   Half is added to differentiate between similar colors and the final outcome rounded done to the nearest integer (INT).  This is the basic procedure needed to color the map.  There are two other commands in this macro: one command instructs the macro to assign the colors to all the regions without updating the screen each time it assigns a color to a region; and another command skips a region if the names in the database and on the map do not match.
After the macro is written, it can be activated by selecting Macros under Tools or a button can be assigned to the Macro that will activate it when pointed with the mouse. 
5.  FINALIZATION OF THE MAP
An example of a final map produced with this method is a map showing the population density in the Western New York region (Figure 5).  
Figure 5. Western New York: Population Density (Residence per square Mile)

This map is designed as a common socio-economic map.  Its main title Western New York is typed in a text box on the map.  The sub-title is Population Density (Residence per square mile) is transferred directly from the cell that contains the heading for the data to the map by using the Excel method “Display cell contents in a shape or text box”.  The map has also a legend with an equal interval classification.  This same method is used to transfer the five value categories from the worksheet to the legend.  In other words, if another dataset from the database is transferred to the map, the sub-title and the values in the legend will change.  The map (Figure 5) is a part of 20+ socio economic indicators stored in columns in a spreadsheet (Figure 6).  
Figure 6.  The Data: A screenshot of the data in Excel worksheet 

The map (Figure 5) has two buttons on it.  The button “ColorMap” activates the macro “ColorMap” above when the data column has been selected.  The color scheme is based on the “SetColorMap”.  The values in the macro could alternatively be placed in a cell rather than within a line in the macro.  The other button is “Scan”.  This button activates the macro “Simulations”.  This macro is described in the following section.
The color scheme for the map is set through a macro that utilizes the Excel color scheme.  The color set for this map is from yellow to red.  Following is an example of a macro that sets the color for the map from yellow (low) to red (high).
SetColorMap Macro
' Sets Excel Colors to a continuous sequence
Sub SetColor()
    Application.ScreenUpdating = False
    HighRed = 255
    HighGreen = 255
    Power = 0.5
    HighBlue = 0
 For Code = 3 To 56
    GreenCode = HighGreen * (Code/56)^
     Power
    With ActiveWorkbook
     .Colors(Code) = RGB(HighRed,
       GreenCode, HighBlue)
    End With
    Next Code
End Sub
6.   ANIMATIONS - 
AN EXTENSION OF GIS
In the real world simulations and forecasts are part of the daily life.  The present generations of mainstream GIS programs are not capable of handling time varying animations or simulations well.  In most cases one has to make several maps and print out for the audience to study or to create a ‘movie´ from the maps in another program.  
However, with a spreadsheet approach, we can create a dynamic map with time varying animations and simulations with a macro program.  The Macro below is made for the animations of data. The data in this example are set up in 24 columns and each map is displayed on the screen for 10 seconds.
  Sub Simulations()
   'This Module increases the 
    number in a Cell d6 by 1 every 10 
    second
   For ColumnNumber = 1 To 24
    Application.ScreenUpdating = False
    Range("Sheet1!d6").Value = 
          ColumnNumber
    On Error Resume Next
    stoptime = Now + TimeValue
         ("00:00:10")
     Do
       If Now > stoptime Then
         Exit Do
       End If
      Loop
    Call ColorMap
     Application.ScreenUpdating = True
     Next ColumnNumber
  End Sub
The value in cell D6 on Sheet 1 is linked to an index function that brings the column data into the GIS routine.  This kind of a life show gives the feeling of a moving picture with a quality similar to home-made video.
7.   CONCLUSIONS
In this paper, an alternative approach to teaching GIS is introduced using MS Excel spreadsheets.  This approach is simple to follow and gives the student a clearer idea on how Geographic Information Systems work.  Thus, it is a more feasible way of teaching GIS to business school students for decision-making in a real business environment.  This approach has been successful in teaching “GIS for Business” as a module for MBA curriculum as well as for upper-level undergraduate business classes.
This approach has several pedagogical benefits.  Spreadsheets are widely available as a part of the Office package from Microsoft.  If a computer either Macintosh or Windows based is available in the classroom this alternative GIS program can be undertaken.  The student does neither need specific prior training in mapping nor in GIS.  The student gets inside information on what the Object is, how the attributes are used and learns to make a GIS map in a simple manner.  
Moreover, it is also easy to write comments and explanations on the map.  Maps can be copied directly from the spreadsheet program to word-processing and publishing programs by using copy and past commands or by a small macro written for the task.  With a simple small macro, data can be scanned through a database and displayed on a map similar to video presentation or the daily presentation of the weather in the weather forecasts on TV.  Though not necessarily the same quality as a Hollywood movie, the visual impression of these spreadsheet GIS can effectively convey to decision-makers the business intelligence on both spatial and temporal dimensions.  
The method of creating spreadsheet GIS can also be utilized as a tool for analyzing and visualizing data as a part of further investigation.  At the same time, it introduces the object oriented programming to students.  Students can actually see the objects involved and how the adjustments they made can change the final product.  This method is a good example of learning by doing in teaching GIS and Visual Basic programming.
In conclusion, while avoiding the frustrations of struggling through all the technicalities of mainstream GIS, this alternative approach to teaching GIS allows students to use regular spreadsheets as a tool to explore ideas for novel interfaces, to ask “what if” questions, to visualize problems as well as their outcomes, and, in this way, to gain a more balanced perspective on GIS theories and operations.
However, as a word of caution, a caveat of creating a spreadsheet GIS map is that maps drawn in this way are not perfect from a cartographic perspective, in that they are relatively crude and not smooth in appearance.  Measurements such as length and area are complicated and geographical accuracy of position is ignored.  Moreover, the scales of these maps are fixed, and overlay and buffering cannot be made as in a full-blown GIS program. 
8.   REFERENCES
Aangeenburg, R. T. 1992. Editoria: Special issue on Geographic Information Systems education. International Journal of Geographic Information Systems. 6:3.
Boason, E., Boasson, V., and Tastle, W. 2004: A New tool in IS Management: Geographic Information Systems. Information Systems Education Journal (ISEDJ).  4,(12).
Cole. S. 1995. A spreadsheet approach to GIS. Environment and Planning B: Planning and Design. 22(2):131-148.
Cole. S. 1998. Of Maps and Macros: Object Oriented Spreadsheet ‘GIS’. Environment and Planning B: Planning and Design. 25(2): 227-243.
Goodchild, M. 1996. Directions in GIS. Proceedings, Third International Conference/Workshop on Integrating GIS and Environmental Modeling, Santa Fe, NM, January 21-25, 1996. Santa Barbara, CA: National Center for Geographic Information and Analysis.
Guariso, G. and Werthner, W., 1989. Environmental Decision Support Systems, Chichester, 240 p.
Hotelling, H., 1929.  Stability in Competition, Economic Journal, 39:41-57.
Innes, J. and Simpson, R. 1993. Implementing GIS for planning. APA Journal. 59: 230-234.
Love. R. F., J.G. Morris and G. O. Wesolowsky, 1988.  Facilities location: models & methods, North-Holland, xiv 296p.
Lösch, A., 1954.  The Economics of Location, New Haven, Conn.: Yale University Press.
Weber, A., 1909.  Über den standort der Industrien.
Wesolowsky, G.O, 1993.  The Weber Problem: History and Perspectives. Location Science, Volume 1, No. 1.



   		2