FusionMaps v3 Documentation

FusionMaps with PHP > 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 > PHP > DB_Drill folder.

Let's discuss the code from the main file : Default.php

<?php
# include file to open database connection
include("../Includes/DBConn.php");
# include Fusionmaps Rendering Control file
include("../Includes/FusionMaps.php");
?>
<HTML>
<HEAD>
<TITLE>FusionMaps v3 - Database Example</TITLE>
<?php
/*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>

<?php
/*
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
*/
$strXML ="";
$sumData =0;

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

#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 .= "<colorRange>";
$strXML .= "<color minValue='0' maxValue='0.50' displayValue='0%25 to 0.50%25 of total' color='D64646' />";
$strXML .= "<color minValue='0.50' maxValue='1' displayValue='0.50%25 to 1%25 of total' color='F6BD0F' />";
$strXML .= "<color minValue='1' maxValue='3' displayValue='1%25 to 3%25 of total' color='8BBA00' />";
$strXML .= "<color minValue='3' maxValue='10' displayValue='3%25 or above of total' color='AFD8F8' />";
$strXML .= "</colorRange>";

# Add the starting element for data
$strXML .= "<data>";

# Fetch all data now
# Initialize sum container
$strQuery = "select sum(data) datap from fcmap_distribution";
$result3 = mysql_query($strQuery) or die(mysql_error());
$rs = mysql_fetch_array($result3);

#extract total sum
if(count($rs)!=0)
$sumdata=$rs['datap'];

# Fetch all Internal id and data sum
$strQuery = "select Internal_Id, sum(data) datap from fcmap_distribution group by Internal_Id";
$result = mysql_query($strQuery) or die(mysql_error());

# Check if we've records to show
if ($result) {
# Iterate through each record
while($rs1 = mysql_fetch_array($result)) {

$strQuery ="select * from fcmap_master where Internal_Id='" . $rs1['Internal_Id'] . "'";
$result1 = mysql_query($strQuery) or die(mysql_error());
$rs2 = mysql_fetch_array($result1);

# Generate <entity id=".." value=".." /> , calculating sum and Percentage data
# and also add link to it
# The link will in format Detailed.php?Internal_Id=Int_Id&map=map_swf.swf -
# we'll need to URL Encode this link to convert & to %26

$strXML .= "<entity id='" . $rs1['Internal_Id'] . "' value='" . round((($rs1['datap'] / $sumdata) * 100),2) . "' link='Detailed.php?Internal_Id=" . $rs1['Internal_Id'] . "%26map=" . $rs2['map_swf'] . "' />";
}

# Finally, close <data> element and add
$strXML .= "</data>";
# If needed, you can append additional XML tags here - like STYLE or MARKERS

$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>";

# Finally, close <map> element and add
$strXML .="</map>";

# Finally Rendering the USA Maps with renderMap() php function present in FusionMaps.php
# Also, since we're using dataXML method, we provide a "" value for dataURL here

print renderMap("../../Maps/FCMap_USA.swf","",$strXML,"UsaMap", 750, 460,0,0);

}
else{
# Else, display a message that we do not have any records to display
print '<h3>No Records</h3>';
}
mysql_close($link);

?>

</BODY>
</HTML>

Let's now see through the lines:

As always, first we include FusionMaps.php and FusionMaps.js files that help in easy map rendering.

We then include DBConn.php, which contains connection function connectToDB() to connect to database.

<?php 

      # include file to open database connection 

include("../Includes/DBConn.php"); 

# include Fusionmaps Rendering Control file 

include("../Includes/FusionMaps.php"); 

?>

...

<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 connect to the database using connectToDB() function.
We also start the root <map> element providing the map properties.

$strXML ="";

$sumData =0;

# Connect to the DB

$link = connectToDB();

#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 USA map.

#Define our color range

$strXML .= "<colorRange>";

$strXML .= "<color minValue='0' maxValue='0.50' displayValue='0%25 to 0.50%25 of total' color='D64646' />";

$strXML .= "<color minValue='0.50' maxValue='1' displayValue='0.50%25 to 1%25 of total' color='F6BD0F' />";

$strXML .= "<color minValue='1' maxValue='3' displayValue='1%25 to 3%25 of total' color='8BBA00' />";

$strXML .= "<color minValue='3' maxValue='10' displayValue='3%25 or above of total' color='AFD8F8' />";

$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). This we did using SQL Query : "select sum(data) as datap from fcmap_distribution". We store the result in array $rs and access the value from it using $rs['datap'].
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 array $rs1 using the SQL query - "select Internal_Id, sum(data) datap from fcmap_distribution group by Internal_Id". We again use fcmap_distribution table here.
Iterating through each record stored in array 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 array and create entity element for each state.

# Add the starting element for data
$strXML .= "<data>";

#extract total sum
if(count($rs)!=0)
$sumdata=$rs['datap'];
...

# Generate <entity id=".." value=".." /> and also add link to it
# The link will in format Detailed.php?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 .= "<entity id='" . $rs1['Internal_Id'] . "' value='" . round((($rs1['datap'] / $sumdata) * 100),2) . "' link='Detailed.php?Internal_Id=" . $rs1['Internal_Id'] . "%26map=" . $rs2['map_swf'] . "' />";

}

# Finally, close <data> element and add
$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'] / $sumdata) * 100),2) and set to value attribute. We set the dacimal limit of the value to 2 places usingPHP 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 php file. This we did in the entity entry itself as shown above using link attribute. Lets 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.php 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 array $rs2 is created for each iteration which stores the map file name of the releted Internal_Id in "map_swf" field. array $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.

print renderMap("../../Maps/FCMap_USA.swf","",$strXML,"UsaMap", 750, 460,0,0);

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