Saturday, June 2, 2012

Google maps - locations live update from SQL Server


Hi Readers This post is about showing the locations (Longitude and Latitude) values stored in a SQL server database in a google map. this will automatically update the location lively. For this I'm using a ASP .Net website . Languages used will be C# Linq and javascript. First we'll create the database
--Create Database
CREATE DATABASE MapSample
USE MapSample

--Create Table
CREATE TABLE Locations
(
 ID INT PRIMARY KEY IDENTITY(1,1),
 Longitude FLOAT,
 Latitude FLOAT,
 Bounce INT,
 [Description] VARCHAR(50)
);

Here the bounce column is to make a marker bounce and the description column is to show the message when the marker is clicked.
Now lets see the javascript explanation
        //The array to hold the markers this will be used to clear the markers for every refresh
        var markersArray = [];

        //The map object
        var map;
        //Longitude of the center point
        var longt = 80.727539;
        //Latitude of the center point
        var lat = 7.373362;

        //Make the update method run once in every 10 seconds
        self.setInterval("updateMap()", 10000);

        //initialize the google map
        function initialize() {
            //Options for the map object
            var myOptions = {
                zoom: 8,
                center: new google.maps.LatLng(lat, longt),
                mapTypeId: google.maps.MapTypeId.ROADMAP
            };

            //create the map object
            map = new google.maps.Map(document.getElementById('map_canvas'),myOptions);
        }

        //method to clear the markers in the map
        function clearOverlays() {
            if (markersArray) {
                for (var i = 0; i < markersArray.length; i++) {
                    //remove the marker from the map
                    markersArray[i].setMap(null);
                }
                //set the marker array as null
                markersArray = [];
            }
        }

        //show the map in the web page
        google.maps.event.addDomListener(window,  load , initialize);

        //The update method that is used to insert markers to the map
        function updateMap() {

            //clear the markers that are currently in the map so that new once can be added
            clearOverlays();

            //loop to set the markers
            for (var i = 0; i < lonarray.length; i++) {

                //get the longtitude from the longtitude array this array is set from the C# code 
                longt = lonarray[i];
                //get the latitude from the latitude array this array is set from the C# code
                lat = latarray[i];
                //set a title
                var title =  test ;

                //create the location object
                var location = new google.maps.LatLng(lat, longt);
                //create the marker
                var marker1 = new google.maps.Marker({ position: location, title: title });
                //set whether the marker should bounce or not
                if (bounce[i] == 1) {
                    marker1.setAnimation(google.maps.Animation.BOUNCE);
                }

                //set the information of the overlay which will be displayed when marker is clicked
                var infoContent = infoarray[i];
                setContent(marker1, infoContent);

                //add the marker to the marker array so that it can be cleared
                markersArray.push(marker1);

                //add the marker to the map
                marker1.setMap(map);

            }
            //clear the array contents so that new locations can be loaded
            infoarray = [];
            latarray = [];
            lonarray = [];
            emergencies = [];
        }

        //the method used to set the onclick method and info window
        function setContent(marker,message) {

            var infowindow = new google.maps.InfoWindow({
                content: message
            });

            google.maps.event.addListener(marker,  click , function() {
                infowindow.open(map, marker);
            });
        }
Now we'll go to the C# code that will be used to set the array contents
Here i'm using an update panel and timer control which refreshes it every 10 seconds (Please download and see the HTML part to see the code)
This will be the code behind file
 protected void UpdatePanel1_Load(object sender, EventArgs e)
        {
            //Linq is used to load the table to the code
            DataClassesDataContext data = new DataClassesDataContext();

            //Select all from the table
            List lst = (from u in data.Locations select u).ToList();

            //add the table contents to the javascript array so that new locations will be loaded
            foreach (Location item in lst)
            {
                ScriptManager.RegisterArrayDeclaration(UpdatePanel1, "infoarray", "'"+item.Description.ToString()+"'");
                ScriptManager.RegisterArrayDeclaration(UpdatePanel1, "lonarray", item.Longitude.ToString());
                ScriptManager.RegisterArrayDeclaration(UpdatePanel1, "latarray", item.Latitude.ToString());
                ScriptManager.RegisterArrayDeclaration(UpdatePanel1, "bounce", item.Bounce.ToString()); 
            }
        }

        protected void Timer1_Tick(object sender, EventArgs e)
        {
            //update the update panel every 10 seconds
            UpdatePanel1.Update();
        }

You can download the source files HERE
Hope this is useful
Happy Coding.

7 comments:

  1. Thank you so much for this code!! WOW it helped me so much. by any chance would you know how to populate the map with a selected item form a dropdown box that is linked to the database?

    ReplyDelete
  2. then you'll have to use ajax again to get the point information from database and show the point in the map using javasript
    or
    you can use jquery and WCF and create a rest service to do that

    ReplyDelete
  3. when you clear your js array you clear emergency[] and should be clearing bounce[]
    i was changing my db and bouncing wouldn't start/stop if this wasn't cleared

    ReplyDelete
    Replies
    1. I should mention that this is a great little chunk of code.

      Delete
  4. Si cambio la posicion en la base de datos, como puedo hacer para que se refresque en tiempo real en el mapa sin usar f5 ???

    ReplyDelete
  5. Thanks for the code. Inside the UpdateMap function while executin the line

    //create the location object
    var location = new google.maps.LatLng(lat, longt);

    I am encountering the following crash
    "0x800a01bd - JavaScript runtime error: Object doesn't support this action"

    Please help me in fixing this.

    Asi

    ReplyDelete
  6. Hi,
    I very much like your post and thanking you for sharing knowledge, however, is it possible to add a watchposition so we can watch movement in real time as shown in this post http://stackoverflow.com/questions/38837894/geolocation-watchposition-multiple-locations very much appreciate you prompt reply . thanks

    ReplyDelete