GeoLocation by Radius Using Google Maps and .NET

• Download source code - 67.7 KB

Introduction

A few years back, I helped to integrate a company's database of national sales stores/locations with a third-party website. We've all seen these "find the nearest store" locator-type pages.

This article will describe, from start to finish, how to take your own database of addresses, geocode them (generate lat and long values), and display them in the order of distance from a given address.

A working knowledge of Web Services, SQL Server functions, Stored Procedures, and IIS is required.

Background

There are several articles and examples out there on using the Google API. As a programmer, I would prefer accessing a Web Service (like their search service), but there are always ways around this.

The essence of this project is getting the lat/long values from the Google Mapping API. At first, I struggled with writing a JavaScript wrapper, then found a few, but they didn't really fit the bill. After taking more time to read through the API docs, I found that the API will return different data formats, which was perfect for what I needed.

Alternately, Sharmil Y Desai's article, "A .NET API for the Google Maps Geocoder" lists a nice little project to achieve some of the same functionality.

Project Steps

For this project, we will need to consider what the requirements are:

1. Create a database of addresses we can geocode - add lat/long values.
2. Create a process to update address records with lat/long values.
3. Create a page/WebService that accepts an address used to compare against our database.
4. Create an algorithm to compare a given address against our database and return results.

Address Table - Database

Our address table, tbl_GeoAddresses, will contain the basic address fields - AddressID, Street, City, State, Zip. Additional fields are Geo_Lat, Geo_Long, GeoAddressUsed, and GeoAddedDate.

Copy/paste the following script to create the table:

/****** Object:  Table [dbo].[tbl_GeoAddresses]  Script Date: 01/23/2009 11:04:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbl_GeoAddresses](
    [AddressID] [int] IDENTITY(1,1) NOT NULL,
    [Street] [varchar](50) NULL,
    [City] [varchar](50) NULL,
    [State] [varchar](50) NULL,
    [Zip] [varchar](50) NULL,
    [Name] [varchar](50) NULL,
    [Geo_Lat] [varchar](50) NULL,
    [Geo_Long] [varchar](50) NULL,
    [GeoAddressUsed] [varchar](128) NULL,
    [GeoAddedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_tbl_GeoAddresses] PRIMARY KEY CLUSTERED 
(
    [AddressID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tbl_GeoAddresses] 
ADD  CONSTRAINT [DF_tbl_GeoAddresses_GeoAddedDate]
DEFAULT (getdate()) FOR [GeoAddedDate]
GO

Geocoding Service - svcGeoSearch

Since we will need to geocode not only addresses in our database, but also incoming addresses (those we will compare against our existing addresses) for distance calculations, we will construct a reusable Web Service which will return the lat/long values for a given address.

The Google API accepts addresses in various formats, from just the Zip code, to city/state, and address locations.

In the project Zip file, refer to the svcGeoSearch project. You will have to add your own Google Maps Key in the web.config.

The main call to the Google Maps API is made in the following line:

Dim gmapRequest As String = "http://maps.google.com/maps/geo?key=" & _
     ConfigurationManager.AppSettings("GMapKey") & _
     "&q=" & address & "&sensor=false&output=xml"

The output type is defined with the output parameter, which accepts {kml, xml, csv}. Having an XML output eliminates the need for some other wrappers, since we can easily parse XML files.

The main parsing is accomplished in the following lines:

Try
    coordinatesNodeList = xmlGeo.GetElementsByTagName("coordinates")
    coordinates = coordinatesNodeList.Item(0).InnerText
    coordinates = coordinates.Substring(0, coordinates.LastIndexOf(","))
Catch ex As Exception
    statusNodeList = xmlGeo.GetElementsByTagName("code")
    statusCode = statusNodeList.Item(0).InnerText
    coordinates = statusCode & "," & statusCode
End Try

Some additional error checking/validation could be performed here, but the preceding code will do for most cases. Status code errors are returned for things like too many queries (over 15,000/day), unknown addresses, etc. To see a full list of the Google Mapping status codes, click here.

Normally, the lat/long values are returned, but if not, and a status code is the result, it is returned from the service, i.e., "620,620". Now, set this service up on your local IIS box. If using IIS 7, set it up as an application. After launching it, you will see the service displayed. Enter an address, or some address varieties, and you'll get the lat/long values returned:

<?xml version="1.0" encoding="utf-8" ?>
<string xmlns="http://svcGeoSearch/">-118.2370170,34.0597670</string>

Geocoding Our Database - ProviderGeoCodingScheduledTask

Now that we have a tool that will return the lat/long values for a given address, we want to create an application that will do this for all the addresses in our table.

Refer to the console application called ProviderGeoCodingScheduledTask in the project Zip file. This project consumes the previously-created Web Service, and you will have to add this web reference, removing the existing one (GeocodingService). In Module1.vb, change line 41 to reference the name of your Web Service:

Dim GeoSearch As New GeocodingService.svcGeoSearch

The code in this file (Module1.vb) is pretty straightforward. We begin by selecting all the records, and setting each field to a variable for processing.

Some rules were added - such as OmitRecord() - where if certain keywords appear in the address, the record will be omitted.

Addresses are parsed in ParseStreet() for additional qualifiers such as "suite #", "ste", and "unit". These address portions are stripped as they are not relevant to the geolocation, and are also not accepted by the Google API (go ahead, try typing these address types in the svcGeoSearch address).

Some Zip codes can appear as "90044" or "900443342". The Google-accepted format is "90044-3342", which is taken care of by:

If zip.Length() > 5 Then
    zip = zip.Insert(5, "-")
End If

There is also a 1/2 second delay built in between requests.

The geocodes are finally added in the SQL update, where GeoAddressUsed is also added. This contains the actual address used to find the lat/long values (as some portions of the original address may have been stripped).

Using updateCommand As New SqlCommand("UPDATE tbl_GeoAddresses SET Geo_Lat=@Geo_Lat,
    Geo_Long=@Geo_Long, GeoAddressUsed=@GeoAddressUsed,
    GeoAddedDate=GetDate() WHERE AddressID=@AddressID AND ZIP=@OrigZip", sqlConn)
    With updateCommand
        .CommandType = CommandType.Text
        .Parameters.Add("Geo_Lat", SqlDbType.VarChar).Value = geoLat
        .Parameters.Add("Geo_Long", SqlDbType.VarChar).Value = geoLong
        .Parameters.Add("GeoAddressUsed", SqlDbType.VarChar).Value = formattedAddress
        .Parameters.Add("AddressID", SqlDbType.Int).Value = AddressID
        .Parameters.Add("OrigZip", SqlDbType.VarChar).Value = origzip
        updateCount = .ExecuteNonQuery()
    End With
End Using

As a console application, you can set this up as a Windows Scheduled Task, if this table is updated on a regular basis with new addresses. Depending on the size of your data, you may want to add additional filtering rules, such as, only update records that have not been updated in the past five days:

SELECT * FROM tbl_GeoAddresses WHERE GeoAddedDate < GetDate() - Day(5)

GeoAlgorithm - SQL Function - CalcDistanceBetweenLocations

So far, we have a Web Service that returns lat/long values, and a database table full of addresses with lat/long values. We now need a method to calculate the distance between two lat/long points - distance between {lat, long} and {lat, long}.

The most efficient way is to create a scalar valued function on our SQL Server to crunch the numbers for us:

/****** Object:  UserDefinedFunction [dbo].[CalcDistanceBetweenLocations]
        Script Date: 01/23/2009 12:23:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[CalcDistanceBetweenLocations]
      (@LatitudeA       FLOAT = NULL,
       @LongitudeA       FLOAT = NULL,
       @LatitudeB       FLOAT = NULL,
       @LongitudeB       FLOAT = NULL,
       @InKilometers      BIT = 0
       )
RETURNS FLOAT
AS
BEGIN
      -- just set @InKilometers to 0 for miles or 1 for km
      -- ex:  SELECT dbo.CalcDistanceBetweenLocations (30.123,27.1,28.14,32.23, 0)

      -- select field1, field2, dbo.CalcDistanceBetweenLocations(lat1, long1, lat2,
      -- long2, 0) as distance from yourtable
      -- where dbo.CalcDistanceBetweenLocations(lat1, long1, lat2,
      -- long2, 0) <= 10 --within the ten miles range
      DECLARE @Distance FLOAT

      SET @Distance = (SIN(RADIANS(@LatitudeA)) *
              SIN(RADIANS(@LatitudeB)) +
              COS(RADIANS(@LatitudeA)) *
              COS(RADIANS(@LatitudeB)) *
              COS(RADIANS(@LongitudeA - @LongitudeB)))

      --Get distance in miles
        SET @Distance = (DEGREES(ACOS(@Distance))) * 69.09

      --If specified, convert to kilometers
      IF @InKilometers = 1
            SET @Distance = @Distance * 1.609344

      RETURN @Distance

END

This function accepts four values - two for Point A lat/long, two for Point B lat/long, and the final to output in miles or KMs - use 0 for miles, 1 for KMs.

This function will only perform the calculations for one set of points, A and B. But, what about our whole table of addresses?

Geocode Our Table - SPROC - sproc_ReturnGeoProviders

To calculate the distance between the points in our address table and another point, we will create a Stored Procedure to accept the other point's lat/long, use the distance calculating function, and return addresses that are within a given radius.

/****** Object:  StoredProcedure [dbo].[sproc_ReturnGeoProviders] 
        Script Date: 01/23/2009 12:27:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sproc_ReturnGeoProviders]
    @clientLat        Float = Null,
    @clientLong        Float = Null,
    @maxRadius        Int = Null
AS
BEGIN
    CREATE TABLE #Listings
    (
    AddressID varchar(50), Street varchar(50), City varchar(50), State varchar(50), 
        Zip varchar(50), Name varchar(50), Geo_Lat varchar(50), Geo_Long varchar(50),
         GeoAddressUsed varchar(128), Distance Decimal(18,12)
    )
    INSERT INTO #Listings (AddressID, Street, City, State, Zip, Name, Geo_Lat,
             Geo_Long, GeoAddressUsed, Distance)
    SELECT AddressID, Street, City, State, Zip, Name, Geo_Lat, Geo_Long,
             GeoAddressUsed,
    dbo.CalcDistanceBetweenLocations(@clientLat, @clientLong,
             tbl_GeoAddresses.Geo_Lat, tbl_GeoAddresses.Geo_Long, 0) AS Distance
    FROM tbl_GeoAddresses
    WHERE dbo.CalcDistanceBetweenLocations(@clientLat, @clientLong,
             tbl_GeoAddresses.Geo_Lat, tbl_GeoAddresses.Geo_Long, 0) <= @maxRadius
    ORDER BY Distance ASC
    
    SELECT * 
    FROM #Listings
    -- temp table is already ordered by distance
END

As you can see, the Stored Procedure accepts the the lat/long values of a given point, and a radius value.

In order to be able to sort our results, we create a temp table, whose lifespan lasts for the length of the process call, add our results, sort, and return the table.

The juicy calculations happen here:

dbo.CalcDistanceBetweenLocations(@clientLat, @clientLong, _
    tbl_GeoAddresses.Geo_Lat, tbl_GeoAddresses.Geo_Long, 0)

This is where we feed the function the lat/long values sent to the Stored Procedure, and compare them against the table's lat/long fields, returning each record's distance from the given point.

Addresses in a Given Radius - wsPublic/svcProviderSearch

Code for the database function and Stored Procedure are located in this project - wsPublic/DatabaseProcs.txt.

We almost have all the pieces together to get this thing working. We have a service that returns lat/long values, our database full of geocoded addresses, and a method to calculate the distance from our addresses to a given point. The next thing we need to do is create some interface to enter that given point. For this, we'll use a Web Service. Refer to wsPublic/svcProviderSearch.asmx in the project Zip file. We actually want to look in the project's App_Code folder for svcProviderSearch.vb. This contains the code to process the incoming request.

There is a DataSet file already present, but if you want to create your own new one, right-lick on the App_Code folder, select Add New Item... / DataSet. Open the DataSet file (.xsd), and right-click on the main pane. Select Add / TableAdapter, and create or use an existing connection string to your database. This table adapter will connect to our new Stored Procedure.

After choosing/creating a database connection string, click Next to Choose a Command Type. Select the Use Existing Stored Procedures option. In the Select drop-down, select "sproc_ReturnGeoProviders (dbo)" - this is the Stored Procedure we just created. Click Next twice, and the Wizard Results will indicate there is a problem with the Stored Procedure. This is because we created a temp table within it called #Listings. Just click on Finish.

Now, inside svcProviderSearch.vb, look at the GetLocalProvidersGeo() function. This WebMethod accepts a Zip code (or address), and a maximum radius value. We need to geocode the incoming Zip code (or address). Since we're primarily dealing with Xip codes, and our address table is probably pretty large, instead of hitting the Google Mapping API first, let's take a look in our own database to see if we have the lat/long values for the given Zip code:

Dim sqlQuery As String = String.Format(
    "SELECT TOP 1 * FROM tbl_GeoAddresses WHERE ZIP='{0}'", strZip)
Dim hasGeoCode As Boolean = False

' first check if we have the lat/long values for the given zip code.
' if not, then access the service
sqlCmd.Connection.Open()
Dim dr As SqlDataReader = sqlCmd.ExecuteReader()
While dr.Read()
    geoLat = dr("Geo_Lat")
    geoLong = dr("Geo_Long")
    hasGeoCode = True
End While
sqlCmd.Connection.Close()
dr.Close()

If Not hasGeoCode Then
    latlong = Me.GetLatLong(pZip)

    If latlong.Length > 0 Then
        geoLat = Trim(latlong.Substring(0,
            latlong.IndexOf(","))).Replace("<point><coordinates>", "")
        geoLong = Trim(latlong.Substring(latlong.IndexOf(",") + 1,
            (latlong.Length - latlong.IndexOf(",") - 1)))
    End If
End If

Now that we have a lat/long values for the given Zip code, we will return a DataTable of results with the final line:

Return adpSearch.GetData(CType(geoLat, Double), _
       CType(geoLong, Double), CType(pRadius, Int32))

Running the Code

I've included the GetLatLong() function in svcProviderService, but the one we're currently interested in is GetLocalProvidersGeo().

Launch this Web Service and select GetLocalProvidersGeo(). I've got two addresses in my table, and to return them all, I used a radius of 100 miles with a local Zip code:

<?xml version="1.0" encoding="utf-8" ?>
<sproc_ReturnGeoProvidersDataTable xmlns="http://wsPublic/">
<xs:schema id="NewDataSet" xmlns="" 
    xmlns:xs="http://www.w3.org/2001/XMLSchema" 
    xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true"
     msdata:MainDataTable="sproc_ReturnGeoProviders" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="sproc_ReturnGeoProviders">
<xs:complexType>
<xs:sequence>
  <xs:element name="AddressID" type="xs:string" minOccurs="0" />
  <xs:element name="Street" type="xs:string" minOccurs="0" />

  <xs:element name="City" type="xs:string" minOccurs="0" />
  <xs:element name="State" type="xs:string" minOccurs="0" />
  <xs:element name="Zip" type="xs:string" minOccurs="0" />
  <xs:element name="Name" type="xs:string" minOccurs="0" />
  <xs:element name="Geo_Lat" type="xs:string" minOccurs="0" />
  <xs:element name="Geo_Long" type="xs:string" minOccurs="0" />
  <xs:element name="GeoAddressUsed" type="xs:string" minOccurs="0" />
  <xs:element name="Distance" type="xs:decimal" minOccurs="0" /> 
  </xs:sequence>
  </xs:complexType>
  </xs:element>
  </xs:choice>
  </xs:complexType>
  </xs:element>
  </xs:schema>
  <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
      xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
  <DocumentElement xmlns="">
  <sproc_ReturnGeoProviders diffgr:id="sproc_ReturnGeoProviders1" msdata:rowOrder="0">
  <AddressID>2</AddressID>
  <Street>988 N Hill St # 201</Street>
  <City>Los Angeles</City>
  <State>CA</State>
  <Zip>90012</Zip> 
  <Name>Empress Pavilion Restaurant</Name> 
  <Geo_Lat>-118.2366158</Geo_Lat> 
  <Geo_Long>34.0684130</Geo_Long> 
  <GeoAddressUsed>988 N Hill St, Los Angeles, CA 90012</GeoAddressUsed> 
  <Distance>1.818756506951</Distance> 
  </sproc_ReturnGeoProviders>
  <sproc_ReturnGeoProviders diffgr:id="sproc_ReturnGeoProviders2" msdata:rowOrder="1">
  <AddressID>1</AddressID> 
  <Street>617 S Olive St</Street> 
  <City>Los Angeles</City> 
  <State>CA</State> 
  <Zip>90014</Zip> 
  <Name>Cicada Restaurant</Name> 
  <Geo_Lat>-118.2537740</Geo_Lat> 
  <Geo_Long>34.0493890</Geo_Long> 
  <GeoAddressUsed>617 S Olive St, Los Angeles, CA 90014</GeoAddressUsed> 
  <Distance>2.905266224802</Distance> 
  </sproc_ReturnGeoProviders>
  </DocumentElement>
  </diffgr:diffgram>
  </sproc_ReturnGeoProvidersDataTable>

You can easily consume this Web Service in another project to output these values to a web page or some other application.

Points of Interest

There are many sources for interfacing with the Google Mapping API. Most of them seem over-complicated. Simply retrieving XML data is the simplest, for this particular case.

I would appreciate hearing any comments or feedback on this article - post them here, and vote!