Combining External Data Sources with a MS SQL Server Database to Create Google Maps Using AJAX and ASP.NET

• Download source files - 44.3 Kb

Introduction

To make a dynamic and interactive Google Map application, often times, we need to retrieve some data from the Internet or a web service and combine it with our internal data. Here is a perfect example that illustrates the steps that are needed to apply AJAX (Asynchronous JavaScript and XML) and ASP.NET C# environment to create a reusable class that accomplishes these tasks. The class retrieves a country capital list from the internet and the US state center location, resolves geo-codes by applying the Yahoo! Map geo-coding web service, and combines these with an internal MS SQL Server database source, Northwind in this demo, to create a Google map.

Background

AJAX, a new marketer friendly technology, could show the user more background information without leaving the main page. It represents Asynchronous JavaScript and XML, and is used here to retrieve the combined data from source page and show it on Google Map. The first scenario is to show the number of customers in different countries; the second scenario is to show the number of customers in different US states. On our website, we count the number of alumni from different countries and states in the US. The only difference is that the bubbles or info-windows are located at each capital for the world map, while in the second case, info-windows are located at the center of each state. If you are a first time reader, the bubble is just an icon on Google maps that sits at the exact latitude and longitude and pops up a small window for more detailed information after the user clicks on it.

In the first scenario of the world map view, we query the database and retrieve a list of country names and capitals, construct a URL to query the Yahoo! map geo-code free service to get the exact geo-codes, and create a new XML file which contains the number of customers from each country. In the second case of US states, we first query the database and get the number of customers within each state, retrieve the geographical center location from the web service provided by toeat.com, and create a new XML file which contains both geo-codes and the number of customers for each state.

Finally, all the data are shown on the Google map with GXmlHttp.

Class Internals

I have a class called MapDataAccess.cs, to query the Northwind database and return number of customers in a SqlDataReader. The following is one of three methods that does the counting and grouping in the database:

public SqlDataReader GetStateStat(string strCountry)
{
    string queryString="SELECT  COUNT(*) AS StateTotal, Region" +
                       " FROM Customers where COUNTRY = '" + 
                       strCountry + "' GROUP BY Region";
    return SqlHelper.ExecuteReader(conn, 
           CommandType.Text, queryString);
}

The second class is called MapData.cs, which invokes Yahoo! map geo-code service to resolve the latitude and longitude. The method constructs the appropriate URL string from different country names and city names, together with the Yahoo! map service key which is required.

public void SetGeocodesEach(string strCountry, string strCity){
    try
    {
        if (strCountry == "USA")
        {
            string url = "http://api.local.yahoo.com/MapsService" + 
                         "/V1/geocode?appid=" + Global.GMYahooKey + 
                         "&city=    "+strCity+ 
                         "&state=Maryland&country=" + 
                         strCountry;
            XmlTextReader xreader;
            xreader = new XmlTextReader(url);
            DataSet ds = new DataSet();
            ds.ReadXml(xreader);
            lat = Convert.ToDouble(ds.Tables[0].Rows[0]["Latitude"]);
            lng = Convert.ToDouble(ds.Tables[0].Rows[0]["Longitude"]);
        }
        else
        {
            string url = "http://api.local.yahoo.com/MapsService" + 
                         "/V1/geocode?appid=" + Global.GMYahooKey + 
                         "&city=    " + strCity + 
                         "&state=&Country=" + strCountry;
            XmlTextReader xreader;
            xreader = new XmlTextReader(url);
            DataSet ds = new DataSet();
            ds.ReadXml(xreader);
            lat = Convert.ToDouble(ds.Tables[0].Rows[0]["Latitude"]);
            lng = Convert.ToDouble(ds.Tables[0].Rows[0]["Longitude"]);
        }
    }
    catch (Exception e) 
    {
        //The next line of code is for debugging purpose.
        //HttpContext.Current.Response.Write(
        //       string.Format("<P>{0}: {1}</P>",
        //       e.Message, strAddr));
        return;
    }
}

The third class is called GoogleAddress.cs, and is used to store the longitude and latitude received from external web services by providing the address information.

ASPX Page that Merges and Outputs the XML

The page StateData.aspx could be called separately to check data errors or connection errors. To write the XML file, the XmlTextWriter is used and we set its content type as text/xml.

Response.Clear();
Response.ContentType = "text/xml";
XmlTextWriter objX = new XmlTextWriter(Response.OutputStream, 
                     System.Text.Encoding.UTF8);

The system first loads the external source as XmlDocument from toeat.com for the state center geo-codes:

XmlDocument nodes = new XmlDocument();
nodes.Load("http://www.toeat.com/Map/US/");

