FusionMaps Documentation

FusionMaps with ASP.NET(VB) > Plotting Data from Database > USMap

In this page we will see the code used to achieve the Goal 1. Below is the important part of the code.

Before moving further with the process of plotting data from database, we recommend to go through "Using dataXML Method" and "Using dataXML Method" to know the basics about FusionMap generation procedure.

The code discussed here is present in Download Package > Code > VB_NET > DrillDown folder.

The code to create the US map and plot data from database contained in Default.aspx is listed as under :

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="FusionMapsDBExample_DrillDown" %>
<html>
   <head>
      <title>FusionMaps v3 - Database Example</title>
       <script language="Javascript" src="../JSClass/FusionMaps.js"></script>
   </head>
   <body>
     <form id='form1' name='form1' method='post' runat="server">

           <% 'Show USA Map' %>
           <asp:Literal ID="USMap" runat="server" />

       </form>
   </body>
</html>

In the above code, we first include FusionMaps.js file to enable us embed the map using JavaScript. The USA map loads in Literal USMap through the following code behind script (Default.aspx.vb).

Imports InfoSoftGlobal

Partial Class FusionMapsDBExample_DrillDown
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

      ' Define dataURL variable
    Dim dataURL As String
      ' URLencode dataURL
      dataURL = Server.UrlEncode("DataGen.aspx?op=GetUSMapDetails")

      ' Create the Map with data contained in dataURL
      ' and embed the chart rendered as HTML into Literal - USMap
      ' We use FusionMaps class of InfoSoftGlobal namespace (FusionMaps.dll in BIN folder)
      ' RenderMap() generates the necessary HTML needed to render the map

      Dim mapHTML As String = FusionMaps.RenderMap("../Maps/FCMap_USA.swf", dataURL, "", "mapid", "600", "400", False, False)

      ' embed the chart rendered as HTML into Literal - USMap
      USMap.Text = mapHTML

   End Sub

End Class

In the above code we do the following :

We create a dataURL to fetch map XML from DataGen.aspx (whose code behind page is DataGen.aspx.vb).
We call renderMap() function from FusionMaps class of InfosoftGlobal assembly which renders map of USA after passing the dataURL to it.

Let's now go thorough the code of DataGen.aspx.vb that fetches data and forms map XML.

Imports DataConnection 'contains the connection parameters to connect to database.
Imports System.Text

Partial Class DrillDown_dataGen
Inherits System.Web.UI.Page

   ' This program call diffarent function using "op" type
   ' The functions generate XML and relay to map/chart using dataURL method
   Protected Sub Page_Load(ByVal ob As Object, ByVal e As EventArgs) Handles Me.Load
      Dim op As String
      op = Request("op")
      ' Depending on op calling function
      Select Case op
         Case "GetUSMapDetails"
            GetUSMapDetails() ' Calling GetUSMapDetails

Case "GetStateDetails"
GetStateDetails() ' Calling GetStateDetails

Case "getChartEmpStat"
getChartEmpStat() ' Calling getChartEmpStat

End Select
End Sub

...

' This program shows USA Map and link with State. and showing
' total population
Public Sub GetUSMapDetails()

'In this example, we show how to connect FusionMaps to a database.
'You can connect to any database. Here, we've shown MSSQL/Access.

'strXML will be used to store the entire XML document generated
Dim strXML As New StringBuilder

'Variable to store SQL Queries
Dim strQuery As String

'Variable to store total Population
Dim sumdata As Double

   'Generate the map element
   'Create the opening <map> element and add the attributes that we need.
   strXML.Append("<map borderColor='FFFFFF' fillAlpha='80' showBevel='0' numberSuffix='% of total US population' legendBorderColor='F1f1f1' hoverColor='FFFFFF' legendPosition='bottom'>")

   'Define color ranges
   strXML.Append("<colorRange>")
   strXML.Append("<color minValue='0' maxValue='0.50' displayValue='0% to 0.50% of total' color='D64646' />")
   strXML.Append("<color minValue='0.50' maxValue='1' displayValue='0.50% to 1% of total' color='F6BD0F' />")
   strXML.Append("<color minValue='1' maxValue='3' displayValue='1% to 3% of total' color='8BBA00' />")
   strXML.Append("<color minValue='3' maxValue='10' displayValue='3% or above of total' color='AFD8F8' />")
   strXML.Append("</colorRange>")

   ' store the sql query
   strQuery = "select sum(data) as datap from fcmap_distribution"
   ' Create datareader object
   Dim Rs As New DbConn(strQuery)

