How To Feed Mysql Data Into Google Map

Submitted by thusitcp on Tuesday, October 14, 2014 - 07:38.

Language

JavaScript

Modem day client server applications support lot of mobile device platform so real time GPS data will be collect most of the applications. Projection of collected data visualized on GIS applications has big trend these types of applications. In this tutorial I am going to teach you how to visualize data on google map using mysql databases. For a tutorial I am going to create online event publishing application as our sample. In this case people set of data containing some information about events and some of them have completed records and some of them have some data only. At first I would like to show you snap shot of what we are going to create Google map

Now let’s move forward. Here is our data base table structure

CREATE TABLE IF NOT EXISTS `geo_location` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `event_title` VARCHAR(50) NOT NULL,
  `location_address` VARCHAR(150) NOT NULL,
  `location_city` VARCHAR(75) NOT NULL,
  `location_state` VARCHAR(75) NOT NULL,
  `zip_code` VARCHAR(20) NOT NULL,
  `country` VARCHAR(50) NOT NULL,
  `lat` VARCHAR(20) NOT NULL,
  `lang` VARCHAR(20) NOT NULL,
  `location_discription` VARCHAR(200) NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  `lcation_url` VARCHAR(150) NOT NULL,
  `locaton_type` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
)

Then we are going to insert few records to above table

INSERT INTO `geo_location` (`id`, `event_title`, `location_address`, `location_city`, `location_state`, `zip_code`, `country`, `lat`, `lang`, `location_discription`, `start_time`, `end_time`, `lcation_url`, `locaton_type`) VALUES
(1, 'Test Event One', '140 Old Kent Road, London SE1, UK', 'test location', 'london', 'Nw1 w42', 'United Kindom', '51.49164465653034', '-0.0823974609375', 'Al marj Cinema center test data ', '0000-00-00 00:00:00', '0000-00-00 00:00:00', 'http://www.geoshaper.com/Reupload/', 'restaurant'),
(2, 'Test Event Two', '16 Pine Coombe, Croydon, Greater London CR0 5HS, UK', 'test location two', 'london', 'Nw1 w42', 'United Kindom', '51.36492148825955', '-0.0439453125', 'Test data description two ', '0000-00-00 00:00:00', '0000-00-00 00:00:00', 'http://www.geoshaper.com/Reupload/', 'restaurant'),
(3, 'Test even 3', 'Steinau 1, 8844 Einsiedeln, Switzerland', 'swiss test ', 'Zurich zug', '215466', 'Awitzerland', '47.08508535995383', '8.778076171875', 'Al marj Cinema center test data ', '0000-00-00 00:00:00', '0000-00-00 00:00:00', 'www.geoshaper.com', 'restaurant'),
(4, 'London tree', '16 Choats Road, Barking, Dagenham, Greater London RM9 6LF, UK', 'test location', 'london', 'Nw1 w42', 'United Kindom', '51.52241608253253', '0.1318359375', 'Al marj Cinema center test data ', '0000-00-00 00:00:00', '0000-00-00 00:00:00', 'www.geoshaper.com', 'bar'),
(5, 'Swiz event two', 'Alte Winterthurerstrasse 51A, 8304 Wallisellen, Switzerland', 'swiss test ', 'london', '215466', 'switzerland', '47.416937456635445', '8.59130859375', 'Al marj Cinema center test data ', '0000-00-00 00:00:00', '0000-00-00 00:00:00', 'www.geoshaper.com', 'restaurant'),
(6, '', 'TQ3 3ST', '', '', '', '', '51.95780738871554', '-2.887207567691803', '', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '', 'restaurant');

Then we are going to read records from mysql database and convert to xml format which is help to feed data into goggle map faster. Have you noted that we have some incomplete records so we have to do visualization as some kind of complete data. As a example if we don’t have lat/ lng values then we need to find out lat /lng using location address. Other hand if we don’t have address we should able to find out using lat/ lng values. This function is used to get lat /lng values from address

function getLatLangbyAddress($adress,$location,$zip)
{
    $address = '';
    $compornent ='components=country:.CH';
 
     if (isset($adress) && $adress !="" ){
        $address = $adress;
     }
 else {
 
     if(isset($location) && $location !="" ){
        $address = $address .",".$location;
        $compornent = $compornent.'| $compornent =locality:'.$location;
     }
 }
 
      if (isset($zip) && $zip !="" ){
        $address = $adress .",".$zip;
         $compornent = $compornent.'|$compornent =postal_code:'.$zip;
     }
$a = urlencode($address);
$b = urlencode($compornent);
   $geocodeURL = 
   "http://maps.googleapis.com/maps/api/geocode/json?address=$a&$b&sensor=false";
   $ch = curl_init($geocodeURL);
   curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
   $result = curl_exec($ch);
   $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
   curl_close($ch);
   if ($httpCode == 200) {
      $geocode = json_decode($result);
     return $geocode; 
   } else {
     $geo_status = "HTTP_FAIL_$httpCode";
   } 
}

This function is used to get address information using lat/ lng values

function getAdressbyLatLang($lat,$lang)
{
    $address = '';
   $address = $lat.",".$lang;
$a = urlencode($address);
   $geocodeURL = 
   "http://maps.googleapis.com/maps/api/geocode/json?latlng=$a&sensor=false";
   $ch = curl_init($geocodeURL);
   curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
   $result = curl_exec($ch);
   $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
   curl_close($ch);
   if ($httpCode == 200) {
      $geocode = json_decode($result);
     return $geocode;
   } else {
     $geo_status = "HTTP_FAIL_$httpCode";
   }
}

Before we are going to read records from data base we need to consider address field. If we get data from google map , values might be contained some special characters. So we need to clean it out. Our input cleaning method look like follows

function parseToXML($htmlStr) 
{ 
$xmlStr=str_replace('<','&lt;',$htmlStr); 
$xmlStr=str_replace('>','&gt;',$xmlStr); 
$xmlStr=str_replace('"','&quot;',$xmlStr); 
$xmlStr=str_replace("'",'&#39;',$xmlStr); 
$xmlStr=str_replace("&",'&amp;',$xmlStr); 
return $xmlStr; 
}

Lets put all together and read data from databse and convert them to XML

// Opens a connection to a MySQL server
$connection=mysql_connect ($localhost, $username, $password);
if (!$connection) {
  die('Not connected : ' . mysql_error());
}
 
// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
  die ('Can\'t use db : ' . mysql_error());
}
 
// Select all the rows in the markers table
$query = "SELECT * FROM geo_location";
 
$result = mysql_query($query);
if (!$result) {
  die('Invalid query: ' . mysql_error());
}
 
header("Content-type: text/xml");
 
// Start XML file, echo parent node
echo '<markers>';
 
// Iterate through the rows, printing XML nodes for each
while ($row = mysql_fetch_assoc($result)){
  // ADD TO XML DOCUMENT NODE
 
       $lat = $row['lat'];
      $lng = $row['lang'];
      $formatted_address = $row['location_address'];
     $loc_city = $row['location_city'];
 
    if( ($row['lat'] == "" || $row['lang'] == "" ) &&  $row['location_address'] != "" ){
        // getting lat/ lng
       $responce = getLatLangbyAddress($row['Location_address'],$row['location_city'],$row['zip_code']);
       $randomFloat = rand(0, 10) / 10000;
       $randomFloat2 = rand(0, 10) / 10000;
     // Adding some variation //
        $lat = $responce->results[0]->geometry->location->lat + $randomFloat; 
        $lng = $responce->results[0]->geometry->location->lng + $randomFloat2; 
    }
            if( ($row['lat'] != "" && $row['lang'] != "" ) &&  $row['location_address'] == "" ){
                // Getting address 
                $responce = getAdressbyLatLang($row['lat'],$row['lang']);
                $formatted_address = $responce->results[0]->formatted_address;
            }
  echo '<marker ';
  echo 'title="' . $row['event_title'] . '" ';
  echo 'address="' . parseToXML($formatted_address) . '" ';
  echo 'city="' . $row['location_city'] . '" ';
  echo 'state="' . $row['location_state'] . '" ';
  echo 'country="' . $row['country'] . '" ';
  echo 'zip="' . $row['zip_code'] . '" ';
  echo 'lat="' .  $lat . '" ';
  echo 'lng="' . $lng . '" ';
  echo 'type="' . $row['locaton_type'] . '" ';
  echo 'description="' . $row['location_discription'] . '" ';
  echo 'start="' . $row['start_time'] . '" ';
  echo 'end="' . $row['end_time'] . '" ';
  echo 'url="' . $row['lcation_url'] . '" ';
  echo '/>';
}
// End XML file
echo '</markers>';

Above code will generate output like this

<markers>
<marker title="Test Event One" address="140 Old Kent Road, London SE1, UK" city="test location" state="london" country="United Kindom" zip="Nw1 w42" lat="51.49164465653034"lng="-0.0823974609375" type="restaurant" description="Al marj Cinema center test data " start="0000-00-00 00:00:00" end="0000-00-00 00:00:00" url="http://www.geoshaper.com/Reupload/"/>
<marker title="Test Event Two" address="16 Pine Coombe, Croydon, Greater London CR0 5HS, UK" city="test location two" state="london" country="United Kindom" zip="Nw1 w42"lat="51.36492148825955" lng="-0.0439453125" type="restaurant" description="Test data description two " start="0000-00-00 00:00:00" end="0000-00-00 00:00:00"url="http://www.geoshaper.com/Reupload/"/>
<marker title="Test even 3" address="Steinau 1, 8844 Einsiedeln, Switzerland" city="swiss test " state="Zurich zug" country="Awitzerland" zip="215466" lat="47.08508535995383"lng="8.778076171875" type="restaurant" description="Al marj Cinema center test data " start="0000-00-00 00:00:00" end="0000-00-00 00:00:00" url="www.geoshaper.com"/>
<marker title="London tree" address="16 Choats Road, Barking, Dagenham, Greater London RM9 6LF, UK" city="test location" state="london" country="United Kindom" zip="Nw1 w42"lat="51.52241608253253" lng="0.1318359375" type="bar" description="Al marj Cinema center test data " start="0000-00-00 00:00:00" end="0000-00-00 00:00:00" url="www.geoshaper.com"/>
<marker title="Swiz event two" address="Alte Winterthurerstrasse 51A, 8304 Wallisellen, Switzerland" city="swiss test " state="london" country="switzerland" zip="215466"lat="47.416937456635445" lng="8.59130859375" type="restaurant" description="Al marj Cinema center test data " start="0000-00-00 00:00:00" end="0000-00-00 00:00:00"url="www.geoshaper.com"/>
<marker title="" address="TQ3 3ST" city="" state="" country="" zip="" lat="51.95780738871554" lng="-2.887207567691803" type="restaurant" description="" start="0000-00-00 00:00:00"end="0000-00-00 00:00:00" url=""/>
</markers>

Now we have all required information that we need visualizes on google map. Lets create map. Initializing map

geocoder = new google.maps.Geocoder();
    var latlng = new google.maps.LatLng(46.837649560937464, 8.60452651977539);
    var mapOptions = {
      zoom: 2,
      center: latlng,
      mapTypeId: google.maps.MapTypeId.ROADMAP
    }
    map = new google.maps.Map(document.getElementById("map-canvas"), mapOptions);

Then we are creating infowindow to show data on icon

var infoWindow = new google.maps.InfoWindow;

Next we move to load from our xml out put file

  downloadUrl("location_xml_reader.php", function(data) {
        var xml = data.responseXML;
         var markers = [];
        var markers = xml.documentElement.getElementsByTagName("marker");
        for (var i = 0; i < markers.length; i++) {
          var title = markers[i].getAttribute("title");
          var city = markers[i].getAttribute("city");
          var state = markers[i].getAttribute("state");
          var country = markers[i].getAttribute("country");
          var address = markers[i].getAttribute("address");
          var type = markers[i].getAttribute("type");
          var zip = markers[i].getAttribute("zip");
		  var description = markers[i].getAttribute("description");
                  var starttime = markers[i].getAttribute("start");
                  var endtime = markers[i].getAttribute("end");
		  var url = markers[i].getAttribute("url");

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Add new comment

257 views

How To Feed Mysql Data Into Google Map

Language

Tags

Add new comment