It then merges the center geo-codes for each state of the United States with the database counting results, by applying XPath to locate the exact node to enhance performance without extra looping.

XmlNode node = 
   doc.SelectSingleNode("descendant::marker[@title='" + 
   strLState + "']");

After that, it writes an XML file with markers, latitude, longitude, country, title, and message attributes, which will be required for Google Map:

objX.WriteStartElement("marker");
objX.WriteAttributeString("lat", node.Attributes["lat"].InnerXml);
objX.WriteAttributeString("lng",node.Attributes["lng"].InnerXml);
objX.WriteAttributeString("country",strCountry);
objX.WriteAttributeString("title", strSState);
objX.WriteAttributeString("message", dr["StateTotal"].ToString());
objX.WriteEndElement();

To merge center geo-codes for the capital of the country with the database results, we need to first render the geo-codes for each country capital, then write an XML file with the database data. The following is the code snippet to resolve geo-codes:

MapData md = new MapData();
md.SetGeocodesEach(strC, MapData.Capital(strC).Trim());
md.GetGeocodes(ga);

AJAX In Action to Show Google Map

The magic page is called AlumStatesMap.aspx, and the code-behind page sets up the default center location geo-codes for the World and US views. To make all the bubbles fit into one map, I randomly selected the center point around Libya for the World view and Kansas for the US view. It also gets the Google map key and display size. A radio button is placed on this page to switch between the World view and the US view, which also sets the default value for country session variable, the display size and the center geo-codes.

To prepare the map with icons and info-windows:

var icon = new GIcon();
icon.image="http://labs.google.com/ridefinder/images/mm_20_red.png";
icon.shadow="http://labs.google.com/ridefinder/images/mm_20_shadow.png";
icon.iconSize = new GSize(12, 20);
icon.shadowSize = new GSize(22, 20);
icon.iconAnchor = new GPoint(6, 20);
icon.infoWindowAnchor = new GPoint(5, 1);

Center the map by filling geo-codes from the code-behind page:

var map = new GMap(document.getElementById("map"));
map.addControl(new GSmallMapControl());
map.addControl(new GMapTypeControl());
map.centerAndZoom(new GPoint(<%Response.Write(longitude);%>

Create a marker whose info window displays the state or country name, and the number of customers in the area, when it is clicked:

function createMarker(point, infostring) {
    var marker = new GMarker(point, icon);
    GEvent.addListener(marker, 'click', function() {
        marker.openInfoWindowHtml(infostring);
    });
    return marker;
}

Download the data from the StateData.aspx page, and load it onto the map. The data is generated dynamically by querying the database and reorganizing the data into XML format for easy upload:

var request = GXmlHttp.create();
var page;
page = "StateData.aspx";
var url = page +"?RandomKey=" + 
          Math.random() * Date.parse(new Date());
request.open('GET', url, true);
request.onreadystatechange = function()  {
    if (request.readyState == 4) {
       var xmlDoc =  request.responseXML;
    var markers = 
      xmlDoc.documentElement.getElementsByTagName("marker");
    for (var i = 0;  i <  markers.length; i++) {
      var point = new 
       GPoint(parseFloat(markers[i].getAttribute("lng")), 
       parseFloat(markers[i].getAttribute("lat")));
      var info = markers[i].getAttribute("title") + "<br>";
      info += "Number of Customers: " + 
              markers[i].getAttribute("message"); 
      var marker = new createMarker(point, info);
      map.addOverlay(marker);
    }
}

Points of Interests

To convert the output of an aspx page into XML format, use the following code snippet:

Response.ContentType = "text/xml";

To increase the performance and reduce the unnecessary loops, XPath is used:

XmlNode node = 
   doc.SelectSingleNode("descendant::marker[@title='" + 
   strLState + "']");

To pass a variable from a code-behind page to an HTML page, use the Response object:

map.centerAndZoom(new GPoint(<%Response.Write(longitude);%>, 
     <% Response.Write(latitude);%>), <%Response.Write(displaySize);%>);

To retrieve new data from XML data, provide a random page key each time when the page is called or do not keep cached versions:

var url = page +"?RandomKey=" + Math.random() * Date.parse(new Date());

To wrap the text of an info-window in the FireFox browser, use a div tag:

var info = markers[i].getAttribute("title") + " ";
info += "Number of Customers: " + 
        markers[i].getAttribute("message");

Final Notes

Since I used some of the keywords in my paper such as Response, div, and br, the code from this page might not work correctly. Please follow the reference codes in the Zip file. There are also differences between this Web demo project and the production code, please visit my blog, or view the live KD Alumni Statistics on Google Map.