' Fetch all Internal id and data sum
strQuery = "select Internal_Id, (sum(data) / " & sumdata & ")*100 as datap from fcmap_distribution group by Internal_Id"

Dim Rs1 As New DbConn(strQuery)

' Add map data element
strXML.Append("<data>")

' Check if we've records to show
If Rs1.ReadData.HasRows = True Then

      ' Iterate throuth the database
      While Rs1.ReadData.Read()
         ' Create query string
         strQuery = "select map_swf from fcmap_master where Internal_Id='" + Rs1.ReadData("Internal_Id").ToString() + "'"

         'Open fcmap_master table to get map swf names
         Dim Rs2 As New DbConn(strQuery)

         ' Read first record
         Rs2.ReadData.Read()

         ' Generate <entity id=".." value=".." /> and also add link to it
         strXML.Append("<entity id='" & Rs1.ReadData("Internal_Id") & "' value='" & Math.Round(Convert.ToDouble(Rs1.ReadData("datap")), 2) & "' link='" & LinkURL & "' />")

         Rs2.ReadData.Close()

       End While

    End If

    ' Close reader
    Rs1.ReadData.Close()

' Finally, close <map> element and add
strXML.Append("</data>")

    ' If needed, you can append additional XML tags here - like STYLE or MARKERS
    strXML.Append("<styles><definition><style type='animation' name='animX' param='_xscale' start='0' duration='1' /><style type='animation' name='animY' param='_yscale' start='0' duration='1' /><style type='animation' name='animAlpha' param='_alpha' start='0' duration='1' /><style type='shadow' name='myShadow' color='FFFFFF' distance='1' /></definition>")

    strXML.Append("<application><apply toObject='PLOT' styles='animX,animY' /><apply toObject='LABELS' styles='myShadow,animAlpha' /></application></styles>")

    ' Close Map element
    strXML.Append("</map>")

' Set Proper output content-type
Response.ContentType = "text/xml"

    ' Just write out the XML data
    ' NOTE THAT THIS PAGE DOESN'T CONTAIN ANY HTML TAG, WHATSOEVER
    Response.Write(strXML.ToString())

End Sub

...

End Class

Let's discuss the steps involded in the above code :

The first part of the code is executed when the page is loaded:

op = Request("op")
      ' Depending on op calling function
      Select Case op
         Case "GetUSMapDetails"
            GetUSMapDetails() ' Calling GetUSMapDetails

Case "GetStateDetails"
GetStateDetails() ' Calling GetStateDetails

Case "getChartEmpStat"
getChartEmpStat() ' Calling getChartEmpStat

End Select

The file getDetails.aspx is passed a querystring - ?op=GetUSMapDetails. The above code retrieves this querystring value in variable -op. Depending on the value of op the code calls related subroutines using select case statement. Hence, when op's value is GetUSMapDetails, it calls GetUSMapDetails() subroutine, which in turn builds and returns the world map XML as dataURL.

Now, we will dive deep into the GetUSMapDetails() subroutine.

We create a StringBuilder object - strXML (to store XML data) and sumdata to store sum of values from database. We also opened the root map element providing the map properties.

strXML.Append("<map borderColor='FFFFFF' fillAlpha='80' showBevel='0' numberSuffix='% of total US population' legendBorderColor='F1f1f1' hoverColor='FFFFFF' legendPosition='bottom'>")

Next, we define color range for the main US Map.

'Define our color range

    strXML.Append("<colorRange>") 

    strXML.Append("<color minValue='0' maxValue='0.50' displayValue='0% to 0.50% of total' color='D64646' />") 

    strXML.Append("<color minValue='0.50' maxValue='1' displayValue='0.50% to 1% of total' color='F6BD0F' />") 

    strXML.Append("<color minValue='1' maxValue='3' displayValue='1% to 3% of total' color='8BBA00' />") 

    strXML.Append("<color minValue='3' maxValue='10' displayValue='3% or above of total' color='AFD8F8' />")

    strXML.Append("</colorRange>")

