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);