Beyond Graphing: Visually Mapping Geographical Data using ASP.NET
This article, by Willem Van Rooyen, is from ASPToday (www.asptoday.com)
When a graph is drawn, the focus is kept on the actual underlying competing figures that constitute the graph. Sometimes it is difficult to make assumptions just by looking at these graphs. When looking at a geographical map of data you immediately see exactly where things are happening. This article presents a .NET geographical mapping solution that will illustrate a fairly simple way of implementing these maps.
|Willem Van Rooyen|
|VS.NET, SQL Server 2000|
Analysts need a starting point when they realize that there is a problem somewhere in the geographical region that they intend to analyze. Sometimes they need a summary of the geographical region to even see whether there is a problem or not. That problem could be sales planning, marketing intentions, a need for forecasting solutions or even a restructuring of company performance in a specific of an area. Whatever the reason, the need is there to provide them with a quick, graphical overview of that area so that they can see what needs to be analyzed and where that analysis should be focused. In this article, we will look at generating these data maps for a website with ASP.NET.
Visual Studio .NET for development.
ASP.NET running on the web server.
SQL Server 2000.
The problem here is that we want to map numerical data to a color map of a region. The colors on the map will depict units achieved (volumes sold, evaluations done, population density etc.). Using the implementation outlined in the rest of the article, the following map was generated from a template to show units achieved in the respective regions:
The colored regions depict the units achieved in that area. In this example the darker the color, the more units, lighter colors depict a lower unit density, red being the most dense, green the least.
We will start with a colored map template, and then switch specific colors on the template for other colors which are based on the underlying numerical data I created a map template of South Africa and what I think are its main areas of analysis interest
This map is a .jpeg file generated from a 24-bit bitmap and houses 380 distinct regions. It serves as the template for the solution. In this specific case the regions with their different colors signify municipal areas where vehicles were registered in South Africa. The regions themselves are defined as unique, true-color RGB values on the map (thereby allowing for 16.7 million different regions). You would of course have to store this template as a 24-bit bitmap.
Note: the white region in the middle of the map is called Lesotho, which is a country in itself, and not part of South Africa.
To set up this solution I will go through the following steps in detail:
- Create a simple database to source the numerical and regional data from.
- Create the bitmap template and define the region colors in the database.
- Create the Map Rendering Library in Visual Studio .NET which will use the above-mentioned template to generate the final result.
- Create the ASP.NET project for utilizing the library.
Creating the Database
For the sake of simplicity we will create three simple tables in our database. For this I used SQL Server 2000. How you source this data is all up to you, of course. Create a database called DataMapper and a table called tblMapData. Create two fields RegionID and RegionValue and populate the table as follows:
This table represents the actual data for each region. The RegionIDs and their color definitions are in the next table. Both of the columns hold integer values, but you are free to make RegionValue a floating-point number, depending on the type of numerical data your database contains.
To define the template colors for the bitmap, which we'll create momentarily, create a table called tblMapColorDef and populate it as follows. This holds the Red ( RegionColorR), Green ( RegionColorG) and Blue ( RegionColorB) values as they are on the template bitmap.
All the columns hold integer values.
We need to define what the Legend (Key) for the result will look like. We also need to define a Key Dataset for the map, which will be used to generate the colors relevant to their mapped numerical representatives. Create a table called tblKeyMap and populate it as follows:
All the columns hold integer values.
Creating the Bitmap Template
We will create a simple bitmap, so Windows Paintbrush will do. We just need 5 different regions for illustration so I created this simple template bitmap of the UK and Ireland:
Note that the RGB values for this bitmap are important - those should be the same as in the color definition table. Undefined values like the white for the background and the black for the borders need not be defined. If a color is on the bitmap, and not defined in the color definition table, it will not be mapped, i.e. it will stay as it is on the template. Save the template as a 24bit bitmap.
Creating the Map Rendering Library
Start by creating a new Visual Basic Class library project in Visual Studio .NET. Name the project VisualData and rename Class1.vb to DataMap.vb. This Class will serve as the engine that generates the data maps.
The class imports the necessary namespaces, has one constructor, one Sub, and two functions. We import the necessary namespaces for use in this class as follows:
Imports System Imports System.IO Imports System.Drawing Imports System.Drawing.Imaging Imports System.Drawing.Imaging.ColorMap Imports System.Data.SqlClient
These namespaces handle the streaming ( System.IO), the imaging ( System.Drawing.Imaging), Color mapping ( System.Drawing.Imaging.ColorMap) and the Data Access ( System.Data.SqlClient) capabilities needed in the class. The documentation and member/method listings for all these namespaces are shipped with Visual Studio.NET Final.
Nothing is required to initialize the class so the constructor is empty:
Public Sub New() End Sub
The Render Sub makes the needed function calls to generate the map:
Public Sub Render(ByVal TemplateBitmapLoc As String, _ ByVal dMapAndKeyData As DataSet, _ ByVal target As Stream) Dim bm As Bitmap = New Bitmap(TemplateBitmapLoc) Dim g As Graphics = Graphics.FromImage(bm) Dim ColormapTable() As ColorMap ColormapTable = BuildColorMapTable(dMapAndKeyData) Dim ia As ImageAttributes = New ImageAttributes() ia.SetRemapTable(ColormapTable, ColorAdjustType.Bitmap) g.DrawImage(bm, New Rectangle(0, 0, bm.Width, bm.Height), _ 0, 0, bm.Width, bm.Height, GraphicsUnit.Pixel, ia) bm.Save(target, ImageFormat.Gif) g.Dispose() bm.Dispose() End Sub
The string TemplateBitmapLoc is the physical disk/network location of the template bitmap. The DataSet dMapAndKeyData will carry the two tables needed for the mapping procedure. The Stream target is the output stream that will be used to stream the resulting data map to the web.
This is not an article about the graphics classes in .NET, so I will keep the descriptions about these as concise as possible.
- The first two lines inside the sub initialize the graphics objects (Bitmap and Graphics).
- The next two lines define the Colormap which will be used to do the color mapping for the data map and uses the function BuildColorMapTable to populate the color map table.
- The following 4 lines create the ImageAttributes object, which is used to apply the built ColorMap to the template bitmap; the DrawImage function applies these image attributes.
- The line bm.Save(target, ImageFormat.Gif) streams the result to the stream target. Note here that the result is a web-safe . gif file, if you want to use another format, say .png, just change the DataMap class accordingly.
- The last two lines handle the housekeeping, which is very important because we need to dispose of these potentially large graphics objects.
The function BuildColorMapTable is called in order to create the color map that will be applied to the template to facilitate the color conversions.
Private Function BuildColorMapTable( _ ByVal MapAndKeyData As DataSet) As ColorMap() Dim dt As DataTable = MapAndKeyData.Tables("RegionData"0) Dim dr As DataRow dr = dt.Rows(0) Dim dMaxValue As Double = dr(1) Dim remapTable(dt.Rows.Count - 1) As ColorMap Dim cm As ColorMap Dim i As Integer For i = 0 To dt.Rows.Count - 1 cm = New ColorMap() cm.OldColor = Color.FromArgb( _ dt.Rows(i)(2), _ dt.Rows(i)(3), _ dt.Rows(i)(4)) cm.NewColor = ConvertDataToColor( _ dMaxValue, _ dt.Rows(i)(1), _ MapAndKeyData.Tables("KeyMapData")) remapTable(i) = cm cm = Nothing Next BuildColorMapTable = remapTable End Function
This is the workhorse function; it does most of the work in our solution. We pass only the needed Dataset that contains two tables of which the results look as follows:
Region Data ( RegionData Dataset)Table 0
The SQL string which generates this table is:
SELECT tblMapData.RegionID, tblMapData.RegionValue, tblMapColorDef.RegionColorR, tblMapColorDef.RegionColorG, tblMapColorDef.RegionColorB FROM tblMapData RIGHT OUTER JOIN tblMapColorDef ON tblMapData.RegionID = tblMapColorDef.RegionID ORDER BY tblMapData.RegionValue DESC
This table shows the RegionID, the RegionValue and the RGB values for the specific regions, as the regions are shown on the template bitmap. Two things to note here are:
- The table is sorted descending on RegionValue. This is needed; otherwise we will have to search through the entire table for the maximum RegionValue, which is a potential performance hit, considering that this result set would have as many rows as there are regions in the template. The maximum value is used in the decision of where a color is mapped to when mapping to the Key dataset, which we will discuss a bit later on.
- We use a RIGHT OUTER JOIN here in order to ensure that ALL Regions in tblMapColorDef are contained in the Dataset, regardless of whether they have numerical values or not. This is also a performance consideration, if this were not done then we'd have to flag the used regions and then find the unused regions, and cycle through them when mapping the colors. If a color is not mapped to a key color in the color map, then it will retain its original template color, thereby making the result meaningless.
Key Map Data ( KeyMapData Dataset)
The SQL string which generates this table is:
SELECT * FROM tblKeyMap WHERE (KeyCount = 3) ORDER BY KeyOrder DESC
This table shows the KeyCount, the KeyOrder and the necessary RGB values for the key of the resulting map. Some things to note here:
- The KeyCount is user-selectable through the code behind page in the calling .aspx file, which will be discussed later). For all the keys that you will need, you need to define them in this table. For this discussion three legend colors were used. The three colors used here are: light yellow, orange and red, which depict low, medium and high data density respectively. To the same effect, a KeyOrder of 1 depicts the lowest data density and the highest data density is depicted by the highest KeyOrder in the Key set (a key set is depicted by the same KeyCount, 3 in our case). You will always only use one Key set when mapping colors from the template.
- The result is sorted descending on the KeyOrder. The reason for this will become clear when we look at how the Key is used to map the colors.
Now that we know what the dataset contains, let's look at how we use this data to create the desired color map. Once again, this is not a discussion of ADO.NET, so I will keep the data retrieval descriptions as concise as possible:
- The first four lines get the Maximum numerical RegionValue which will be used later when calling the next core function ConvertDataToColor.
- The following lines loop through the first table in the MapAndKeyData dataset, calling ConvertDataToColor for each Region and thus generating the Color map table. To map a color, you need to define the color that you want to replace ( OldColor) and the color that you want to replace it with ( NewColor). When it completes the loop, the Color map table is returned to the Render Sub to be applied to the template to generate the final result.
The function ConvertDataToColor is called in order to map the RegionValues to their correct Key RGB Values.
Private Function ConvertDataToColor( _ ByVal MaxValue As Double, _ ByVal DataValue As Double, _ ByVal dTable As DataTable) As Color Dim clrResult As Color Dim iKeyCount As Integer = dTable.Rows.Count Dim i As Integer Dim dr As DataRow For i = 0 To iKeyCount - 1 If DataValue <= MaxValue - ((MaxValue / iKeyCount) * i) Then clrResult = Color.FromArgb( _ dTable.Rows(i)(2), _ dTable.Rows(i)(3), _ dTable.Rows(i)(4)) Else Exit For End If Next ConvertDataToColor = clrResult End Function
This is what we might call the brains of the operation. This is where the decision of which Region color maps to which Key Color is made. The maximum RegionValue ( MaxValue) of table 0, the current RegionValue ( DataValue) also from table 0 and the entire table 1 ( dTable) is passed to this function.
1. The first four lines in the body of the function make some definitions of variables that will be used later in the function and counts the number of rows in the Key Data table.
2. We then loop through the Key table to make the Key mapping decision.
3. The line If DataValue <= MaxValue - ((MaxValue / iKeyCount) * i) Then decides which Key color to assign to the RegionValue ( DataValue). Because we are using three key values this simple mathematical function basically divides MaxValue by 3 and decides into which range the DataValue falls, then assigns the relevant color to the current Region. This is why we needed to sort table 1 descending on KeyOrder. We always assign a key color value to the DataValue if it is in a range higher or equal to than its value. As soon as it reaches a range lower than its value then we exit the loop and keep the previously assigned color. You could also sort the KeyOrder ascending, but remember that this mathematical test relies on the ordering of KeyOrder, so you would have to amend it accordingly.
That concludes the discussion on the building of the simple data mapping class. All that remains now is serving the data map to the web.
Creating the ASP.NET web application
To start, create a new ASP.NET Web application project within the VisualData solution. Name it ASPDataMap and save it in the wwwroot directory on the webserver that will be hosting the solution (I used http://localhost/). I created two .aspx files: ASPDataMapDemo.aspx and DrawDatamap.aspx. ASPDatamapdemo.aspx includes Drawdatamap.aspx in an IMG tag on the page:
<IMG alt="UKIreland Data Map" src="DrawDatamap.aspx?NumKeys=3">
The NumKeys argument defines which Key Data set from the database we want to use.
DrawDatamap.aspx streams the resulting .gif file to the Demo page and contains the necessary code behind structure for the function calls to the Datamap Class.
The full extent of the HTML on DrawDatamap.aspx is as follows:
<%@ Page ContentType="image/gif" Language="VB" AutoEventWireup="false" Codebehind="DrawDatamap.aspx.vb" Inherits="ASPDataMap.DrawDatamap" %>
The code-behind page DrawDatamap.aspx.vb imports the VisualData.DataMap namespace and contains the following Page_Load sub:
Private Sub Page_Load( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles MyBase.Load 'connection String Dim sConnect As String = _ ConfigurationSettings.AppSettings("SQLConnectString") 'Get RegionData and KeyMapData Dim sRegionDataSQL As String = _ ConfigurationSettings.AppSettings("RegionDataSQLString") Dim sKeyMapDataSQL As String = _ ConfigurationSettings.AppSettings("KeyMapDataSQLString1") & _ Request("numkeys") & _ ConfigurationSettings.AppSettings("KeyMapDataSQLString2") Dim conn As New SqlConnection(sConnect) Dim da As SqlDataAdapter = New SqlDataAdapter(sRegionDataSQL, conn) Dim ds As DataSet = New DataSet() Dim rowsRegionData As Integer = da.Fill(ds, "RegionData") da = New SqlDataAdapter(sKeyMapDataSQL, sConnect) Dim rowsKeyMapData As Integer = da.Fill(ds, "KeyMapData") Dim dm As DataMap = New DataMap() dm.Render(ConfigurationSettings.AppSettings("TemplateLocation"), _ ds, Response.OutputStream)dm.Render("C:\temp\UKIrelandtemplate.bmp", dm = Nothing ds = Nothing da = Nothing End Sub
All that happens here is the instantiation of the data objects needed to source the necessary data from the DataMapper database. Remember to change the DB connection string as needed (as defined in the web.config file). Note here that I used the SQLDataAdapter Class because I'm using a SQL 2000 database as a backend. If you would rather use an OLEDB provider, the choice is yours, the resulting dataset (containing the two tables) should however be constructed as discussed above. You can even source this data from text files, just as long as you construct the dataset as the DataMap class expects it. After the dataset is built, we instantiate an instance of the DataMap class, call its Render method and then clean up after ourselves. I used "C:\temp\template.bmp " as the source location for the template file, change that as you see fit. The web.config file for the ASP.NET application contains all the necessary application variables (DB connection string, SQL strings and template bitmap location). I list only the appSettings section of the configuration file here:
<appSettings> <add key="SQLConnectString" value="Password=; Persist Security Info=True; User ID=sa; Initial Catalog=DataMapper; Data Source=WILLEM\NetSDK" /> <add key="RegionDataSQLString" value="SELECT tblMapData.RegionID, tblMapData.RegionValue, tblMapColorDef.RegionColorR, tblMapColorDef.RegionColorG, tblMapColorDef.RegionColorB FROM tblMapData RIGHT OUTER JOIN tblMapColorDef ON tblMapData.RegionID = tblMapColorDef.RegionID ORDER\BY tblMapData.RegionValue DESC" /> <add key="KeyMapDataSQLString1" value= "SELECT * FROM tblKeyMap WHERE (KeyCount_= " /> <add key="KeyMapDataSQLString2" value=") ORDER BY KeyOrder DESC" /> <add key="TemplateLocation" value="C:\temp\template.bmp" /> </appSettings>
The Sample Application
The final result, based on the data we supplied to the database earlier looks as follows:
Here we have a very simple result, for a very simple example. I must stress here that the more regions you define on a map, the more meaningful the results will become (see Figure 2 at the start of the article, which is based on actual data).
I have included the necessary example project files, the database, the template bitmap and the procedure to set them up in the articles downloadable files.
Limitations and Further Work
The only thing that is customizable by the calling ASP.NET solution is the key set used, you may add/change key colors as you fit in the database, or add more keys depending on your requirements. The solution is, however, data independent; you may use any database, keeping in mind that the queries should return with the structure as that used in the example for the solution to work. As for further work, there are many things that can be added to further customize this implementation. Things like adding a title and subtitle to the picture, improving the Key Mapping function to generate a better key (using order of magnitude calculations to round the Key ranges), actually adding the key and legend to the map itself using the drawing classes in .NET, resizing the picture based on client needs and adding some business identity (a logo or otherwise) somewhere on the results just to round things off. Adding extra functionality here is important, we need to describe what the result means. We will look at implementing most of these enhancements in the second part of this article.
There are many ways to draw data maps, this being only one of them. This type of solution lends itself to specific needs (i.e. that of creating a detailed map based on data). These maps can be generated quickly at high quality for publication purposes. Speaking from personal experience, these maps add flair to a presentation and helps create the wow factor needed in today's competitive environment. We've been able to do this type of implementation for quite a while now, but with the provided .NET namespaces everything is easier. People like to be impressed; we have all the tools available to us to create impressive applications.
Please rate this article using the form below. By telling us what you like and dislike about it we can tailor our content to meet your needs.
|Author||Willem Van Rooyen|
|Chief Technical Editor||John R. Chapman|
|Project Manager||Helen Cuthill|
|Reviewers||Neil Piggot, Andrew Krowczyk|
If you have any questions or comments about this article, please contact the technical editor.
User ReviewsTotal of 4 reviews
Here is the ChartFX websiteWritten by Peter Snyders on November 10, 2005
Very usefull ideas!Written by Alexa Smith on November 10, 2005
I found this queve vert interesting, could you please let me know how can I contact this software vendor, a link to their website would be very usefull. Thanks
Professional and Fast alternative using Chart FX MAPS (Charting tool for .NET)Written by Peter Snyders on November 9, 2005
Hello guys, here is my 2 cents... Our company purchased this Charting component called Chart FX MAPS couple of years ago. We tried to accomplish what Mr. Rooyen suggested but we took too much time. Was there when I found this Charting tool ...
neatWritten by David Comtois on February 5, 2004
this is really neat... never thought of detecting colors and changing it on that data...