FusionMaps with ASP > 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 the dataXML and dataURL method to know the basics about FusionMap generation procedure.
 
The code discussed here is present in Download Package > Code > ASP > DB_Drill folder.
Let's discuss the code from the main file : Index.asp

<%@ LANGUAGE="VBScript" %>
<!-- #include file="../Includes/DBConn.asp" -->
<!-- #include file="../Includes/FusionMaps.asp" -->
<HTML>
<HEAD>
<TITLE>FusionMaps v3 - Database Example</TITLE>
<%
'You need to include the following JS file to embed the map using JavaScript
'Embedding using JavaScripts avoids the "Click to Activate..." issue in Internet Explorer
'When you make your own maps, make sure that the path to this JS file is correct. Else, you would
'get JavaScript errors.
%>
<SCRIPT LANGUAGE="Javascript" SRC="../JSClass/FusionMaps.js"></SCRIPT>
</HEAD>
<BODY>

<%
'In this example, we show how to connect FusionMaps to a database.

'Variables to store XML Data and sum of data
'strXML will be used to store the entire XML document generated
dim strXML, sumdata


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

'Define our color range
strXML = strXML + "<colorRange>"
strXML = strXML + "<color minValue='0' maxValue='0.50' displayValue='0%25 to 0.50%25 of total' color='D64646' />"
strXML = strXML + "<color minValue='0.50' maxValue='1' displayValue='0.50%25 to 1%25 of total' color='F6BD0F' />"
strXML = strXML + "<color minValue='1' maxValue='3' displayValue='1%25 to 3%25 of total' color='8BBA00' />"
strXML = strXML + "<color minValue='3' maxValue='10' displayValue='3%25 or above of total' color='AFD8F8' />"
strXML = strXML + "</colorRange>"
'Add the starting element for data
strXML = strXML + "<data>"

'Fetch all data now
'Initialize sum container
sumdata=0
strQuery = "select sum(data) as datap from fcmap_distribution"
set rs = Server.CreateObject("ADODB.Recordset")
rs.open strQuery,oConn
'Store sum of all data
if rs.bof=false then
  sumdata=rs("datap")
end if
'Fetch all Internal id and data sum
strQuery = "select Internal_Id, (sum(data) / " & sumdata & ")*100 as datap from fcmap_distribution group by Internal_Id"
set rs1 = Server.CreateObject("ADODB.Recordset")
rs1.open strQuery,oConn

'Check if we've records to show
if rs1.bof=false then
'Iterate through each record
do while not rs1.eof
strQuery ="select * from fcmap_master where Internal_Id='" + rs1("Internal_Id") + "'"
set rs2 = Server.CreateObject("ADODB.Recordset")
rs2.open strQuery,oConn

'Generate <entity id=".." value=".." /> and also add link to it
'The link will in format Detailed.asp?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 &)
strXML = strXML & "<entity id='" & rs1("Internal_Id") & "' value='" & round(rs1("datap"),2)
& "' link='Detailed.asp?Internal_Id=" & rs1("Internal_Id") & "%26map=" & rs2("map_swf") & "' />"

rs1.movenext
loop

'Finally, close <map> element and add
strXML = strXML + "</data>"
'If needed, you can append additional XML tags here - like STYLE or MARKERS
strXML = strXML + "<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' />"
strXML = strXML + "</definition><application><apply toObject='PLOT' styles='animX,animY' />
<apply toObject='LABELS' styles='myShadow,animAlpha' /></application></styles>"
'Close Map element
strXML = strXML + "</map>"

'Now, render the map using renderMap function present in FusionMaps.asp (include file)
'Also, since we're using dataXML method, we provide a "" value for dataURL here
Call renderMap("../../Maps/FCMap_USA.swf", "", strXML, "Map1Id", "750", "460", false, false)
else
'Else, display a message that we do not have any records to display
response.write "<h3>No Records</h3>"
end if

'Clear up memory
set rs=nothing
set rs1=nothing
set rs2=nothing
oConn.close
set oConn=nothing
%>

</BODY>
</HTML>
 
Let's now see through the lines:
 
  • As always, first we include FusionMaps.asp and FusionMaps.js files that help in easy map rendering.
  • We then include DBConn.asp, which contains connection parameters to connect to Access database.
<!-- #include file="../Includes/DBConn.asp" -->
<!-- #include file="../Includes/FusionMaps.asp" -->
...
<SCRIPT LANGUAGE="Javascript" SRC="../JSClass/FusionMaps.js"></SCRIPT>
 
  • Next , we create variables strXML (to store XML data) and sumdata to store sum of values from database.  We also started the root <map> element providing the map properties.
dim strXML, sumdata

'Generate the map element
'Create the opening <map> element and add the attributes that we need.
strXML = "<map showCanvasBorder='0' borderColor='FFFFFF' connectorColor='000000' fillAlpha='80' hoverColor='FFFFFF' showBevel='0' numberSuffix='%25 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 = strXML + "<colorRange>"
strXML = strXML + "<color minValue='0' maxValue='0.50' displayValue='0%25 to 0.50%25 of total' color='D64646' />"
strXML = strXML + "<color minValue='0.50' maxValue='1' displayValue='0.50%25 to 1%25 of total' color='F6BD0F' />"
strXML = strXML + "<color minValue='1' maxValue='3' displayValue='1%25 to 3%25 of total' color='8BBA00' />"
strXML = strXML + "<color minValue='3' maxValue='10' displayValue='3%25 or above of total' color='AFD8F8' />"
strXML = strXML + "</colorRange>
 
Providing data
 
  • 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 recordset rs1 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 recordset and create entity element for each state.
'Add the starting element for data
strXML = strXML & "<data>"

'Store sum of all data
if rs.bof=false then
  sumdata=rs("datap")
end if
...

'Generate <entity id=".." value=".." /> and also add link to it
'The link will in format Detailed.asp?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 &)
strXML = strXML & "<entity id='" & rs1("Internal_Id") & "' value='" & round(rs1("datap"),2) & "' link='Detailed.asp?Internal_Id=" & rs1("Internal_Id") & "%26map=" & rs2("map_swf") & "' />"

...
'Finally, close <map> element and add
strXML = strXML & "</data>"
      • Here we extract internalID from rs1("Internal_Id") and set to id attribute.
      • We get percentage of population of the state against total US population from round(rs1("datap"),2) and set to value attribute. We set the dacimal limit of the value to 2 places using ASP function 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.

...link='Detailed.asp?Internal_Id=" & rs1("Internal_Id") & "%26map=" & rs2("map_swf")...

  • All states/entities will drill-down to Detailed.asp 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 Detailed.asp page using querystirng variable -map. Note that another recordset rs2 is created for each iteration which stores the map file name of the releted Internal_Id in "map_swf"  field. Recordset rs2 is created from table fcmap_master using SQL query -"select * from fcmap_master where Internal_Id='" + rs1("Internal_Id") + ".
 
Thus all entities are created.
 
  • Next we add styles like XScale, YScale, alpha & shadow styles to the map using <styles> element.
  • Finally we call renderMap() function to render the map.
Call renderMap("../../Maps/FCMap_USA.swf", "", strXML, "Map1Id", "750", "460", false, false)
 
Here is the screenshot of the US map that we just created extracting data from database.