FusionMaps Documentation

FusionMaps with PHP > Plotting data from Database > Drilldown Map

Each entity of the main US map on click drills-down to Detailed.php page passing its Internal id and map swf file name to it. In this section we will see how we make Detaild.php handle these requests and render proper 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 > PHP > DB_Drill folder.

The code of Detailed.php file is as follows:

<?php
/*
This page is invoked when the user clicks on the population distribution map
contained in Default.php. 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.
*/
include("../Includes/DBConn.php");
# include Fusionmaps Rendering Control file
include("../Includes/FusionMaps.php");
?>

<HTML>
<HEAD>
<TITLE>FusionMaps v3 - Database Example</TITLE>
<SCRIPT LANGUAGE="Javascript" SRC="../JSClass/FusionMaps.js"></SCRIPT>

<?php

# Connect to the DB
$link = connectToDB();

# Variables to store XML Data and sum of data
# $strXML will be used to store the entire XML document generated
# Generate the Map element

$strXML = "<map showCanvasBorder='0' borderColor='FFFFFF' connectorColor='000000' fillAlpha='80' hoverColor='FFFFFF' showBevel='0' legendBorderColor='F1f1f1' hoverColor='FFFFFF' legendPosition='bottom'>";
$strXML .= "<colorRange>";
$strXML .= "<color minValue='0' maxValue='93' displayValue='0%25 to 93%25' color='D64646' />";
$strXML .= "<color minValue='93' maxValue='94' displayValue='93%25 to 94%25' color='F6BD0F' />";
$strXML .= "<color minValue='94' maxValue='95' displayValue='94%25 to 95%25' color='8BBA00' />";
$strXML .= "<color minValue='95' maxValue='100' displayValue='95%25 or above' color='AFD8F8' />";
$strXML .= "</colorRange>";

$strXML .="<data>";

# Fetch all entity records

$strQuery ="select a.Internal_Id,a.entity_id,sum(data) datap from fcmap_distribution a group by a.Internal_Id,a.entity_id having a.Internal_Id='" . $_REQUEST['Internal_Id'] . "'";

$result = mysql_query($strQuery) or die(mysql_error());

# If we've records to iterate, proceed
if ($result) {
while($rs = mysql_fetch_array($result)) {
# Get details for the region
$strQuery ="select a.Internal_Id,a.entity_id,b.group_name,sum(data) 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'] . "'";

# We'll create our custom tool text for each entity. So, create a place holder.
$tooltext="";
# Total counter
$totEmp=0;
$result2 = mysql_query($strQuery) or die(mysql_error());
while($rs2 = mysql_fetch_array($result2)){
# Calucation value, Get percentage of employment
# Add to tool text
$tooltext .= $rs2['group_name'] . ":" . round((($rs2['datap'] / $rs['datap']) * 100),2) . "%25 \\n";
# If it's not unemployed group
if ($rs2['group_name']!='Unemployed'){
$totEmp += round((($rs2['datap'] / $rs['datap']) * 100),2);
}
}

# Generate <entity id=".." value=".." />
# Also append link to Charts.php passing all required information (from querystring) and the database
# We also add our custom tool text

$strXML .= "<entity id='" . $rs['entity_id'] . "' value='" . $totEmp . "' link='Charts.php?" . urlencode($QUERY_STRING) . "%26entity_id=" . $rs['entity_id'] . "' tooltext='" . $tooltext . "' />";

}
}
mysql_close($link);

# Finally, close <data> element
$strXML .= "</data>";

# Adding styles element
$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 .= "</definition><application><apply toObject='PLOT' styles='animX,animY' /><apply toObject='LABELS' styles='myShadow,animAlpha' /></application></styles>";

# Closing map element
$strXML .="</map>";

# Now, render the map using renderMap function present in FusionMaps.php (include file)
# Also, since we're using dataXML method, we provide a "" value for dataURL here

print renderMap("../../Maps/" . $_REQUEST['map'] ,"",$strXML,"Maps", 750, 460,0,0);

?>

</BODY>
</HTML>

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

Like the previous page we include FusionMaps.php, DBConn.php and FusionMaps.js.
We also create a variable - strXML to store map XML.
We connect to database using connectToDB() function.
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)

$strQuery ="select  a.Internal_Id,a.entity_id,sum(data) 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 array-recordset $rs2 using the following SQL query:

select a.Internal_Id,a.entity_id,b.group_name,sum(data) 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 having total of each group - Business, Service and Unemployed. So iterating through each, we calculate percentage of employment of each group and total employed population. This we did using the following calculation:

while($rs2 = mysql_fetch_array($result2)){

      # Calculation value, Get percentage of employment

# Add to tool text

$tooltext .=  $rs2['group_name'] . ":" . round((($rs2['datap'] /  $rs['datap']) * 100),2) . "%25 \\n";

# If it's not unemployed group do not add it. 

if ($rs2['group_name']!='Unemployed'){

$totEmp += round((($rs2['datap'] /  $rs['datap']) * 100),2);

}

}

In the above code 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 .= "<data>";

...

# Generate <entity id=".." value=".." />
# Also append link to Charts.php passing all required information (from querystring) and the database
# We also add our custom tool text

$strXML .= "<entity id='" . $rs['entity_id'] . "' value='" . $totEmp . "' link='Charts.php?" . urlencode($QUERY_STRING) . "%26entity_id=" . $rs['entity_id'] . "' tooltext='" . $tooltext . "' />";

}
}
mysql_close($link);

# Finally, close <data> element
$strXML .= "</data>";

As entity id we use the "entity_id" field from the first array-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.php.
$_REQUEST["map"] gives the map swf name.

print renderMap("../../Maps/" . $_REQUEST['map'] ,"",$strXML,"Maps", 750, 460,0,0);

Here is the spanshot of the drilled-down map