Does this mySQL "spatial" query work in SQL Server 2008 as well?

Question

Before I embark on a a pretty decent overhaul of my web app to use a spatial query, I'd like to know if this MySQL query works in SQL Server 2008:

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * 
        cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * 
        sin( radians( lat ) ) ) ) AS distance 
FROM markers HAVING distance < 25 
ORDER BY distance LIMIT 0 , 20;

Or is there a better way to do this in SQL Server 2008?

My database currently stores that lat/long of businesses near military bases in Japan. However, I'm querying the table to find businesses that contain the specified bases' id.

Biz table
----------------------
PK BizId bigint (auto increment)
Name
Address
Lat
Long
**FK BaseId int (from the MilBase table)**

A spatial query, based on having a center lat/long and given radius (in km) would be a better fit for the app and would open up some new possibilities.

Any help is greatly appreciated!

Answer 1

It looks like you're selecting the distance between two points. In SQL Server 2008, you can use the STDistance method of the geography data type. This will look something like this:

SELECT   TOP 20
         geography::STGeomFromText('POINT(-122.0 37.0)', 4326).STDistance(p) 
FROM     markers
WHERE    geography::STGeomFromText('POINT(-122.0 37.0)', 4326).STDistance(p) < 25
ORDER BY geography::STGeomFromText('POINT(-122.0 37.0)', 4326).STDistance(p);

Where p would be a field of type geography instead of two separate decimal fields. You may probably also want to create a spatial index on your p field for better performance.

To use the geography data type, simply specify your field as geography in your CREATE TABLE:

CREATE TABLE markers ( 
    id     int IDENTITY (1,1),
    p      geography, 
    title  varchar(100) 
);

Inserting values into your markers table will now look like this:

INSERT INTO markers (id, p, title) 
VALUES (
    1,
    geography::STGeomFromText('POINT(-122.0 37.0)', 4326),
    'My Marker'
);    

Where -122.0 is the longitude, and 37.0 is the latitude.

Creating a spatial index would look something like this:

CREATE SPATIAL INDEX  ix_sp_markers
                      ON markers(p)
                      USING GEOGRAPHY_GRID
                      WITH ( GRIDS = (HIGH, HIGH, HIGH, HIGH),
                             CELLS_PER_OBJECT = 2,
                             PAD_INDEX = ON);

Answer 2

If you are only interested in retrieving points within 25 miles, then there is absolutely no need to use spherical or great circle math in the distance calculations... More than sufficient would be to just use the standard cartesian distance formula...

Where  Square(Delta-X) + Square(Delta-Y) < 225  

All you need to do is convert the difference in Latitudes and the difference in longitudes to mileages in whatever units you are using (statue miles naultical miles, whatever)

If u r using nautical miles each degree of latitude = 60 nm...
And each degree of Longitude is equal to 60 * cos(Latitude) nm
Here if both points are within 25 miles of one another, you don;t even need to worry about the difference between this factor from one point to the other...