Click here to Skip to main content
Click here to Skip to main content

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

, 17 Nov 2006
Rate this:
Please Sign up or sign in to vote.
To dynamically generate a Google Map with combined data sources from an external web service or API and from an internal MS SQL database, using AJAX and the ASP.NET environment.

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

MarkWei
Web Developer
United States United States
No Biography provided

Comments and Discussions

 
QuestionHow to retrive latitude and longitude values from database through javascript Pinmemberinfosenthi22-Oct-09 21:34 
GeneralWish it Was Oracle DB Pinmembermbaocha6-May-09 11:52 
GeneralHelp PinmemberShawn12822-Jun-08 12:21 
GeneralGreat article! Pinmemberfstrahberger20-Nov-06 4:20 
Hi Mark,
 
I really like your article - its very usefull Smile | :) . But I prefer to use Virtual Earth instead of Google Maps.
 
I have created a football map (1. Bundesliga in Germany) using microsofts Virtual Earth. I like Virtual Earth because you do not have to register and get an API key. Also you can move your maps from "localhost" to any hoster without changing the source code.
 
I also like the (easy to use mouseover) effects. By the way take a look at the Virtual Earth SDK from microsoft.
 

GeneralError Pinmemberluckydyno16-Nov-06 21:08 
GeneralRe: Error PinmemberMarkWei17-Nov-06 3:06 
GeneralMontana, Wyoming, Louisiana missing PinmemberGeepster18-May-06 7:39 
GeneralRe: Montana, Wyoming, Louisiana missing PinmemberMarkWei26-May-06 6:23 
QuestionGoogle 2? Keys with VS 2005? PinmemberGeepster16-May-06 6:37 
AnswerRe: Google 2? Keys with VS 2005? PinmemberMarkWei16-May-06 8:34 
GeneralRe: Google 2? Keys with VS 2005? PinmemberGeepster16-May-06 10:05 
GeneralRe: Google 2? Keys with VS 2005? PinmemberMarkWei18-May-06 3:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 17 Nov 2006
Article Copyright 2006 by MarkWei
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid