Web Code Blog

In this article I will describe how you can create a simple location search feature with Google Maps using ASP.NET and SQL Server.  The basis of this article comes from the Google Maps API Documentation (http://code.google.com/apis/maps/articles/phpsqlsearch.html)

The first thing you will want to do is sign up and get a Google Maps API Key.  You will need one for the domain name of where you will be hosting the map.  You can get your API Key from here (http://code.google.com/apis/maps/signup.html).  Hold onto this key, you will need it later.

The end result will look something like this:

First, we need to set up the database.  You will want to store all the locations in a database table.  So lets create this table in SQL Server by using code like the below:

CREATE TABLE [dbo].[tblLocation](
    [LocationID] [int] IDENTITY(1,1) NOT NULL,
    [LocationDescription] [nvarchar](100) NOT NULL,
    [Address1] [nvarchar](50) NOT NULL,
    [Address2] [nvarchar](50) NULL,
    [Town] [nvarchar](50) NOT NULL,
    [County] [nvarchar](50) NULL,
    [Postcode] [nvarchar](10) NOT NULL,
    [Country] [nvarchar](50) NOT NULL,
    [Latitude] [decimal](10, 7) NOT NULL CONSTRAINT [DF_tblLocation_Latitude]  DEFAULT ((0)),
    [Longitude] [decimal](10, 7) NOT NULL CONSTRAINT [DF_tblLocation_Longitude]  DEFAULT ((0))
 CONSTRAINT [PK_tblLocation] PRIMARY KEY CLUSTERED
(
    [LocationID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

You can then pre-populate this table with your store locations. Here is an SQL example:

insert into dbo.tblLocation(LocationDescription, Address1, Address2, Town, County, Postcode, Country,
                            Latitude, Longitude)
values('Earls Court', '50 Earls Court Road', 'Earls Court', 'London', NULL, 'W8 6JE', 'United Kingdom',
       51.49635, -0.19664)

insert into dbo.tblLocation(LocationDescription, Address1, Address2, Town, County, Postcode, Country,
                            Latitude, Longitude)
values('Cromwell Place', '50 Cromwell Road', 'Earls Court', 'London', NULL, 'W8 6JE', 'United Kingdom',
       51.49464, -0.19209)

insert into dbo.tblLocation(LocationDescription, Address1, Address2, Town, County, Postcode, Country,
                            Latitude, Longitude)
values('South Kensington Mall', '10 Old Brompton Road', 'South Kensington', 'London', NULL, 'W8 6JE', 'United Kingdom',
       51.49295, -0.17728)

Note, to get the actual latitudes and longitudes for your locations, you can either use your local postal service data or use Google Maps. You can read my post about obtaining latitude and longitude co-ordinates for an address here (http://webcodeblog.com/2010/04/24/obtain-latitude-and-longitude-co-ordinates-for-an-address-using-asp-net-and-the-google-maps-api/)

Now that you have a locations table and some location data, you will want to create a stored procedure to find all the locations based on some parameters. This will use some maths to find the nearest locations based on a passed in co-ordinate (Haversine formula).  Do not worry so much about the maths of it unless you really want to.  We also want the code to return XML as we will eventually be looking at the resultset with JavaScript.

Here is the basic procedure code:

CREATE PROCEDURE [dbo].[proc_Location_List]
    (
        @dmlLat decimal(10, 7),
        @dmlLng decimal(10, 7),
        @intRadius int
    )
    As

SET NOCOUNT ON

DECLARE @intMilesModifier int
SET @intMilesModifier = 3959  -- If using kilometers, use 6371 instead of 3959

-- Select locations that are near the parameters based on distance formula on a sphere
SELECT LocationID, LocationDescription, Address1, Address2, Town, County, Postcode, Latitude, Longitude,
      (@intMilesModifier*acos(cos(radians(@dmlLat))*cos(radians(Latitude))*cos(radians(Longitude)-
       radians(@dmlLng))+sin(radians(@dmlLat))*sin(radians(Latitude)))) AS distance
FROM dbo.tblLocation
WHERE (@intMilesModifier*acos(cos(radians(@dmlLat))*cos(radians(Latitude))*cos(radians(Longitude)-
       radians(@dmlLng))+sin(radians(@dmlLat))*sin(radians(Latitude)))) < @intRadius

-- Return XML for formatting results
FOR XML RAW('marker'),ROOT('markers')

SET NOCOUNT OFF
GO

Executing the above stored procedure will render XML like the below:

<markers>
    <marker LocationID="1" LocationDescription="Earls Court" Address1="50 Earls Court Road" 
            Address2="Earls Court" Town="London" Postcode="W8 6LE" Latitude="51.4963500"
            Longitude="-0.1966400" distance="1.669166379205019e-001" />
    <marker LocationID="2" LocationDescription="Cromwell Place" Address1="50 Cromwell Road"
            Address2="Earls Court" Town="London" Postcode="W8 6LE" Latitude="51.4946400" 
            Longitude="-0.1920900" distance="2.747850170042376e-001" />
    <marker LocationID="3" LocationDescription="South Kensington Mall" Address1="10 Old Brompton Road"
            Address2="South Kensington" Town="London" Postcode="W8 6LE" Latitude="51.4929500"
            Longitude="-0.1772800" distance="9.144638212456977e-001" />
</markers>

That completes the SQL Server side of it. Now it is time to create some basic ASP.NET pages to show the location results.

You will essentially want two pages 1) A Search Request Page and 2) A Search Response Page.

Here is an snippet of code that demonstrates how the Search Request Page could look like (SearchRequest.aspx):

<div style="margin-left:20px;">


    <div style="margin-left: 5px;">
        <span style="font-size: 10px;">Please enter an address, town or post code and click search</span>
        <br />
        Address: <input type="text" id="txtSearchAddress" value="High Street Kensington" />&nbsp;
        Country: <input type="text" id="txtSearchCountry" value="United Kingdom" />&nbsp;
        Distance:
        <select id="ddlRadius">
          <option value="5" selected>5 miles</option>
          <option value="10">10 miles</option>
          <option value="25">25 miles</option>
          <option value="50">50 miles</option>
          <option value="100">100 miles</option>
        </select>&nbsp;
        <input type="button" onclick="searchLocations()" value="Search"/>
    </div>

    <br />

    <div style="clear:both; margin-left: 5px;">
        <div id="sidebar" style="overflow: auto; height: 400px; width:150px; font-size: 11px; color: #000; 
             float:left; margin-left:5px; padding-left:5px;">Search Results:</div>
        <div style="float:left; margin-left: 5px;">
            <div id="GoogleMap_Div_Container">
                <div id="map" style="width:750px;height:400px;"></div>
            </div>
            <!-- Google Map API Key -->
            <script src="http://maps.google.com/maps?file=api&amp;v=2&amp;key=YabbaDabbaDoo"  
                    type="text/javascript"></script>

        </div>

    </div>

    <br style="clear:both;" />

    

</div>   

Note: In the above, you might want to change the key value of “YabbaDabbaDoo” to your own API Key that you generated when you registered with Google Maps.

On the same page, we will load all the Google map JavaScript that sets up the map and the calls to the Google Maps API. Here is an example of the JavaScript you will need on the page:

<script language="javascript" type="text/javascript">

    // Google Map API Javascript

    var map;
    var geocoder;
    var http_request = false;
    var lat = 0;
    var lng = 0;
    var startingLat = 54.1509;  // view of england
    var startingLng = -4.4855;  // view of england
    var startingZoom = 5;
    var maximumZoom = 15;

    function mapLoad() {
        if (GBrowserIsCompatible()) {
            geocoder = new GClientGeocoder();
            map = new GMap2(document.getElementById('map'));
            map.addControl(new GSmallMapControl());
            map.addControl(new GMapTypeControl());
            map.enableScrollWheelZoom();
            map.setCenter(new GLatLng(startingLat, startingLng), startingZoom);
        }

    }

    function searchLocations() {

        var address = document.getElementById('txtSearchAddress').value;
        var country = document.getElementById('txtSearchCountry').value;
        var searchString = address + ', ' + country;

        geocoder.getLatLng(searchString, function(latlng) {
            if (!latlng) {
                alert(searchString + ' - not found');
            } else {
                searchLocationsNear(latlng);
            }
        });
    }

    function searchLocationsNear(center) {
        var radius = document.getElementById('ddlRadius').value;
        var searchUrl = 'SearchResponse.aspx?lat=' + center.lat() + '&lng=' + center.lng() + '&radius=' + radius;

        GDownloadUrl(searchUrl, function(data) {
            var xml = GXml.parse(data);
            var sidebar = document.getElementById('sidebar');
            sidebar.innerHTML = '';
            map.clearOverlays();

            if (xml.documentElement == null) {
                sidebar.innerHTML = 'No results found.  Please try widening your search area.';
                map.setCenter(new GLatLng(startingLat, startingLng), startingZoom);
                return;
            }

            var markers = xml.documentElement.getElementsByTagName('marker');

            if (markers.length == 0) {
                sidebar.innerHTML = 'No results found.  Please try widening your search area.';
                map.setCenter(new GLatLng(startingLat, startingLng), startingZoom);
                return;
            }

            var bounds = new GLatLngBounds();
            for (var i = 0; i < markers.length; i++) {
                var address1 = markers[i].getAttribute('Address1');
                var address2 = markers[i].getAttribute('Address2');
                var town = markers[i].getAttribute('Town');
                var postcode = markers[i].getAttribute('Postcode');
                var distance = parseFloat(markers[i].getAttribute('distance'));
                var point = new GLatLng(parseFloat(markers[i].getAttribute('Latitude')),
                             parseFloat(markers[i].getAttribute('Longitude')));

                var marker = createMarker(point, address1, address2, town, postcode);

                map.addOverlay(marker);
                var sidebarEntry = createSidebarEntry(marker, address1, address2, town, distance);
                sidebar.appendChild(sidebarEntry);
                bounds.extend(point);
            }

            var pointCenter = bounds.getCenter();
            var iZoomLevel = map.getBoundsZoomLevel(bounds);
            if (iZoomLevel > maximumZoom) { iZoomLevel = maximumZoom; }
            map.setCenter(pointCenter, iZoomLevel);

        });
    }

    function createMarker(point, address1, address2, town, postcode) {
        var marker = new GMarker(point);
        var html;
        if (address2 == null) {
            html = '<br/>' + address1 + '<br/>' + town + '<br/>' + postcode;
        }
        else {
            html = '<br/>' + address1 + ', ' + address2 + '<br/>' + town + '<br/>' + postcode;
        }
        GEvent.addListener(marker, 'click', function() {
            marker.openInfoWindowHtml(html);
        });
        return marker;
    }

    function createSidebarEntry(marker, address1, address2, town, distance) {
        var div = document.createElement('div');
        var address;
        if (address2 == '' || address2 == null) {
            address = address1 + '<br/>' + town;
        }
        else {
            address = address1 + '<br/>' + address2 + '<br/>' + town;
        }
        var html = '<b>' + distance.toFixed(2) + ' miles: </b><br/>' + address;
        div.innerHTML = html;
        div.style.cursor = 'pointer';
        div.style.marginBottom = '5px';
        GEvent.addDomListener(div, 'click', function() {
            GEvent.trigger(marker, 'click');
        });
        GEvent.addDomListener(div, 'mouseover', function() {
            div.style.backgroundColor = '#eee';
        });
        GEvent.addDomListener(div, 'mouseout', function() {
            div.style.backgroundColor = '#fff';
        });
        return div;
    }

</script>

When the page loads for the first time, we want to make sure we load up the map JavaScript variables. So in the code-behind of this SearchRequest.aspx page, we will add something like the below in the page load event handler:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Page.ClientScript.RegisterStartupScript(Me.GetType(), "mapLoad", 
                                           "<script language='javascript'> mapLoad(); </script>")

    End Sub

Okay, so all that is left is the guts for the page that will return the XML. If you create a new page (e.g. SearchResponse.aspx) in the same directory as the Search Request page, then all it needs to do is call the stored procedure that will return the locations as XML and return an XML response. The aspx will be empty except for the page declaration. The code-behind of this Search Response Page will look like the below:

Imports System.Xml

Partial Class SearchResponse
    Inherits System.Web.UI.Page

    ' Page Load
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim dmlSearchLatitude As Double
        Dim dmlSearchLongitude As Double
        Dim intSearchRadius As Int32

        If Request.QueryString("lat") <> "" Then
            dmlSearchLatitude = CDbl(Request.QueryString("lat"))
        End If
        If Request.QueryString("lng") <> "" Then
            dmlSearchLongitude = CDbl(Request.QueryString("lng"))
        End If
        If Request.QueryString("radius") <> "" Then
            intSearchRadius = CInt(Request.QueryString("radius"))
        End If

        ' Here you make the call to your locations stored procedure
        ' This database call is a little messy but is just to show you the point.
        ' You should really use the MS Application Blocks and/or some other seperate data layer
        Dim connDB = New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand
        connDB.ConnectionString = "Your database connection string"
        connDB.Open()
        cmd.Connection = connDB
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandTimeout = 20
        cmd.CommandText = "dbo.proc_Location_List"
        cmd.Parameters.AddWithValue("@dmlLat", dmlSearchLatitude)
        cmd.Parameters.AddWithValue("@dmlLng", dmlSearchLongitude)
        cmd.Parameters.AddWithValue("@intRadius", intSearchRadius)

        ' Execute the stored procedure and return the result as plain XML
        Dim rdrXMLLocations As XmlReader = Nothing
        rdrXMLLocations = cmd.ExecuteXmlReader()

        Response.Expires = 0
        Response.ContentType = "text/xml"
        Dim oDocument As New XmlDocument()
        Dim sb As New System.Text.StringBuilder()
        Using rdrXMLLocations
            While Not rdrXMLLocations.EOF
                rdrXMLLocations.MoveToContent()
                sb.Append(rdrXMLLocations.ReadOuterXml())
            End While
            rdrXMLLocations.Close()
        End Using
        oDocument.LoadXml(sb.ToString())
        oDocument.Save(Response.Output)
        Response.OutputStream.Flush()
        Response.OutputStream.Close()

    End Sub

End Class

Hopefully that should be enough to get the Google Maps API in action for you.

Click here to download a zip file of the example code used in this article.

Hope this helps,

Stefan.

For more Web Code, ASP.NET, SQL Server and other development tips, please check back here at webcodeblog.com often.