Great Circle Distance Formula: T-SQL

问题:

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!

回答1:

SQL Server 2008

Using the spacial function STDistance return distance in meters

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

回答2:

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.

回答3:

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)