Tech-Today

Google Map API - an example implementation in C#. Converts an array of string addresses into longtitude, latitude pair

Objective:

-To create a web application that will use the google map api to produce longtitude, latitude locations. The application will read string address values from an excel document. So it's also an excel reader.

What you need:
1.) Visual Studio IDE (I'm using 2008)
2.) Microsoft Excel (will contain the addresses)
3.) GoogleAPIKey (http://code.google.com/apis/maps/signup.html)

Now we're set. What we should do:
1.) create a new dotnet web application, name it GMapWeb
2.) in our application's web.config, under the configSection section add an entry: <add key="GoogleApiKey" value="yourgoogleapikey"/>
-googleapikey, something like: ABQIAAAAYYO_Kw81Wxrb9OWt-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxENLRU965DSD7kFtdhVw
3.) next, we will edit the default page, Default.aspx.cs (codebehind)
a.) onpageload event we will read the address data in the excel document (the excel document must reside on the same location as Default.aspx):

    protected void Page_Load(object sender, EventArgs e)
    {
        string st = MappedApplicationPath + "address.xls"; //virtual location of the excel document
        String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
               "Data Source=" + st + ";" +
               "Extended Properties=Excel 8.0;"; //set the connection string
        OleDbConnection objConn = null; ;
        try
        {
            //open connection and execute query
            objConn = new OleDbConnection(sConnectionString);
            objConn.Open();
            OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);
            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
            objAdapter1.SelectCommand = objCmdSelect;
            DataSet objDataset1 = new DataSet();
            objAdapter1.Fill(objDataset1);
            ArrayList arrAdd = new ArrayList();

            //read each entry in excel's document first column
            foreach (DataTable table in objDataset1.Tables)
                foreach (DataRow row in table.Rows)
                {
                    string s = "";
                    foreach (DataColumn col in table.Columns)
                    {
                        s = s + " " + row[col].ToString();
                    }
                    arrAdd.Add(s); //add each address to an ArrayList
                }
            ViewState["edward"] = arrAdd; //add the ArrayList in a ViewState object
        }
        catch (Exception ex)
        {
            string s = ex.ToString();
        }
        finally
        {
            objConn.Close();
        }
    }

    //get the virtual location
    protected string MappedApplicationPath
    {
        get
        {
            try
            {
                string APP_PATH = System.Web.HttpContext.Current.Request.ApplicationPath.ToLower();
                if (APP_PATH == "/")      //a site
                    APP_PATH = "/";
                else if (!APP_PATH.EndsWith(@"/")) //a virtual
                    APP_PATH += @"/";

                string it = System.Web.HttpContext.Current.Server.MapPath(APP_PATH);
                if (!it.EndsWith(@"\"))
                    it += @"\";
                return it;
            }
            catch (Exception nre)
            {
                return null;
            }
        } //end get
    } //end mapped

Finally we have to edit the Default.aspx page (front end - ui):

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="getCoordinate.aspx.cs" Inherits="getCoordinate" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="content-type" content="text/html; charset=UTF-8"/>
    <title>Google Maps API Example - Geocoding API</title>
    <script src="http://maps.google.com/maps?file=api&v=2.x&key=googlekeyapi" type="text/javascript"></script>
    <script type="text/javascript">
    //<![CDATA[

    var map = null;
    var geocoder = null;

    function load() {
      if (GBrowserIsCompatible()) {
        map = new GMap2(document.getElementById("map"));
        map.setCenter(new GLatLng(37.4419, -122.1419), 13); //set the center map to "1600 Amphitheatre Pky, Mountain View, CA"
        geocoder = new GClientGeocoder(); //create a new Geocoder object
      }
    }
    
    <% ArrayList arrAdd = (ArrayList)ViewState["edward"]; %>

    function showAddress() {    
      if (geocoder) {
        var address = null;
        var d = open('', ''); //open a blank document
        <% for(int i = 0; i < arrAdd.Count; i++) { %>
            address = '<% Response.Write(arrAdd[i].ToString()); %>';
            geocoder.getLatLng(
              address,
              function(point) { //display the string address and its corresponding longtitude, latitude
                d.document.write('<% Response.Write(arrAdd[i].ToString()); %> :sachiko: ' + point + '<br />');
              } // sample printed data: 1 Star Drive Laguna Niguel :sachiko: (33.541886, -117.676792)              
            );
        <% } %>
      }
    }
    //]]>
    </script>
  </head>

  <body onload="load()" onunload="GUnload()">
    <form action="#" onsubmit="showAddress(); return false">
      <p>
        <input type="text" size="60" name="address" value="1600 Amphitheatre Pky, Mountain View, CA" />
        <input type="submit" value="Go!" />
      </p>
      <div id="map" ></div>
    </form>
  </body>
</html>

Note:
-change the googlekeyapi, with the one you registered with
-on button click, the application will call the javascript function showAddress, it will create a blank document to display the address string and it's latitude, longtitude coordinate
-GClientGeocoder class, is responsible for processing string address information and converting it to longtitude, latitude, there are more functions available just consult the api from google
-sample printed data: 1 Star Drive Laguna Niguel :sachiko: (33.541886, -117.676792)

BTW, I think there's no need for me to attach a sample excel document. Just create a blank excel document and enter the following address data in the first column, you can add more if you like

1 Star Drive Laguna Niguel
1011 Brioso Drive, Suite 108 Costa Mesa
10605 Bechler River Ave Fountain Valley
10605 Bechler River Ave. Fountain Valley
10900 Firestone Blvd Norwalk
11800 Woodruff Ave. Downey
1201 Normandy Pl Santa Ana
1227 S LA BREA AVE INGLEWOOD
1290 Knollwood Circle Anaheim

- Html Scraping With Java
One rainy Sunday afternoon since I can't get out to go somewhere I've decided to create an organized excel file (for now) for the list of birds commonly found in the Philippines. I found a good site to start with, http://www.birding2asia.com/tours/reports/PhilFeb2010_list.html,...

- How To Upload A File In An Mvc3 C# Ajax Form
It's easy to upload file in a synchronous form, where you just post the file and read a HttpPostedFile variable in the server side: In plain html form, take note of the form enctype property: //the view (index.cshtml) @using (Html.BeginForm("Upload",...

- Sending And Receiving Xml Through Http Post
Usually, it's easier done on dotnet webservice but there are times when your client wants to do this using Http Post. So I've created 2 test pages, one that will request and with for a response while the other will accept the request and return...

- Excel Applicationclassclass Object Still Running After Quit Method
Problem: I have developed an application in Visual Studio C# that will read and write contents on an excel document. After I'm done coding, I've found several instances of the EXCEL application in the System's Task Manager. I've googled...

- Missing Microsoft.office.interop.excel.dll
To read an excel document, you need the excel interop. Most of the time people are confused on where is this library located. VS2003 Add Reference->COM->Microsoft Excel 11 Object Library VS2005 Add Reference->COM->Microsoft Office 11 Object Library Or...

Tech-Today