Gal Ratner
Gal Ratner is a Techie who lives and works in Los Angeles CA and Austin TX. Follow galratner on Twitter Google
Spatial Search made easy with Google Maps API and SQL Server 2008

Spatial search is the process of searching locations nearby to a position in space.  A good example of a spatial search would be finding all of the restaurants near your present location. With Google’s Maps API and SQL Server’s Geography data types we can build a quick spatial search in no time.


In this little exercise I will be detecting the user’s location, displaying it on a map and suggesting nearby restaurants. Since not all browsers support the W3C standards I will also allow manual input of the user’s address.
Spatial search relies on latitude and longitude which are the global position coordinates. The process of turning an address to a set of latitude and longitude coordinates is referred to as Geocoding.


Let’s start by adding the Google Maps API along with the auto user location detection. We are also going to add a service all to our database. The database will contain the recommended restaurants along with their latitude and longitude. The service call will be invoked by clicking the button when viewing the page with a non Geolocation supported browsers.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.2/jquery.min.js"></script>
    <script type="text/javascript" src="http://maps.google.com/maps/api/js?sensor=true"></script>
    <script type="text/javascript" src="http://code.google.com/apis/gears/gears_init.js"></script>
<script type="text/javascript">
 
    var initialLocation;
    var map;
    var infowindow = new google.maps.InfoWindow();
    var latitude;
    var longitude;
 
    function initialize() {
        var myOptions = {
            zoom: 10,
            mapTypeId: google.maps.MapTypeId.ROADMAP
        };
        map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
 
        // Try W3C Geolocation method (Preferred)
        if (navigator.geolocation) {
            navigator.geolocation.getCurrentPosition(function (position) {
                latitude = position.coords.latitude;
                longitude = position.coords.longitude;
                initialLocation = new google.maps.LatLng(position.coords.latitude, position.coords.longitude);
                contentString = "Location found using W3C standard";
                map.setCenter(initialLocation);
                infowindow.setContent(contentString);
                infowindow.setPosition(initialLocation);
                infowindow.open(map);
            }, function () {
                handleNoGeolocation();
            });
        } else if (google.gears) {
            // Try Google Gears Geolocation
            var geo = google.gears.factory.create('beta.geolocation');
            geo.getCurrentPosition(function (position) {
                latitude = position.coords.latitude;
                longitude = position.coords.longitude;
                initialLocation = new google.maps.LatLng(position.latitude, position.longitude);
                contentString = "Location found using Google Gears";
                map.setCenter(initialLocation);
                infowindow.setContent(contentString);
                infowindow.setPosition(initialLocation);
                infowindow.open(map);
            }, function () {
                handleNoGeolocation();
            });
        } else {
            // Browser doesn't support Geolocation
            handleNoGeolocation();
        }
    }
 
    function handleNoGeolocation(errorFlag) {
        $('#noLocation').show();
    }
 
    function codeAddress() {
        var geocoder = new google.maps.Geocoder();
        geocoder.geocode({ 'address': $("#LocationText").val() }, function (results, status) {
            if (status == google.maps.GeocoderStatus.OK) {
                map.setCenter(results[0].geometry.location);
                var marker = new google.maps.Marker({
                    map: map,
                    position: results[0].geometry.location
                });
                latitude = results[0].geometry.location.lat();
                longitude = results[0].geometry.location.lng();
                getNearby();
            } else {
                alert("Geocode was not successful for the following reason: " + status);
            }
        });
    }
 
    $(function () {
        initialize(); ;
    });
 
    function getNearby() {
        jQuery.ajax({
            url: 'NearbyLocations.svc/GetNearbyLocations',
            data: "latitude=" + latitude + "&longitude=" + longitude,
            dataType: 'json',
            success: function (data) {
                $.each(data.d, function (i, item) {
                    var myLatlng = new google.maps.LatLng(item.Latitude, item.Longitude);
                    var marker = new google.maps.Marker({ position: myLatlng, title: item.Description });  
                    marker.setMap(map);  
                })
            }
        });
    }
</script>
 
</head>
<body>
    <form id="form1" runat="server">
    <div id="noLocation" style="display:none">Enter Address 
    <input id="LocationText" type="text" value="Beverly Hills, California" /><input id="AddressButton1" type="button" value="Set Address" onclick="codeAddress();" /></div>
 
    <div id="map_canvas" style="height:500pxwidth:800px"></div>
    </form>
</body>
</html>


This is our database table:



I have added some of my favorite restaurants:



In order to search the database for locations close to ours we will convert our latitude and longitude coordinates to the SQL data type GEOGRAPHY and search for places nearby using STDistance. STDistance will return the distance in meters between two GEOGRAPHY points so we need to convert it to miles.

USE [GeolocationExample]
GO
 
/****** Object:  StoredProcedure [dbo].[GetNearbyLocations]    Script Date: 05/14/2011 08:25:25 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
CREATE PROCEDURE [dbo].[GetNearbyLocations] 
	@Distance int,
	@Latitude float,
	@Longitude float
AS
BEGIN
	
	SET NOCOUNT ON;
 
    DECLARE @StartPoint geography;
	SET @StartPoint = GEOGRAPHY::STGeomFromText('Point(' + CAST(@Longitude AS VARCHAR(32)) + ' ' + CAST(@Latitude AS VARCHAR(32)) + ')',4326)
	
	SELECT * FROM Location L
	WHERE
    GEOGRAPHY::STGeomFromText('Point(' + CAST(L.Longitude AS VARCHAR(32)) + ' ' + CAST(L.Latitude AS VARCHAR(32)) + ')',4326).STDistance(@StartPoint) <= (@Distance / 0.000621371192)
 
END
 
GO
 
 

The final step will be using a WCF service to invoke our stored procedure along with our desired search radius:

using System.Collections.Generic;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.ServiceModel.Web;
 
using GeolocationExampleModel;
 
[ServiceContract(Namespace = "")]
[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
public class NearbyLocations
{
    [OperationContract]
    [WebGet]
    public List<GeoLocation> GetNearbyLocations(double latitude, double longitude)
	{
        WebOperationContext.Current.OutgoingResponse.Headers.Add("Cache-Control""no-cache"); WebOperationContext.Current.OutgoingResponse.Headers.Add("Pragma""no-cache");
        List<GeoLocation> locations = new List<GeoLocation>();
        int distance = 20; // Miles
        using (GeolocationExampleEntities context = new GeolocationExampleEntities())
        {
            var nearby = context.GetNearbyLocations(distance, latitude, longitude);
            foreach (var near in nearby)
                locations.Add(new GeoLocation() { Description = near.Description, Latitude = near.Latitude, Longitude = near.Longitude });
        }
        return locations;
	}
 
    [DataContract]
    public class GeoLocation
    {
        [DataMember]
        public string Description { getset; }
        [DataMember]
        public double Latitude { getset; }
        [DataMember]
        public double Longitude { getset; }
    }
}

Here I used Entity Framework in order to return the results from the database, however, I needed to convert them to a valid data contract in order to pass them to the client.

This is the final Map:

The map contains all three records as markers.


Conclusion:
As you can tell spatial search can be composed together fairly quickly, however, as always when we are using 3rd party components we are subject to Google’s terms of service. I hope you find this useful.

Shout it


Posted 14 May 2011 4:20 PM by Gal Ratner
Filed under: ,

Powered by Community Server (Non-Commercial Edition), by Telligent Systems