Sponsored Links

Creating a Virtual Earth Map from MySQL PDF Print E-mail
Written by Richard Marsden   
Monday, 26 January 2009 10:02

This article demonstrates how to create a relatively simple Virtual Earth map application using data read from a MySQL database. The data is read using PHP, and JavaScript is used to implement some client-side functionality.

The application was created for the University of Dallas' Biology Department to plot student field data. This field data consists of sample points that attempt to determine if the point is a wetland or not. The determination is based on three indicators: hydrology, vegetation, and soil. Two of these indicators have to indicate a wetland for the point to be determined a wetland. The application allows all three indicators and the final wetland determination to be displayed as individual layers. Individual student teams can also be displayed, as well as the entire dataset.

The finished application can be found at http://www.biomarcy.com/wetlands/map.php.  Here is a screen shot of the wetland layer with all teams displayed:

Screenshot of the Biology Dept Wetland Map

This is actually a very simple application. Although it uses JavaScript, it does not use AJAX to transfer the pushpins to the client. Instead, PHP reads the data points from MySQL and creates the JavaScript required to plot the pushpins. This is effective for a small application like this, but it is not scalable. An application with more data points should probably use AJAX with XML or JSON to transfer the pushpin data on demand.

The display application is implemented as one large PHP file. Here is the header:

  1 
  2 
  3 
  4 
  5 
  6 
  7 
  8 

  9 
 10 


 11 
 12 
 13 
 14 

 15 
 16 
 17 
 18 
 19 
 20 
<? include '/your_path/wetland_config.php' ?>

<!doctype html public "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
  <head>
    <meta http-equiv="content-type" content="application/xhtml+xml; 
      charset=utf-8" />
    <meta name="author" content="Winwaed Software Technology LLC" />
    <link rel="stylesheet" type="text/css" 
      href="http://www.biomarcy.com/wetlands/wetland.css" title="wetland 
      stylesheet"/>
    <title><? echo $title?>: Map of Field Data</title>


<script type="text/javascript" 
  src="http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2"></script>

<script type="text/javascript">
var map = null;
var layerWetland, layerHydro, layerVeg, layerSoil;
var iconSpecNull, iconSpecWet, iconSpecDry;

 

Most of this should be self explanatory HTML header boiler plate. Of note is the wetland_config.php include. This includes a number of configuration parameters which should not be stored in a public area. The most important of these are $dbserver, $dbuser, and $dbpassword which store the information required to open the MySQL database. $title is also defined here for convenience.

After the header boiler plate, we include the Virtual Earth control and then start the main JavaScript definition. This definition starts with a few global variables. These refer to the Virtual Earth map object, individual map layers, and icon specifications. We only use three icons but we use them a lot. Therefore it is a good idea to keep global references to them.

Next we define some JavaScript callbacks. The wetland determination setting and each indicator all have their own layer. The user can select which to be displayed by pressing one of the layer selection buttons. These buttons call these callbacks to switch the required layer on, and all other layers off:

  1 
  2 
  3 
  4 
  5 
  6 
  7 
  8 
  9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 27 
 28 
 29 
 30 
// Layer-changing call backs

function ShowWetland()
{
  map.HideAllShapeLayers();
  layerWetland.Show();
  document.getElementById('layer_indicator').innerHTML="Wetland";
}

function ShowHydrology()
{
  map.HideAllShapeLayers();
  layerHydro.Show();
  document.getElementById('layer_indicator').innerHTML="Hydrology";
}

function ShowVegetation()
{
  map.HideAllShapeLayers();
  layerVeg.Show();
  document.getElementById('layer_indicator').innerHTML="Vegetation";
}

function ShowSoil()
{
  map.HideAllShapeLayers();
  layerSoil.Show();
  document.getElementById('layer_indicator').innerHTML="Soil";
}

 

Next we start to create the Virtual Earth map. We create a new map object and set the map style and position. Then we create the four data layers, and the three icon specifications. These are all global variables. Here is the code:

  1 
  2 
  3 
  4 
  5 
  6 
  7 
  8 
  9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 27 
 28 
 29 
 30 
 31 
 32 
 33 
 34 
 35 
 36 
 37 
 38 
 39 
// this creates the actual map object
function GetMap()
{
 // create the main map object
 map = new VEMap('myMap');
 map.LoadMap();
 map.SetCenterAndZoom( new VELatLong(32.8488, -96.9180), 18 );
 map.SetMapStyle(VEMapStyle.Aerial);

 // Create the layers
 layerWetland = new VEShapeLayer();
 layerWetland.SetTitle("Wetland Determinations");
 map.AddShapeLayer(layerWetland);
 layerHydro = new VEShapeLayer();
 layerHydro.SetTitle("Hydrology Determinations");
 map.AddShapeLayer(layerHydro);
 layerVeg = new VEShapeLayer();
 layerVeg.SetTitle("Vegetation Determinations");
 map.AddShapeLayer(layerVeg);
 layerSoil = new VEShapeLayer();
 layerSoil.SetTitle("Soil Determinations");
 map.AddShapeLayer(layerSoil);

 var shape;


 // Create the icon styles (we create only three and re-use them as necessary)
 iconSpecNull = new VECustomIconSpecification();
 iconSpecNull.Image = "white.gif"; 

 iconSpecWet = new VECustomIconSpecification();
 iconSpecWet.Image = "blue.gif"; 
 
 iconSpecDry = new VECustomIconSpecification();
 iconSpecDry.Image = "yellow.gif"; 


var iconSpec;

 

We are now ready to dive into the PHP to fetch the data. Our first PHP function create_dropdown is called to create the HTML for a dropdown box that allows the team to be selected. The dropdown box will be created in a form that calls the map page with the required team as a parameter. The parameter $pairs specifies the team names and their symbols. The symbol is a character ('0' or a letter of the alphabet) that is used as an identifier here.

After this definition, we open the database (winwaed_wetland) and read the team names and symbols. If a team was requested in the URL parameter 'team', then this is checked against the available teams. The default is "All Teams" with symbol "0". Here is the code:

  1 
  2 
  3 
  4 
  5 
  6 
  7 
  8 
  9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 27 
 28 
 29 
 30 
 31 
 32 
 33 
 34 
 35 
 36 
 37 
 38 
 39 
 40 
 41 
 42 
 43 
 44 
 45 
 46 
 47 
 48 
 49 
 50 
 51 
 52 
 53 
 54 
 55 
 56 
 57 
 58 
 59 
 60 
 61 
 62 
 63 
 64 
 65 
 66 
 67 
 68 
 69 
<?
// The following PHP adds the pushpins



// Create the team selection combo box

function create_dropdown($identifier$pairs$firstentry$multiple="")
{

  
$dropdown "<select name=\"$identifier\" ";
//  $dropdown .= "<option name\"\">$firstentry</option>";

  // Add "all teams" - indicated by a '0' (zero)
  
$dropdown .= "<option value=\"0\">All Teams</option>";

  
// Add the full list
  
if (count($pairs) > 0)
  { 
    foreach (
$pairs AS $value => $name)
    {
      
$dropdown .= "<option value=\"$value\">$name</option>";
    }
  }
  
$dropdown .= "</select>";
  return 
$dropdown;
}



// Open the database

$conn mysql_connect($dbserver,$dbuser,$dbpassword
     or die(
"Cannot connect to the database.");
mysql_select_db("winwaed_wetland")
     or die(
"Cannot select the database.");

// First get a list of teams
// These are used in the team combo box and to verify team selection

$query "SELECT team, symbol FROM teams WHERE year=$year ORDER BY symbol";
$result mysql_query($query);

while (
$row=mysql_fetch_array($result))
{
  
$value $row["symbol"];
  
$name $row["symbol"].", ".$row["team"];
  
$pairs[$value] = $name;
}



// Extract requested team. Default to All (0) if invalid or none
$this_teamsymbol "0";
$sym trim($_POST['team']);

if (
strlen($sym) >=1)
{
  
$this_teamsymbol strtoupper$sym{0} );
}

$team_caption "All Teams";
if (
$this_teamsymbol !='0' )
{
  if (! 
array_key_exists$this_teamsymbol$pairs) )
     
$this_teamsymbol '0';
  
$team_caption $pairs$this_teamsymbol ];
}

 

Now that we have the team data, we can get the required data points. Two SQL statements are available according to whether we are selecting all teams or just one team. We then loop over each data row extracting the position, and results. A caption ($desc) is created from the field data and teams:

  1 
  2 
  3 
  4 
  5 
  6 


  7 
  8 
  9 
 10 


 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 27 
 28 
 29 
 30 
 31 
 32 
 33 
 34 
 35 
 36 
 37 
 38 
 39 
 40 
 41 
 42 
 43 
 44 
 45 
 46 
 47 
 48 
 49 
 50 
 51 
 52 
 53 
 54 
 55 
 56 
 57 
// Get the data points and construct the Javascript Pushpin code

$query = " ";
if ($this_teamsymbol =='0' )
{
$query = "SELECT datapoints.*, teams.symbol FROM datapoints, teams WHERE 
  datapoints.year=$year AND datapoints.year=teams.year AND 
  datapoints.team=teams.team";
}
else
{
$query = "SELECT datapoints.*, teams.symbol FROM datapoints, teams WHERE 
  datapoints.year=$year AND datapoints.year=teams.year AND 
  datapoints.team=teams.team AND teams.symbol='$this_teamsymbol'";
}


