How To Feed Mysql Data Into Google Map

Submitted by: 
Language: 
Visitors have accessed this post 2005 times.

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

  1. CREATE TABLE IF NOT EXISTS `geo_location` (
  2. `id` INT(11) NOT NULL AUTO_INCREMENT,
  3. `event_title` VARCHAR(50) NOT NULL,
  4. `location_address` VARCHAR(150) NOT NULL,
  5. `location_city` VARCHAR(75) NOT NULL,
  6. `location_state` VARCHAR(75) NOT NULL,
  7. `zip_code` VARCHAR(20) NOT NULL,
  8. `country` VARCHAR(50) NOT NULL,
  9. `lat` VARCHAR(20) NOT NULL,
  10. `lang` VARCHAR(20) NOT NULL,
  11. `location_discription` VARCHAR(200) NOT NULL,
  12. `start_time` datetime NOT NULL,
  13. `end_time` datetime NOT NULL,
  14. `lcation_url` VARCHAR(150) NOT NULL,
  15. `locaton_type` VARCHAR(50) NOT NULL,
  16. PRIMARY KEY (`id`)
  17. )

Then we are going to insert few records to above table

  1. 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
  2. (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', '<a href="http://www.geoshaper.com/Reupload/'" rel="nofollow">http://www.geoshaper.com/Reupload/'</a>, 'restaurant'),
  3. (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', '<a href="http://www.geoshaper.com/Reupload/'" rel="nofollow">http://www.geoshaper.com/Reupload/'</a>, 'restaurant'),
  4. (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', '<a href="http://www.geoshaper.com'" rel="nofollow">www.geoshaper.com'</a>, 'restaurant'),
  5. (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', '<a href="http://www.geoshaper.com'" rel="nofollow">www.geoshaper.com'</a>, 'bar'),
  6. (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', '<a href="http://www.geoshaper.com'" rel="nofollow">www.geoshaper.com'</a>, 'restaurant'),
  7. (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

  1. function getLatLangbyAddress($adress,$location,$zip)
  2. {
  3. $address = '';
  4. $compornent ='components=country:.CH';
  5.  
  6. if (isset($adress) && $adress !="" ){
  7. $address = $adress;
  8. }
  9. else {
  10.  
  11. if(isset($location) && $location !="" ){
  12. $address = $address .",".$location;
  13. $compornent = $compornent.'| $compornent =locality:'.$location;
  14. }
  15. }
  16.  
  17. if (isset($zip) && $zip !="" ){
  18. $address = $adress .",".$zip;
  19. $compornent = $compornent.'|$compornent =postal_code:'.$zip;
  20. }
  21. $a = urlencode($address);
  22. $b = urlencode($compornent);
  23. $geocodeURL =
  24. "<a href="http://maps.googleapis.com/maps/api/geocode/json?address=" rel="nofollow">http://maps.googleapis.com/maps/api/geocode/json?address=</a>$a&$b&sensor=false";
  25. $ch = curl_init($geocodeURL);
  26. curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
  27. $result = curl_exec($ch);
  28. $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
  29. curl_close($ch);
  30. if ($httpCode == 200) {
  31. $geocode = json_decode($result);
  32. return $geocode;
  33. } else {
  34. $geo_status = "HTTP_FAIL_$httpCode";
  35. }
  36. }

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

  1. function getAdressbyLatLang($lat,$lang)
  2. {
  3. $address = '';
  4. $address = $lat.",".$lang;
  5. $a = urlencode($address);
  6. $geocodeURL =
  7. "<a href="http://maps.googleapis.com/maps/api/geocode/json?latlng=" rel="nofollow">http://maps.googleapis.com/maps/api/geocode/json?latlng=</a>$a&sensor=false";
  8. $ch = curl_init($geocodeURL);
  9. curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
  10. $result = curl_exec($ch);
  11. $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
  12. curl_close($ch);
  13. if ($httpCode == 200) {
  14. $geocode = json_decode($result);
  15. return $geocode;
  16. } else {
  17. $geo_status = "HTTP_FAIL_$httpCode";
  18. }
  19. }

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

  1. function parseToXML($htmlStr)
  2. {
  3. $xmlStr=str_replace('<','&lt;',$htmlStr);
  4. $xmlStr=str_replace('>','&gt;',$xmlStr);
  5. $xmlStr=str_replace('"','&quot;',$xmlStr);
  6. $xmlStr=str_replace("'",'&#39;',$xmlStr);
  7. $xmlStr=str_replace("&",'&amp;',$xmlStr);
  8. return $xmlStr;
  9. }

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

  1. // Opens a connection to a MySQL server
  2. $connection=mysql_connect ($localhost, $username, $password);
  3. if (!$connection) {
  4. die('Not connected : ' . mysql_error());
  5. }
  6.  
  7. // Set the active MySQL database
  8. $db_selected = mysql_select_db($database, $connection);
  9. if (!$db_selected) {
  10. die ('Can\'t use db : ' . mysql_error());
  11. }
  12.  
  13. // Select all the rows in the markers table
  14. $query = "SELECT * FROM geo_location";
  15.  
  16. $result = mysql_query($query);
  17. if (!$result) {
  18. die('Invalid query: ' . mysql_error());
  19. }
  20.  
  21. header("Content-type: text/xml");
  22.  
  23. // Start XML file, echo parent node
  24. echo '<markers>';
  25.  
  26. // Iterate through the rows, printing XML nodes for each
  27. while ($row = mysql_fetch_assoc($result)){
  28. // ADD TO XML DOCUMENT NODE
  29.  
  30. $lat = $row['lat'];
  31. $lng = $row['lang'];
  32. $formatted_address = $row['location_address'];
  33. $loc_city = $row['location_city'];
  34.  
  35. if( ($row['lat'] == "" || $row['lang'] == "" ) && $row['location_address'] != "" ){
  36. // getting lat/ lng
  37. $responce = getLatLangbyAddress($row['Location_address'],$row['location_city'],$row['zip_code']);
  38. $randomFloat = rand(0, 10) / 10000;
  39. $randomFloat2 = rand(0, 10) / 10000;
  40. // Adding some variation //
  41. $lat = $responce->results[0]->geometry->location->lat + $randomFloat;
  42. $lng = $responce->results[0]->geometry->location->lng + $randomFloat2;
  43. }
  44. if( ($row['lat'] != "" && $row['lang'] != "" ) && $row['location_address'] == "" ){
  45. // Getting address
  46. $responce = getAdressbyLatLang($row['lat'],$row['lang']);
  47. $formatted_address = $responce->results[0]->formatted_address;
  48. }
  49. echo '<marker ';
  50. echo 'title="' . $row['event_title'] . '" ';
  51. echo 'address="' . parseToXML($formatted_address) . '" ';
  52. echo 'city="' . $row['location_city'] . '" ';
  53. echo 'state="' . $row['location_state'] . '" ';
  54. echo 'country="' . $row['country'] . '" ';
  55. echo 'zip="' . $row['zip_code'] . '" ';
  56. echo 'lat="' . $lat . '" ';
  57. echo 'lng="' . $lng . '" ';
  58. echo 'type="' . $row['locaton_type'] . '" ';
  59. echo 'description="' . $row['location_discription'] . '" ';
  60. echo 'start="' . $row['start_time'] . '" ';
  61. echo 'end="' . $row['end_time'] . '" ';
  62. echo 'url="' . $row['lcation_url'] . '" ';
  63. echo '/>';
  64. }
  65. // End XML file
  66. echo '</markers>';

Above code will generate output like this

  1. <markers>
  2. <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="<a href="http://www.geoshaper.com/Reupload/"/>
  3. <marker" rel="nofollow">http://www.geoshaper.com/Reupload/"/>
  4. <marker</a> 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="<a href="http://www.geoshaper.com/Reupload/"/>
  5. <marker" rel="nofollow">http://www.geoshaper.com/Reupload/"/>
  6. <marker</a> 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="<a href="http://www.geoshaper.com"/>
  7. <marker" rel="nofollow">www.geoshaper.com"/>
  8. <marker</a> 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="<a href="http://www.geoshaper.com"/>
  9. <marker" rel="nofollow">www.geoshaper.com"/>
  10. <marker</a> 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="<a href="http://www.geoshaper.com"/>
  11. <marker" rel="nofollow">www.geoshaper.com"/>
  12. <marker</a> 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=""/>
  13. </markers>

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

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

Then we are creating infowindow to show data on icon

  1. var infoWindow = new google.maps.InfoWindow;

Next we move to load from our xml out put file

  1. downloadUrl("location_xml_reader.php", function(data) {
  2. var xml = data.responseXML;
  3. var markers = [];
  4. var markers = xml.documentElement.getElementsByTagName("marker");
  5. for (var i = 0; i < markers.length; i++) {
  6. var title = markers[i].getAttribute("title");
  7. var city = markers[i].getAttribute("city");
  8. var state = markers[i].getAttribute("state");
  9. var country = markers[i].getAttribute("country");
  10. var address = markers[i].getAttribute("address");
  11. var type = markers[i].getAttribute("type");
  12. var zip = markers[i].getAttribute("zip");
  13. var description = markers[i].getAttribute("description");
  14. var starttime = markers[i].getAttribute("start");
  15. var endtime = markers[i].getAttribute("end");
  16. var url = markers[i].getAttribute("url");
  17.  

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

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.