Providing data

To connect to database we have creatred a namespace DataConnection (file DbHelper.vb in App_Code folder). This contains a class DbConn, which we would using to connect to database in this example and subsequent example(s).

We had included DataConnection namespace in the header of the code page. DataConnection contains the connection parameters to connect to database.

Here comes the database mining part where we start fetching data.

First we initialize sumdata to store sum total of all data found in the table - fcmap_distribution. That represents the total US population (ficticious of-course). We did this using SQL Query : "select sum(data) as datap from fcmap_distribution".
Next, we fetch internal ID of each entity/state of US map and get the percentage of population of each entity/state against total US population. This is stored in data reader aReader1 using the SQL query - "select Internal_Id, (sum(data) / " & sumdata & ")*100 as datap from fcmap_distribution group by Internal_Id"'. We again used fcmap_distribution table here.
We get total population of each state and divide it by sum total US population and multiply it with 100 to get the percentage value.

Now we iterate through each record in the data reader and create entity element for each state.

' store the sql query
strQuery = "select sum(data) as datap from fcmap_distribution"
' Create datareader object
Dim Rs As New DbConn(strQuery)

' Initialize sum container
sumdata = 0

' Check if we've records to show
If Rs.ReadData.HasRows = True Then
   ' Read first record
   Rs.ReadData.Read()
   ' Store sum
   sumdata = Convert.ToDouble(Rs.ReadData("datap"))
End If

' Close the reader
Rs.ReadData.Close()

' Fetch all Internal id and data sum
strQuery = "select Internal_Id, (sum(data) / " & sumdata & ")*100 as datap from fcmap_distribution group by Internal_Id"

Dim Rs1 As New DbConn(strQuery)

' Add map data element
strXML.Append("<data>")

' Check if we've records to show
If Rs1.ReadData.HasRows = True Then

   ' Iterate throuth the database
   While Rs1.ReadData.Read()
      ' Create query string
      strQuery = "select map_swf from fcmap_master where Internal_Id='" + Rs1.ReadData("Internal_Id").ToString() + "'"

    'Open fcmap_master table to get map swf names
      Dim Rs2 As New DbConn(strQuery)
      ' Read first record
      Rs2.ReadData.Read()

      ' The link will in format StateDetails.aspx?Internal_Id=Int_Id&map=map_swf.swf - we'll need to URL Encode this link to convert & to %26 (or manually add it as %26 instead of &)
      Dim LinkURL As String
      LinkURL = Server.UrlEncode("StateDetails.aspx?Internal_Id=" & Rs1.ReadData("Internal_Id").ToString() & "&map=" & Rs2.ReadData("map_swf").ToString())

      ' Generate <entity id=".." value=".." /> and also add link to it
      strXML.Append("<entity id='" & Rs1.ReadData("Internal_Id") & "' value='" & Math.Round(Convert.ToDouble(Rs1.ReadData("datap")), 2) & "' link='" & LinkURL & "' />")

      Rs2.ReadData.Close()

   End While

End If

' Close reader
Rs1.ReadData.Close()

' Finally, close <map> element and add
strXML.Append("</data>")

