Problem
The company I work for has migrated to SQL Server 2008 and we’re ready to start planning for the use of geospatial data. One of the applications we want to use geospatial data in will assist order fulfillers in efficiently picking stock for orders. Our warehouse is fairly large, which geospatial data type should we use?
Solution
In a previous tip, we gave a high-level overview of the new geospatial data types in SQL Server 2008. Now it is time to talk about data storage for geospatial data and when to use which type.
It’s not uncommon for geospatial datasets to grow to millions of records, particularly when housing data for points over a large area (like restaurants in the United States). In both the geometry and geography data types the field is equivalent to VARBINARY(MAX). Remember that the geometry data type is best used with a Euclidian or a flat coordinate system to house point, line, linestring and other object dimensions.
We will use the AdventureWorks database and assign product locations housed in the Production.Product table. Although the warehouse is very large in size the Earth’s curvature has nil effect on the distances between products, so the geometry data type should be adequate. If we were to use the geography data type we would have to select a Spatial Reference Identifier (SRID)ProductLocation to house the point data.
CREATE TABLE Production.ProductLocation
(
ProductID INT
CONSTRAINT PK_ProductLocation_ProductID PRIMARY KEY
CONSTRAINT FK_ProductLocation_ProductID_Product_ProductID
FOREIGN KEY REFERENCES Production.Product(ProductID)
, ProductLocation GEOMETRY NOT NULL
, CreatedDt DATETIME NOT NULL
CONSTRAINT dft_ProductLocation_CreatedDtNow DEFAULT(GETDATE())
, CreatedBy VARCHAR(50) NOT NULL
, ModifiedDt DATETIME
, ModifiedBy VARCHAR(50)
) The table’s primary key is the ProductID, since there can only be one location for each product (in another scenario a product could have both a primary location and a location where surplus product could be housed). In addition, there is a foreign key relationship between the ProductID in ProductLocation and ProductID in the Product table. The cascading action on the foreign key is DELETE, since there would be no need to keep location information on a product that is no longer stocked.
For the example we will populate the ProductLocation table with locations for some of the flat washers and bike frames:
INSERT INTO Production.ProductLocation(ProductID,ProductLocation,CreatedBy,ModifiedBy)
VALUES(342,geometry::STPointFromText('POINT(0 10)',0),'timcullen','timcullen')
INSERT INTO Production.ProductLocation(ProductID,ProductLocation,CreatedBy,ModifiedBy)
VALUES(343,geometry::STPointFromText('POINT(0 12)',0),'timcullen','timcullen')
INSERT INTO Production.ProductLocation(ProductID,ProductLocation,CreatedBy,ModifiedBy)
VALUES(344,geometry::STPointFromText('POINT(0 14)',0),'timcullen','timcullen')
INSERT INTO Production.ProductLocation(ProductID,ProductLocation,CreatedBy,ModifiedBy)
VALUES(345,geometry::STPointFromText('POINT(0 16)',0),'timcullen','timcullen')
INSERT INTO Production.ProductLocation(ProductID,ProductLocation,CreatedBy,ModifiedBy)
VALUES(346,geometry::STPointFromText('POINT(0 18)',0),'timcullen','timcullen')
INSERT INTO Production.ProductLocation(ProductID,ProductLocation,CreatedBy,ModifiedBy)
VALUES(347,geometry::STPointFromText('POINT(0 20)',0),'timcullen','timcullen')
INSERT INTO Production.ProductLocation(ProductID,ProductLocation,CreatedBy,ModifiedBy)
VALUES(348,geometry::STPointFromText('POINT(0 22)',0),'timcullen','timcullen')
INSERT INTO Production.ProductLocation(ProductID,ProductLocation,CreatedBy,ModifiedBy)
VALUES(349,geometry::STPointFromText('POINT(0 24)',0),'timcullen','timcullen')
INSERT INTO Production.ProductLocation(ProductID,ProductLocation,CreatedBy,ModifiedBy)
VALUES(748,geometry::STPointFromText('POINT(29 31)',0),'timcullen','timcullen')
INSERT INTO Production.ProductLocation(ProductID,ProductLocation,CreatedBy,ModifiedBy)
VALUES(739,geometry::STPointFromText('POINT(29 41)',0),'timcullen','timcullen')
INSERT INTO Production.ProductLocation(ProductID,ProductLocation,CreatedBy,ModifiedBy)
VALUES(740,geometry::STPointFromText('POINT(29 51)',0),'timcullen','timcullen')
INSERT INTO Production.ProductLocation(ProductID,ProductLocation,CreatedBy,ModifiedBy)
VALUES(742,geometry::STPointFromText('POINT(29 61)',0),'timcullen','timcullen')
INSERT INTO Production.ProductLocation(ProductID,ProductLocation,CreatedBy,ModifiedBy)
VALUES(741,geometry::STPointFromText('POINT(29 71)',0),'timcullen','timcullen') Now that we have populated the ProductLocation table we can evaluate the structure of the table. When we perform a simple select statement against the table we see that the product location is stored as a binary value, known as the Well-Known Binary Representation:
The binary representation is created by using a .NET Common Language Runtime data type. One other thing to note is that when a query is performed on a table with a geospatial data type an additional tab appears in the results area called Spatial Results. The data returned is represented on a grid:
Since the geometry data in this table are point values, we can use the STX and STY functions to extract each coordinate. One interesting note about using these functions is that since they use the .NET CLR they are case-sensitive. If I execute the query with one of the characters in lower-case I receive the following message:
When I change the case of the character I receive the intended results:
One other thing to note is that if the geometry column, in our case ProductLocation, is not queried the Spatial Results tab will not appear.
Next Steps
- Review information about Designing and Implementing Spatial Storage on the Microsoft Developer Network
- Read the overview on Working with Spatial Data
- Read more information about the Simple feature access – Part 2: SQL option from the Open Geospatial Consortium
