Google Maps allows you to quickly and easily create data visualization and plot analytic markers on a map. Whether you would like to visualize monthly sales, target sales, re-seller count or location of your project site; all of these can be done using Google Map API.

Having spatial data such as latitude and longitude is not particularly meaningful to see; unless you know where 47 degrees latitude 250 degrees longitude located on the earth, it doesn’t really make much sense. This is where maps play an important role in translating spatial data in a meaningful way.

In this article, I’ll be using sample data of retailer in my area, Jacksonville Florida. This data are just sample and don’t reflect the actual performance of those retailers used in this article. Below screenshot displays a map with markers based on the Sales Date and Region parameters. The data used to populate and color the markers are stored in SQL Server database.

Stored Procedure

For this article, I’m using a retail demo database that contains store locations with latitude and longitude. The following is a stored procedure that returns those data; such as, Store Name, Target Sales, the sum of sale for the month, Latitude, Longitude, etc. The stored procedure seems to be a little bit longer due to the need for the data for the markers color rule. But most importantly, for the map, we need to have the latitude and longitude to determine the exact location of the store.

When the above stored-procedure is executed using the same parameters as the ones for the map, the data returned are identical. The difference is the visualization of the data; one presented on the map and the other presented in a tabular form, as seen in below screenshot.

HTML Markup and Google Maps Scripts

Below is the JavaScript which is placed in the markup/.aspx. The markup includes an ASP .NET repeater that holds the data returned from the SQL Server table and used to populate the markers array. When the Search button is clicked, the values from the database are loaded to the markers array. The JavaScript’s GetMap() method is invoked at the end of the btnSearch_Click event.

 

Populating the Google Maps Marker

The btnSearch_Click event populates the ASP.Net Repeater control using the records stored SQL Server table through the procedure displayed above. The color rule for the markers is determined by the values returned from the database. As seen on below code snippet, inside of the foreach loop, the markers’ color are determined by the product of the fractions (monthly sale over the target sales).

C#

 protected void btnSearch_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            List stores = new List();
            Store str = null;
            string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "spStoreInfoWithTarget";
            cmd.Parameters.Add("@Date", SqlDbType.Date).Value = txtDate.Text.Trim();
            cmd.Parameters.Add("@Region", SqlDbType.VarChar, 50).Value = txtRegion.Text.Trim();
            cmd.Connection = con;
            try
            {
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                double salesThisMonth = 0;
                double targetSales = 0;
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        str = new Store();
                        str.StoreName = dr["StoreName"].ToString();
                        str.Region = dr["Region"].ToString();
                        str.Latitude = dr["Latitude"].ToString();
                        str.Longitude = dr["Longitude"].ToString();
                        if (dr["SaleThisMonth"] != DBNull.Value)
                            salesThisMonth = Convert.ToDouble(dr["SaleThisMonth"]);
                        if (dr["TargetSale"] != DBNull.Value)
                            targetSales = Convert.ToDouble(dr["TargetSale"]);
                        if (salesThisMonth > 0 && targetSales > 0)
                        {
                            str.SaleThisMonth = string.Format("{0:0.00}", salesThisMonth);
                            str.TargetSale = string.Format("{0:0.00}", targetSales);

                            if ((salesThisMonth / targetSales) < .4)
                                str.MarkerType = "red";
                            else if ((salesThisMonth / targetSales) > .4 && (salesThisMonth / targetSales) < .6)
                                str.MarkerType = "yellow";
                            else
                                str.MarkerType = "green";
                        }
                        stores.Add(str);
                    }

                    rptStore.DataSource = stores;
                    rptStore.DataBind();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }

            ClientScript.RegisterStartupScript(GetType(), "GoogleMap", "GetMap();", true);
        }

Store Class

Below class contains properties that hold the data returned from the database to be used for the map.

 public class Store
    {
        private string storeName;
        private string latitude;
        private string longitude;
        private string region;
        private string salesThisMonth;
        private string targetSale;
        private string markerType;

        public string StoreName
        {
            get
            {
                return storeName;
            }
            set
            {
                storeName = value;
            }
        }

        public string Latitude
        {
            get
            {
                return latitude;
            }
            set
            {
                latitude = value;
            }
        }

        public string Longitude
        {
            get
            {
                return longitude;
            }
            set
            {
                longitude = value;
            }
        }

        public string Region
        {
            get
            {
                return region;
            }
            set
            {
                region = value;
            }
        }

        public string SaleThisMonth
        {
            get
            {
                return salesThisMonth;
            }
            set
            {
                salesThisMonth = value;
            }
        }

        public string TargetSale
        {
            get
            {
                return targetSale;
            }
            set
            {
                targetSale = value;
            }
        }

        public string MarkerType
        {
            get
            {
                return markerType;
            }
            set
            {
                markerType = value;
            }
        }
    }

Conclusion
Developing data visualization that can be understood by ordinary people requires a right development tool, and the map is one of the best options. With a map, you can see the data in a meaningful way – the markers are color coded which represent the trend of your data, and you can also view the detail of your data by clicking on those markers.