Here we extract internalID from Rs1.ReadData("Internal_Id") and set to id attribute.
We get percentage of population of the state against total US population from Math.Round(Convert.ToDouble(Rs1.ReadData("datap")) and set to value attribute. We set the dacimal limit of the value to 2 places using ASP.NET function Math.Round().

Setting up the Links

We also set up drill-down link to each entity. Each enity on click will drill down to another asp file. This we did in the entity entry itself as shown above using link attribute. Let's see the how the link is structured.

...LinkURL = Server.UrlEncode("StateDetails.aspx?Internal_Id=" & Rs1.ReadData("Internal_Id").ToString() & "&map=" & Rs2.ReadData("map_swf").ToString())...

All states/entities will drill-down to StateDetails.aspx page.
Each entity will pass a query string variable - Internal_Id whose value will be the internal id of that state. This is extracted out from the"Internal_id" field of the iterated record.
Each entity will also pass the map file name to StateDetails.aspx page using querystirng variable -map. Note that another data reader Rs2 is created for each iteration which stores the map file name of the releted Internal_Id in "map_swf" field. Data reader Rs2 is created from table fcmap_master using SQL query - "select map_swf from fcmap_master where Internal_Id='" + Rs1.ReadData("Internal_Id").ToString() + "'".

Thus all entities are created.

Next we add styles like XScale, YScale, alpha & shadow styles to the map using <styles> element.
Finally we return the XML as dataURL to renderMap() function in Default.aspx.vb.

' Set Proper output content-type
Response.ContentType = "text/xml"

' Just write out the XML data
Response.Write(strXML.ToString())

The above mentioned renderMap() function (in Default.aspx.vb) renders the map retrieving the XML and set it to literal control USMap.

 Dim mapHTML As String = FusionMaps.RenderMap("../Maps/FCMap_USA.swf", dataURL, "", "mapid", "600", "400", False, False)

       USMap.Text = mapHTML

Here is the screenshot of the US map that we just created extracting data from database.

We have used DataConnection Namespace in the above code and in all subsequent Database example(s). Using this class we establish connection to the MS Access database with ADO.NET component. You can always change your connection to any other database server. Let's go through the lines of code inside this class:

Imports Microsoft.VisualBasic
Imports System.Data.Odbc
Imports System.Data
Imports System.Web
Imports System.Configuration

Namespace DataConnection

   ''' <summary>
   ''' DataBase Connection Class.
   ''' </summary>
   Public Class DbConn
      Public connection As OdbcConnection
      Public ReadData As OdbcDataReader
      Public aCommand As OdbcCommand

      ''' <summary>
      ''' Data Connection and get Data Reader
      ''' </summary>
      ''' <param name="strQuery">SQL Query</param>
      Public Sub New(ByVal strQuery As String)
         Dim ConnectionString As String, connectionName As String

' MS Access DataBase Connection - Defined in Web.Config
connectionName = "MSAccessConnection"

'' SQL Server DataBase Connection - Defined in Web.Config
'' connectionName = "SQLServerConnection";

         ' Creating Connection string using web.config connection string
         ConnectionString = ConfigurationManager.ConnectionStrings(connectionName).ConnectionString
         Try

' Creating OdbcConnection Oject
connection = New OdbcConnection()

' Setting Conection String
connection.ConnectionString = ConnectionString

' Open Connection
connection.Open()

' get reader
GetReader(strQuery)

            Catch ex As Exception
            HttpContext.Current.Response.Write(ex.Message)
         End Try

      End Sub

      ''' <summary>
      ''' Create an instance dataReader
      ''' </summary>
      ''' <param name="strQuery">SQL Query</param>
      ''' <remarks>Return type object of OdbcDataReader</remarks>
      Public Sub GetReader(ByVal strQuery As String)

' Create a Command object
aCommand = New OdbcCommand(strQuery, connection)

' Create data reader object using strQuery string
ReadData = aCommand.ExecuteReader(CommandBehavior.CloseConnection)

End Sub
End Class
End Namespace

What it does:

Set up Connection as per the connection string defined in web.config file.

<connectionStrings>
<add name="MSAccessConnection" providerName="System.Data.Odbc" connectionString="Driver={Microsoft Access Driver (*.mdb)};Dbq=|DataDirectory|\FactoryDB.mdb"/>
</connectionStrings>

To change your connection to any other database server, you only need to setup web.config file.
The class DBConn accepts SQL Query, executes it and returns the result as ASP.NET DataReader object -ReadData.

Example:

Imports DataConnection

...

   'store the sql query
   Dim strQuery As String = "select colName1, colName2 from yourTable"
   ' Create datareader object
   Dim Rs As New DbConn(strQuery)

   ' Create a sample accumulator
   Dim accumulator as Integer=0

   ' Check if we've records to show
   If Rs.ReadData.HasRows = True Then
      ' Iterate through table
      While Rs1.ReadData.Read()
        ' do something using the columns
        sumdata += Convert.ToInt32(Rs.ReadData("colName1"))+Convert.ToInt32(Rs.ReadData("colName2"))
      Wend
   End If

   ' Close the reader
   Rs.ReadData.Close()