SQL Server 2008 - Querying Spatial Data

Question

Hello,

I have a SQL Server database that I have migrated to SQL Server 2008. I want to take advantage of the spatial features. However my data is using more traditional data types. For instance, 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 get the orders within a 10 mile radius of a particular postal code?

Thank you!

Answer 1

You need to store the latitude and longitude in your Location table, and you will also need to have the latitude and longitude of your postcodes.

SQL Server can only work with coordinates. You may want to use a reverse geocoding service to get the coordinates of your locations.

In any case, this is how your geospatial query could look like in 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;

The above assumes that your Location table would have a field of type geography called Position.