A scenario familiar to many web developers is the requirement to provide a location search to your client’s users. The user enters their address and/or zip code, and is shown the 5 closest locations that they can visit. Add information from your Inventory system, and you can tell the user which locations have the item they are looking for in stock.

Most developers are very aware of the power of SQL Server. With SQL Server 2008, Microsoft added the support for spatial searches, allowing you to calculate the distance between physical locations, making this type of search that much easier.

In this example, I will walk you through setting up the database tables and queries you need to perform searches by location.

First steps: Table configuration

Let’s take a look at the Locations table. Add a Geography type field to store the location data and two Float fields store the latitude and longitude of your location. You’ll see how these are used in just a moment.

The key to storing the location as a geography data type is the STGeometryFromText method. The 4326 is a reference to SRID 4326, which is the most widely used system to reference points on the earth. The key to remember is that whichever SRID you use, you must remain consistent, as using different spatial reference systems will provide inconsistent results. Unfortunately, the calculated column cannot be persisted, so you might consider not using the calculated column, and manually populating the data on insert.

Once you have created the table, it’s time to populate your locations. We will need to find the Latitude and Longitude of each address. In a future blog I will show how simple it is to do using the Bing API. For now, we can simply use http://geocoder.us/ to get a few addresses for test data.

Using data from GeoNames.Org, I uploaded the latitude and longitude for over 43,000 US Zip codes into a table called ZipCodes in my test system.

This will be used for the queries we build to search by zip code.

Digging deeper: Querying by location

Now that you have your locations set up, it’s time to begin using all that data.

The query below will find all the locations within 100 miles of the White House.

Here we use the STDistance method to calculate the distance between each location and the geographic location of the search center (geography::Point(@Latitude, @Longitude, 4326)). If, instead, you wanted to find all locations within a certain range of a specified zip code, you would change the query that populates the @Latitude and @Longitude variables as follows:

Results:

Results are sorted by distance.

 

Wrapping it up: Using a  stored procedure

Now that you can query by range, the obvious thing to do is put this together in a stored procedure that can be easily reused.

Here we’ve added the option to search by Miles, Kilometers, or Meters, and combined zip code and latitude/longitude searching.

Now it’s simple to search for all locations within 100 KM of Zip code 20502 by calling  exec [p_FindLocationsInRange]‘20502’,null,null, 100,‘KM’

 

In part two,  I’ll show how to integrate this spatial search into your website using the Bing API to convert addresses to Latitude and Longitude, and display your results on a map.

Like this post? Share it!