$result = mysql_query($query);

while ($row=mysql_fetch_array($result))
{
$pointid = $row["pointid"];
$team = $row["team"];
$symbol = $row["symbol"];
$longitude = $row["longitude"];
$latitude = $row["latitude"];
$havehydro = $row["havehydro"];
$haveveg = $row["haveveg"];
$havesoil = $row["havesoil"];
$havewetland = $row["havewetland"];

$hydrology = $row["hydrology"];
$soil = $row["soil"];
$vegetation = $row["vegetation"];
$wetland = $row["wetland"];

$species = trim($row["species"]);
$soil_color = trim($row["soil_color"]);
$soil_texture = trim($row["soil_texture"]);


$desc = "";
if ($havewetland)
  $desc = "Wetland: ".( $wetland==1 ? "YES" : "NO")."<br/>";
if ($havehydro)
  $desc = $desc."Hydrology: ".( $hydrology==1 ? "YES" : "NO")."<br/>";
if ($havesoil)
{
  $desc = $desc."Soil: ".( $soil==1 ? "YES" : "NO")."<br/>";
  if (strlen($soil_color)>0)
      $desc = $desc."Soil Color: ".$soil_color."<br/>";
  if (strlen($soil_texture)>0)
      $desc = $desc."Soil Texture: ".$soil_texture."<br/>";
}
if ($haveveg)
{
  $desc = $desc."Vegetation: ".( $veg==1 ? "YES" : "NO")."<br/>";
  if (strlen($species)>0)
      $desc = $desc."Dominant Species: ".$species."<br/>";
}


We are now ready to create the JavaScript that actually plots the pushpins on the map. We create a pushpin shape for each layer. These have identical captions, but have different symbols according to their respective wetland indication. There are three options: wetland (blue circle), not a wetland (yellow circle), and insufficient data (white circle). Here is the code, including the closing '}' of the data loop:

  1 
  2 
  3 
  4 

  5 
  6 
  7 
  8 
  9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 

 22 
 23 
 24 
 25 
 26 
 27 
 28 
 29 
 30 
 31 
 32 
 33 
 34 
 35 
 36 
 37 
 38 
 39 
 40 

 41 
 42 
 43 
 44 
 45 
 46 
 47 
 48 
 49 
 50 
 51 
 52 
 53 
 54 
 55 
 56 
 57 
 58 
 59 

 60 
 61 
 62 
 63 
 64 
 65 
 66 
 67 
 68 
 69 
 70 
 71 
 72 
////////////////
// wetland layer

echo "shape = new VEShape(VEShapeType.Pushpin, new 
  VELatLong($latitude,$longitude));\n";
echo "shape.SetTitle(\"Team: $symbol $team, Point: $pointid\");\n";
echo "shape.SetDescription(\"".$desc."\");\n";

if (! $havewetland)
     echo "shape.SetCustomIcon(iconSpecNull);\n";
else if ($wetland)
     echo "shape.SetCustomIcon(iconSpecWet);\n";
else
     echo "shape.SetCustomIcon(iconSpecDry);\n";

echo "layerWetland.AddShape(shape);\n";


////////////
// Hydrology

echo "shape = new VEShape(VEShapeType.Pushpin, new 
  VELatLong($latitude,$longitude));\n";
echo "shape.SetTitle(\"Team: $symbol $team, Point: $pointid\");\n";
echo "shape.SetDescription(\"".$desc."\");\n";

if (! $havehydro)
     echo "shape.SetCustomIcon(iconSpecNull);\n";
else if ($hydrology)
     echo "shape.SetCustomIcon(iconSpecWet);\n";
else
     echo "shape.SetCustomIcon(iconSpecDry);\n";

echo "layerHydro.AddShape(shape);\n";




////////////
// Vegetation

echo "shape = new VEShape(VEShapeType.Pushpin, new 
  VELatLong($latitude,$longitude));\n";
echo "shape.SetTitle(\"Team: $symbol $team, Point: $pointid\");\n";
echo "shape.SetDescription(\"".$desc."\");\n";

if (! $haveveg)
     echo "shape.SetCustomIcon(iconSpecNull);\n";
else if ($vegetation)
     echo "shape.SetCustomIcon(iconSpecWet);\n";
else
     echo "shape.SetCustomIcon(iconSpecDry);\n";

echo "layerVeg.AddShape(shape);\n";




////////////
// Soil

echo "shape = new VEShape(VEShapeType.Pushpin, new 
  VELatLong($latitude,$longitude));\n";
