Great Circle Distance Formula: T-SQL

Question

So I have a table with a bunch of different addresses in it. I need a proc that will select the addresses in that table that are within a specified distance in miles from the passed in lat/long values.

So example of my table:

- messageId
- lat (float)
- long (float)

Proc is passing in another lat/long pair (both floats as well) as well as an int (miles)

I found this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81360 to calculate the actual formula but I can't figure out to modify it in proc form to be able to go through an entire list of addresses and only give me the Id's of the addresses that are <= the miles (that I pass in), from the lat/long I pass in.

Can I get any help here?

Thanks!

score 6 · Accepted Answer · answered Apr 29 '11 at 00:32

6

SQL Server 2008

Using the spacial function STDistance return distance in meters

geography::Point(@lat1, @lon1, 4326).STDistance(geography::Point(@lat2, @lon2, 4326))

answered Apr 29 '11 at 00:32

tidwall

6,881
2
36
47

I like this way better it's a lot cleaner, and I can just convert meters to miles – slandau Apr 29 '11 at 00:48
4326 is the SRID for a WGS82 geographic point. Unless you plan on using a different coordinate system than standard GPS Lat/Lon, you will always want to use 4326. – tidwall Apr 29 '11 at 18:03
For a tremendous speed boost on your queries, pre-calculate and store the `geography::Point` as a spacial data type with an index on it. Then you can call `point1.STDistance(point2)` – tidwall Apr 29 '11 at 18:06
What if I created a calc'd column that got stored as the geography for the lat/long as i insert them, and then i only create the other point in the stored proc? – slandau Apr 29 '11 at 18:16
That would work well. The idea is to avoid having to unnecessarily generate geography points. – tidwall Apr 29 '11 at 18:19
Gotcha. And it stores as a float I believe? So i'd just create a float column, and have it calculate based on the lat/long I'm inserting with. I'd still have to generate another point on the proc, but cut in half – slandau Apr 29 '11 at 18:21
The `geography` type is different than a standard `float`. It's optimized for geographical formulas and is rather large in size (22 bytes). Compared to 2 floats (16 bytes). – tidwall Apr 29 '11 at 18:40

score 2 · Answer 2 · answered Apr 29 '11 at 00:07

I actually wrote a short blog post a while back for exactly this purpose. Basically, the query I have is:

SELECT
  Name,
  Address,
  City,
  State,
  Latitude,
  Longitude,
  (
    ACOS(
      COS(@center_latitude * (PI()/180)) *
      COS(@center_longitude * (PI()/180)) *
      COS(Latitude * (PI()/180)) *
      COS(Longitude * (PI()/180)) +
      COS(@center_latitude * (PI()/180)) *
      SIN(@center_longitude * (PI()/180)) *
      COS(Latitude * (PI()/180)) *
      SIN(Longitude * (PI()/180)) +
      SIN(@center_latitude * (PI()/180)) *
      SIN(Latitude * (PI()/180))
    ) *
    (
      (@equatorial_radius * @polar_radius) /
      (
        SQRT(
          (@equatorial_radius * @equatorial_radius) -
          (
            (
              (@equatorial_radius * @equatorial_radius) -
              (@polar_radius * @polar_radius)
            ) *
            (
              COS(@center_latitude) *
              COS(@center_latitude)
            )
          )
        )
      )
    )
  ) AS Miles
FROM
  Places
WHERE
  Miles <= @search_radius

Give it the center latitude, the center longitude, and the search radius and you should be good. (The parameters for the equatorial and polar radii of the Earth can be hard-coded, naturally.)

All that math is supposed to account for the curvature of the earth, the bulging at the equator, etc.

I'm getting an error when I run that in the WHERE clause. It says "Invalid Column Name - Miles" — slandau, Apr 29 '11 at 00:23
@slandau: Interesting. I would think it should work since the column with all the math is aliased as `Miles`. But upon further research, I guess T-SQL doesn't like that. (I probably used this in MySQL back in the day, it's been a while.) You can try putting it in a derived table. See: http://stackoverflow.com/questions/260399/using-an-alias-column-in-the-where-clause-in-ms-sql-2000 — David, Apr 29 '11 at 00:40
Is there a way I can use longitude/latitude from the table in the calculation but not return them as part of the result set? — slandau, Apr 29 '11 at 00:50
@slandau: Just removing them from the SELECT clause should do the trick. — David, Apr 29 '11 at 01:03

score 0 · Answer 3 · answered Apr 29 '11 at 00:10

0

you can just use the function directly in the SP... I was thinking:


CREATE PROCEDURE [FindPlaces](@lat float, @long float, @min_dist float) 
AS 

select messageId from yourtable
where dbo.F_GREAT_CIRCLE_DISTANCE(@lat, @long, lat, long)

answered Apr 29 '11 at 00:10

M.R.

4,737
3
37
81

Great Circle Distance Formula: T-SQL

3 Answers3

Linked