×

First time here?

You are looking at the most recent posts. You may also want to check out older archives. Please leave a comment, ask a question and consider subscribing to the latest posts via RSS or email. Thank you for visiting!

or "find stuff near me" and how i did it. (long read sorry)

after my last post about using the proximity search article for sql server 2000, i received some requests for a simple walkthrough on how i implemented my app.  well, here's an attempt at a very simple sample.

first, so we are clear, my scenario was as follows: given a known single point, search a database of other known points and return a result set of those points that are known to be within a certain mile radius of the single known point.  better put "find locations near me" :-)

let's use a sample database for the known points.  i'm using sql server 2005 express which is free.  here's my table structure for known points (for this sample):

   1:  CREATE TABLE [dbo].[Locations](
   2:      [LocationId] [int] IDENTITY(1,1) NOT NULL,
   3:      [LocationName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   4:      [Address] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   5:      [City] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   6:      [State] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   7:      [Zip] [nvarchar](9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   8:      [Latitude] [float] NOT NULL,
   9:      [Longitude] [float] NOT NULL,
  10:      [XAxis] [float] NOT NULL,
  11:      [YAxis] [float] NOT NULL,
  12:      [ZAxis] [float] NOT NULL,
  13:   CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED 
  14:  (
  15:      [LocationId] ASC
  16:  )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
  17:  ) ON [PRIMARY]


once you have the locations, there are two key steps here: 1) geocoding those locations and 2) establishing the proximity calculations for the axis points.  the first is rather simple.  you essentially have to use a geocoding service to perform the action.  in my application i use my and the yahoo geocode apis.  i've found them to be accurate and current.  google also has some as well.  both are 'free' (certain number of transactions) but you have to register your application.  i think yahoo is also implementing referrer-based checking as well, which might be a consideration.  either way, these will geocode your address.

suggestion: you can abstract this from your users very easily.  when they enter an address to save into your application UI, take an intermediary step to verify and geocode the address...once you have the lat/long then you are ready to save it to the database.

the next step is calculating the axis.  i'm honestly not going to sit here and pretend to tell you that i'm a geospacial expert on the laws of longitude, because i'm not.  basically the math that is performed makes calculations based on the radius and axis point of the earth.  no, i'm serious.  you'll see later one of the parameters is earth radius.  once you have the known lat/long of your addresses, then you have to calc the axis points.  to simplify this for me, i created three functions in sql server like this one:

   1:  CREATE FUNCTION [dbo].[XAxis] 
   2:      (
   3:      @lat float,
   4:      @lon float
   5:      )
   6:  RETURNS float
   7:  AS
   8:      BEGIN
   9:      RETURN COS(4 * (4 * atn2(1, 5) - atn2(1, 239)) / 180 * @lat) * COS(4 * (4 * atn2(1, 5) - atn2(1, 239)) / 180 * @lon)
  10:      END


the others are similar and you can get the scripts at the bottom of this post.  once you have those in your database for reusability, you have two options: stored procedure or trigger.  stored procedure (or rather, implementing within your insert/update routine) is probably most ideal.  you want to ensure the axis calculations (and also the geocoding) maintains integrity.  after all, if the user updates the address, you want to update all that information!  so within your stored proc you can simply update the XAxis, YAxis, and ZAxis fields using these functions using something like:

   1:  UPDATE Locations SET XAxis = dbo.XAxis(@lat, @long)


you could also do this in a trigger (as i was using), but it was pointed out to me (thanks bert) that using an AFTER UPDATE trigger might send this in an infinite loop as my trigger was performing an update statement itself.  stored proc is the way to go if you can.  i've included the INSERT trigger in the files just for your perusal though.

okay, with that done, you should have a sequence of when an address is entered (again possibly adding the intermediary step of geocoding for the user) you now have all the information you need.  now when your user needs to perform a "find near me" query the process is simple.

first, you'll need to geocode their asking point.  again, this is simple and can be accomplished by various geocoding apis.  once you have the lat/long of the asking point, you can feed them to the stored proc that will use your previous proximity information and find the ones nearest you.  here's the stored proc:

   1:  CREATE PROCEDURE [dbo].[FindNearby]
   2:     @CenterLat float, 
   3:     @CenterLon float, 
   4:     @SearchDistance float, 
   5:     @EarthRadius float
   6:  AS
   7:  declare @CntXAxis float
   8:  declare @CntYAxis float
   9:  declare @CntZAxis float
  10:   
  11:  set @CntXAxis = cos(radians(@CenterLat)) * cos(radians(@CenterLon))
  12:  set @CntYAxis = cos(radians(@CenterLat)) * sin(radians(@CenterLon))
  13:  set @CntZAxis = sin(radians(@CenterLat))
  14:   
  15:  select *,  ProxDistance = @EarthRadius * acos( XAxis*@CntXAxis + 
  16:    YAxis*@CntYAxis + ZAxis*@CntZAxis)
  17:  from  Locations
  18:  where  (@EarthRadius * acos( XAxis*@CntXAxis + YAxis*@CntYAxis + 
  19:    ZAxis*@CntZAxis) <= @SearchDistance) AND latitude is not null
  20:  order by ProxDistance ASC


