SQL Server 2008 - Querying Spatial Data
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!
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
.
精彩评论