echo "shape.SetTitle(\"Team: $symbol $team, Point: $pointid\");\n";
echo "shape.SetDescription(\"".$desc."\");\n";

if (! $havesoil)
     echo "shape.SetCustomIcon(iconSpecNull);\n";
else if ($soil)
     echo "shape.SetCustomIcon(iconSpecWet);\n";
else
     echo "shape.SetCustomIcon(iconSpecDry);\n";

echo "layerSoil.AddShape(shape);\n";

}

 

Next we tidy things up by closing the database, close the PHP section, set the layer visibility (ShowWetland), finish the JavaScript, and the HTML header:

  1 
  2 
  3 
  4 
  5 
  6 
  7 
  8 
  9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
mysql_close();

?>


// continue with the end of the GetMap() method

   // Set the layers to only show the wetland to start with
   ShowWetland();
}


// end of Javascript

</script>

</head>

 

After closing the HTML header, we finally come to the HTML body:

  1 
  2 
  3 
  4 
  5 
  6 
  7 

  8 
  9 
 10 
 11 
 12 
 13 
 14 

 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 

 25 

 26 

 27 
 28 
 29 
 30 
 31 
 32 
 33 
 34 
 35 
 36 
 37 
 38 
 39 
 40 
 41 
 42 
 43 
<!-- here's the main body!-->
<body onload="GetMap();">

<h1><?echo $title?>: Map of Field Data</h1>


<div id="myMap" style="position:relative; top:0px; width:750px; height:500px;">
  </div>


<p>Current team: <b><? echo $team_caption?></b><br/>
Current layer: <b><span id="layer_indicator">(none)</span></b>
<span style="padding-left:40px;">&​nbsp;</span> Key:&​nbsp;&​nbsp;&​nbsp;
<img src="/blue.gif" style="width:16px;height:16px"/>Wetland&​nbsp;&​nbsp;&​nbsp;
<img src="/yellow.gif" style="width:16px;height:16px"/>Not a wetland&​nbsp;&​nbsp;
  &​nbsp;
<img src="/white.gif" style="width:16px;height:16px"/>Insufficient data
</p>


<p>Available Layers:
</p>

<table>
<tr>
<td><input type="button" name="wetlands" value="Wetland" onclick="ShowWetland()"
  /></td>
<td><input type="button" name="hydro" value="Hydrology" 
  onclick="ShowHydrology()" /></td>
<td><input type="button" name="vegetation" value="Vegetation" 
  onclick="ShowVegetation()" /></td>
<td><input type="button" name="soil" value="Soil" onclick="ShowSoil()" /></td>
</tr>
</table>


<form action="map.php" method="post">
<p>Select a specific team to display:
<?
echo create_dropdown("team"$pairs"Select a specific team:");
?>
<input type="submit" name="Select" value="Display" />
</p>
</form>

</body>
</html>

 

After the title, the main map div myMap is defined. This is followed by a key defined in HTML, and the layer-selection buttons. Finally, a small form includes a drop down list of the available teams created using the PHP function create_dropdown that was defined above. When the user selects a new team, this form simply calls the same page but with the team's symbol (identifier) as a POST parameter.

Although this example is quite long, you can see that in reality it is fairly simple. PHP reads data from a MySQL database. This is used to create JavaScript code which, in turn, is plotted on the map. Data selection uses two different methods: client level layer selection, and server-side team selection. It might be tempting to have used layer selection for the teams, but this would have resulted in a large number of layers. A better alternative which would scale better, would be to use XML to JSON to transfer the pushpin data.


Comments (2)Add Comment
0
me
written by Jim, December 05, 2009
Hi there, this seems to be a great example. What does the database look like? I have figured out everything else - but can't really see - or reverse out what all the fields are - is there a blob carrying the gif fiename? I am a newb, but try to work through these to learn some database/GIS stuff.

Thanks for any help!
Jim
62
Database
written by Richard Marsden, December 07, 2009
The exact database structure will depend on the data you have. This particular application had a lot of field data parameters (many were boolean) which are beyond the scope of the article. The point is these can be used to determine how the points should be plotted.

Two tables are used because we have "teams" (groups of locations). The team table has a 'symbol' (one character abbreviation), name (text), and year (the database supports student data from successive years merely because I believe it should never be thrown away!). The second table stores the actual data. This requires a point identifier (text or number could be used), and longitude, latitude coordinates. The coordinates are double precision floating point. Other fields are application specific (eg. team, year), plus the parameters used to choose each icon (hydrology,etc). These should be changed to suit your application.

Write comment

security code
Write the displayed characters


busy
Last Updated on Monday, 26 January 2009 10:04