SQL Server 2008 - spatial data query

Question

I have a SQL Server database ported to SQL Server 2008. I want to use spatial functions. However, my data uses more traditional data types. For example, I have the following two tables:

Location
--------
ID char(36)
Address nvarchar (256)
City nvarchar (256)
State char (2)
PostalCode char (10)

Order
-----
LocationID char(36)
Product nvarchar(30)
Quantity int
TotalPrice decimal

How can I use the spatial features of SQL Server 2008 to receive orders within a 10 mile radius of a particular postal code?

Thank!

Answer 1

You need to keep the latitude and longitude in the table Location, and you will also need the latitude and longitude of your zip codes.

SQL Server . .

, SQL Server 2008:

DECLARE @some_point geography;
SET @some_point = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);

SELECT
    Order.Product
FROM
    Order
INNER JOIN
    Location ON (Location.ID = Order.LocationID)
WHERE
    Location.Position.STDistance(@some_point) < 16093;

, Location Position.