you'll notice the parameters of radius and earth radius.  since i'm using miles, i use the earth radius of 3961 miles.  you can search on live.com and other places for another number, but this seemed to be a general consensus of the radius of the earth in miles.  i put this in my configuration file in case i needed to change it.  no, not in case the earth changed, but in case it needed to be kilometers.  the SearchDistance param needs to be in the same unit of measurement as the earth radius.  feed those and your lat/long in and you get those near the point -- as well as the approximate distance in the same unit of measure.  boom, you are done.  do with the data as you wish.

using virtual earth, you can easily plot those points as i did in my sample application.  heck using you could also geocode for you in your user interface.

suggestion: you could present a map in your UI.  have the user enter an address...if found you can have them click on the point to verify and capture that lat/long from virtual earth, then sending back to your procedure and skipping that functional step on the backend.

that's it.  below are the files for the scripts to play around if you'd like.

Files: ProximitySampleSqlScripts


1/31/2007 3:26 PM | # re: implementing a proximity search in sql server
Thanks for the example Tim.
4/14/2007 3:13 PM | # re: implementing a proximity search in sql server
FYI, the SQL Scripts don't work for those still with SQL 2000. :) I was able to work around it.
4/14/2007 10:25 PM | # re: implementing a proximity search in sql server
Okay, I have this workingnow in SQL 2000. Stepping back for a moment to analyze how this will impact server performance....the WHERE clause in the stored procedure 'FindNearby' is basically going to do a table scan. We know what happens once this table gets many records.

Anyone care to post their plan on how to filter a large table with indexes to find matches?
5/28/2007 5:34 AM | # re: implementing a proximity search in sql server
You should use the simpler version of the Spherical Law of Cosines formular in your query, as SQL 2005 understands the radians function.

Instead of:
COS(4 * (4 * atn2(1, 5) - atn2(1, 239)) / 180 * @lat) * COS(4 * (4 * atn2(1, 5) - atn2(1, 239)) / 180 * @lon)

Use:
cos(radians(Latitude)) * cos(radians(Longitude))

You get the same results.
8/20/2008 8:05 PM | # re: implementing a proximity search in sql server
Thanks for the help. Those equations bring me back to my college days. Also your equations work equally as well in other database systems.
9/30/2009 4:32 AM | # re: implementing a proximity search in sql server
Hi Tim,

Thanks for the information above.

I am having some problems with it though and I was hoping you could help?

I have run the create scripts with no errors.

I then added a row into the table with correct lat/longs and zeros for the xaxis,yaxis and zaxis.

I then ran the UPDATE Locations SET XAxis = dbo.XAxis(@lat, @long) but i get the error "Must declare the scalar variable "@lat""

Any help would be very much appreciated!

Trev
9/30/2009 8:29 AM | # re: implementing a proximity search in sql server
Trevor -- you have to provide that variable value to the query.
1/15/2010 4:22 PM | # re: implementing a proximity search in sql server
Wow! Just what I was looking for, nice work and I will let you know if I find success in an implementation!
1/27/2010 10:33 PM | # re: implementing a proximity search in sql server
A tip to speed things up would be to reduce the dataset to start with by using a bounding box where, eliminating those outside a box containing the radius of interest, then an index on the lat/long can be directly used, then the bits in the corners would be trimmed off.
While not perfect, will reduce the number of rows considered, and probably remove a complete table scan...
Gravatar
3/18/2011 10:03 AM | # re: implementing a proximity search in sql server
Would you happen to have the scripts that could be used with SQL Server 2008 and the new Geography field?
3/18/2017 6:46 AM | # re: implementing a proximity search in sql server
i was searching the code to implementing a proximity search in sql server, but finally found here.
3/20/2017 4:12 AM | # re: implementing a proximity search in sql server
this code explanation is that much good anybody can understand.
3/24/2017 11:03 PM | # biharboard.ac.in
Bihar 12th Results 2017 For Science, Arts & Commerce: It is really pleasant news to all the candidates, those who are awaiting the Bihar Board 12th Results 2017 after the triumphant completion of Examinations
3/28/2017 2:55 AM | # re: implementing a proximity search in sql server
Commerce: It is really pleasant news to all the candidates, those who are awaiting the Bihar Board 12th Results 2017 after the triumphant completion of

 
Please add 2 and 4 and type the answer here:

DISCLAIMER:

The opinions/content expressed on this blog are provided "ASIS" with no warranties and are my own personal opinions/content (unless otherwise noted) and do not represent my employer's view in any way.