FusionMaps Documentation

FusionMaps with ASP > Plotting Data from Database > Drilldown Map

Each entity of the main US map on click drills down to Detailed.asp page passing its Internal id and map swf file name to it. In this section we will see the code in Detailed.asp and how the page handle these requests and renders map.

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.

The code of Detailed.asp file is as follows:

<%@ LANGUAGE="VBScript" %>


<%
'This page is invoked when the user clicks on the population distribution map
'contained in Index.asp. From there, we pass the internal id of the state and the
'SWF name of the map which represents that state.
'Here, we show more details on that particular state.
%>
<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>

...
<%
'Variables to store XML Data and
'strXML will be used to store the entire XML document generated
dim strXML

'strXML will be used to store the entire XML document generated
'Generate the chart element
strXML = strXML & "<map showCanvasBorder='0' borderColor='FFFFFF' connectorColor='000000' fillAlpha='80' hoverColor='FFFFFF' showBevel='0' legendBorderColor='F1f1f1' hoverColor='FFFFFF' legendPosition='bottom'>"
strXML = strXML & "<colorRange>"
strXML = strXML & "<color minValue='0' maxValue='93' displayValue='0%25 to 93%25' color='D64646' />"
strXML = strXML & "<color minValue='93' maxValue='94' displayValue='93%25 to 94%25' color='F6BD0F' />"
strXML = strXML & "<color minValue='94' maxValue='95' displayValue='94%25 to 95%25' color='8BBA00' />"
strXML = strXML & "<color minValue='95' maxValue='100' displayValue='95%25 or above' color='AFD8F8' />"
strXML = strXML & "</colorRange>"
'Start the <data> element
strXML = strXML & "<data>"

'Fetch all entity records
strQuery ="select a.Internal_Id,a.entity_id,sum(data) as datap from fcmap_distribution a group by a.Internal_Id,a.entity_id having a.Internal_Id='" & request("Internal_Id") & "'"

set rs = Server.CreateObject("ADODB.Recordset")
rs.open strQuery,oConn

'If we've records to iterate, proceed
if rs.bof=false then
do while not rs.eof
'Get details for the region
strQuery ="select a.Internal_Id,a.entity_id,b.group_name,sum(data) as datap from fcmap_distribution a, fcmap_group_master b where b.group_id=a.group_id group by a.Internal_Id ,a.entity_id, b.group_name having a.Internal_Id='" & rs("Internal_Id") & "' and entity_id='" & rs("entity_id") & "'"

set rs2 = Server.CreateObject("ADODB.Recordset")
rs2.open strQuery,oConn
'We'll create our custom tool text for each entity. So, create a place holder.
tooltext=""
'Total counter
totEmp=0
dim StateValue,TotalStateValue,StatePer
StateValue=0: TotalStateValue=0: StatePer=0
do while not rs2.eof
'StateValue stores the total population of each group
StateValue=cdbl(rs2("datap"))
'TotalStateValue stores the total population of each county/entity
TotalStateValue=cdbl(rs("datap"))
'Get percentage of populaiton of each group
StatePer=round((StateValue / TotalStateValue) * 100,2)

'Add to tool text having gorup name and group percentage
tooltext = tooltext & rs2("group_name") & ":" & StatePer & "%25 \n"
'If it's not unemployed group
if rs2("group_name") <> "Unemployed" then
totEmp = totEmp + StatePer
end if
rs2.movenext
loop

'Generate <entity id=".." value=".." />
'Also append link to Charts.asp passing all required information (from querystring) and the database
'We also add our custom tool text
strXML = strXML & "<entity id='" & rs("entity_id") & "' value='" & totEmp & "' link='Charts.asp?" & server.URLEncode(request.ServerVariables("QUERY_STRING")) & "%26entity_id=" & rs("entity_id") & "' tooltext='" & tooltext & "' />"
rs.movenext
loop
end if

'Finally, close <data> element
strXML = strXML & "</data>"
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>"
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/" & Request("map"), "", strXML, "Map1Id", "750", "460", false, false)
%>
</BODY>
</HTML>
<%
   set rs=nothing
   set rs1=nothing
   set rs2=nothing
   oConn.close
   set oConn=nothing
%>

Let's discuss the process flow of this page in details:

Like the previous page we include FusionMaps.asp, DBConn.asp and FusionMaps.js.
We also create a variable - strXML to store map XML.
Next we initialize root map element and add color range settings in XML.

Then using SQL we sum up the total population of each entity or county (represented by entity_id) of the selected state (represented by its Internal_Id).

"select a.Internal_Id,a.entity_id,sum(data) as datap from fcmap_distribution a   group by a.Internal_Id,a.entity_id having a.Internal_Id='" &   request("Internal_Id") & "'"

Now we iterate through each entity/county of the map/state and get details like percentage of employment in Business, in Service and percentage Unemployed population.
To achieve this, we create another recordset rs2 using the following SQL:

select a.Internal_Id,a.entity_id,b.group_name,sum(data) as datap from fcmap_distribution a, fcmap_group_master b where b.group_id=a.group_id  group by a.Internal_Id ,a.entity_id, b.group_name having a.Internal_Id='" & rs("Internal_Id")  &  "' and entity_id='" & rs("entity_id") & "'"

Here, rs2 stores records each having total of each group - Business, Service and Unemployed - in each record. So iterating through each, we calculate percentage of employment of each group and total employed population. This we did using the following calculation:

do while not rs2.eof 

         'StateValue stores the total population of each group

         StateValue=cdbl(rs2("datap"))

         'TotalStateValue stores the total population of each county/entity

         TotalStateValue=cdbl(rs("datap"))

   'Get percentage of populaiton of each group 

    StatePer=round((StateValue /  TotalStateValue) * 100,2)

   'Add to tool text having gorup name and group percentage 

   tooltext = tooltext & rs2("group_name") & ":" & StatePer & "%25 \n"

'If it's not unemployed group

if rs2("group_name") <> "Unemployed" then

totEmp = totEmp + StatePer

   end if

rs2.movenext

loop

The above code also shows that we also generate custom tool-text for each entity to show Percentage of population in Business, Service and Percentage of Unemployed population for each district/entity of that state's map. We create a variable - tooltext and accumulate group name and corresponding percentage of each group in it.
Moreover we also keep a total of employment percentage in totEmp variable. This we did by skipping "Unemployed" group.

Providing map data

Let's now discuss how to provide data to each enity of the map. In the above outer iteration through each entity we also add our code to add <entity> element.

'Start the <data> element

      strXML =   strXML & "<data>"

      ...

      'Generate <entity id=".." value=".." /> 

      'Also append link to Charts.asp passing all required information 

      '(from querystring) and the database

      'We also add our custom tool text

        strXML = strXML & "<entity id='" & rs("entity_id") & "' value='" & totEmp & "' link='Charts.asp?" & server.URLEncode(request.ServerVariables("QUERY_STRING")) & "%26entity_id=" & rs("entity_id") & "' tooltext='" & tooltext & "' />"

    ...

    'Finally, close <data> element

    strXML =   strXML & "</data>"

As entity id we use the "entity_id" field from the first recordset - rs.
We give total employed percent stored in totEmp as value attribute of each entity.
We set tooltext attribute from tooltext variable.
We also create links that drill-down to another page showing details of the clicked district/entity in charts.

Thus, we create entity data.

Finally we apply style like XScale, YScale, alpha & shadow styles to the map using <styles> element and
Render the map using renderMap() function from FusionMas.asp.
Request("map") gives the map swf name.

'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/" & Request("map"), "", strXML, "Map1Id", "750", "460", false, false) 

Here is the snapshot of the detailed county map: