Reading Google Map pointers from a MySQL database

As mentioned in my other blog post, you may be interested in how you can plot points on a google map using the google maps API, combined with a MySQL database.
The basic premise is that you have a MySQL database containing a series of latitudes and longitudes, and you need to read the latitudes/longitudes from the database and plot them onto the google map.

First thing first, create a a simple database table with a column for both latitude and longitude, you would of course have other fields depending on what data you are using the google map to represent. For simplicities sake, I’ve called them ‘lat’ and ‘lon’:

CREATE TABLE `map` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`lat` DECIMAL( 10, 6 ) NOT NULL ,
`lon` DECIMAL( 10, 6 ) NOT NULL ) ENGINE = MYISAM

We have set the lat and long as DECIMAL(10,6) to enable us to use decimal points due to how lat/long is represented (i.e 52.132633 is a latitude). We’ve also thrown in a auto incrementing ID for the primary key.
Now we have the MySQL table, we need to populate it, so go ahead and throw some latitudes and longitudesinto it. If you are feeling adventurous you can take a stab at Geocoding some locations using the API (I will be covering Geocoding in another article shortly). You can grab some latitudes and longitudes using maporama to search for a location, and you will find the lat/long on the bottom left corner of your screen.

Now onwards with the code.
First we need to connect to the database:

mysql_select_db(“dbname”, $db); ?>

Next, we will need to include the google maps API key in the headof your page. You can sign up for your API key by going to this page.

So, in the header of your page, enter this bit of script, with your API key placed where it says so:

<script src=“http://maps.google.com/maps?file=api&amp;v=2&amp;key=ENTERYOURKEYHERE” type=“text/javascript”></script>

Now we have set up access to the DB and linked to our google maps API, we can continue with the creation of the map.

Still in the of the page, create some javascript that will create the points. In this instance we want the points clickable so when you click on the point on the map, it will go to another webpage. For this we use the GEvent.addListener function, which will direct the user to the new page when they click on the point. We are directing people to a PHP page that will take the point as a variable from the address bar (using GET) and then display some sort of information depending on what is required.

<script type=“text/javascript”>// <![CDATA[
        function createMarker(point,html) {         var marker = new GMarker(point);
       GEvent.addListener(marker,"click",function(){ top.location = "http://www.websiteaddress.com/page.php?point="+point });
       return marker;
      }
// ]]></script>

Now we need to initialize the map:

<script type=“text/javascript”>// <![CDATA[
         function initialize() {
        if (GBrowserIsCompatible()) {
        var map = new GMap2(document.getElementById("map_canvas"),{ size: new GSize(580,400) } );
              map.removeMapType(G_HYBRID_MAP);
                map.setCenter(new GLatLng(-3.703250,40.416741,0), 1);
                var mapControl = new GMapTypeControl();
                map.addControl(mapControl);
                map.addControl(new GLargeMapControl());

This is only half the code, but I thought I'd halt there for a second and explain what's happening, We are creating a new map, and placing it in the DIV on our page called 'map_canvas'. We can specifiy the size of the map you want, and what type of map you want(i,e satellite, street,hybrid etc). The size is controlled by the 'size:' command, and the type of map by the 'map.removeMapType', which will remove any type of map button you don't want. You also set the the starting point of the map by using the 'map.setCenter' function.
You finally add the controls onto the map, i.e if you want the zoom, and the directional cursors for navigating.
After initializing the map, and in the same script and function (notice how the above piece of javascript isn't closed by a

// ]]> tag, and the function isn't closed either with curly bracket), we need to now plot our points onto the map from our MySQL table. To do this we use PHP to perform a query on the database and extract the lat and long, assign them to variables and output each one in the javascript as a point. Simple (err..)!

$result_map = mysql_query($exe_map, $db)or die(mysql_error());
while(list($lat,$long) = mysql_fetch_row($result_map)){ echo "n var point = new GLatLng(".$lat.",".$long.");n";
echo "var marker = createMarker(point,'');n";
echo "map.addOverlay(marker);n";
echo "n";
}
?&gt;

And don't forget to close the function,'if' statement and script:

}
}

This basically gets all latitudes and longitudes from the database, row by row, and creates a point on the map by assigning the lat/long variables to a javascript variable called 'point'. A google map marker called 'marker' is then created with the coordinates assigned to 'point'. Finally the marker is added to the map. This is repeated for all points in the database table.

And that's the hard part done. We have read out our points from the database and added them to the google map. All we need to do is create All we need to do is put the google map in the place on the page we said we would ("map_canvas"), and initialize the javascript when the page loads, otherwise the map won't show up.

Make sure your body tag has these javascript fucntions included: and finally, you can just place your div called 'map_canvas' wherever you want on your page.

Link: http://www.goatkarma.com/2009/06/reading-google-map-pointers-from-a-mysql-database/