I’ve been a little reticent in my posts on SQL Server 2008 lately. It certainly
didn’t come down to being too busy, as I’ve had far too much time on my hands in the
last month. But, I suppose writing these technical posts always came from looking
for something to do while procrastinating on another project. Which is odd,
because I never found writing all that easy…
Getting back to the topic at hand, lets talk space. I did a lot of talking on
spatial data (literally) in presentations and sales talks, but didn’t really write
too much on it. So, much like my talks, lets do some basic research.
Spatial data in SQL Server is handled by a Complex Data Type. It references
a point in space, by way of coordinates (x, y, z). Usually they are handled
in degrees, referring to latitude and longitude. However this not always the
case. You can use your own scale or units if you are mapping flat surfaces,
which I will explain in a moment.
So what are the data types?
You have two to choose from:
-
Geography data type
-
Geometry data type
With the Geography data type you store geodetic values, which reference the Lat/Lon
values. You would use this on a large scale, where the curvature of the earth
has an effect (or any spherical surface for that matter).
The geometric data type can be used with any units, as it represents simple coordinates
(x, y, z) on a flat surface. You can use this on any scale, as long as you don’t
have to take into account spherical curvature.
Now, just referencing a single point can have its uses, but a lot of times you want
to store more than a single point. Like if were storing a selection of area.
This is done by using a collection of points that form a polygon. You can also
store a collection of points that create a line. This could be useful if you
were storing routes.
Using the data types
A point in space:
-- Creates a point instance representing the point (3, 4) with an SRID (Spatial Reference
System Identifier) 0.
DECLARE @g GEOMETRY;
SET @g = GEOMETRY::STGeomFromText('POINT (3 4)', 0)
SELECT @g.STX
SELECT @g.STY
SELECT @g.Z
SELECT @g.M
GO
Create a polygon (known as a multipoint):
-- Create a polygon
DECLARE @g GEOMETRY;
SET @g = GEOMETRY::STPolyFromText('POLYGON((0 0, 0 3, 3 3, 3 0, 0 0), (1 1, 1 2, 2
1, 1 1))', 10)
SELECT
@g.STStartPoint().STX AS 'Point 1 - X',
@g.STStartPoint().STY AS 'Point 1 - Y',
@g.STPointN(7).STX AS 'Point 7 - X',
@g.STPointN(7).STY AS 'Point 7 - Y'
GO
Create a line:
-- Creates a line with 3 points (1/1, 2/4, 3/9) and a SRID of 0
DECLARE @g GEOMETRY;
SET @g = GEOMETRY::STGeomFromText('LINESTRING(1 1, 2 4, 3 9)', 0)
SELECT
@g.STStartPoint().STX AS 'Point 1 - X',
@g.STStartPoint().STY AS 'Point 1 - Y',
@g.STPointN(2).STX AS 'Point 2 - X',
@g.STPointN(2).STY AS 'Point 2 - Y',
@g.STEndPoint().STX AS 'Point 3 - X',
@g.STEndPoint().STY AS 'Point 3 - Y'
GO
Now some function fun
Figure out where the instances intersect:
-- Return the points where the two geometry instances intersect each other
DECLARE @geom1 GEOMETRY
DECLARE @geom2 GEOMETRY
DECLARE @result GEOMETRY
SELECT @geom1 = GeomColumn1 FROM SpatialTable WHERE ID = 1
SELECT @geom2 = GeomColumn1 FROM SpatialTable WHERE ID = 2
SELECT @result = @geom1.STIntersection(@geom2)
SELECT @result.STAsText()
GO
Make some calculations (using your own units if you wish):
-- Doing some calculations
DECLARE @g GEOMETRY;
SET @g = GEOMETRY::STPolyFromText('POLYGON((0 0, 0 3, 3 3, 3 0, 0 0))', 0)
SELECT
@g.STArea() AS 'Square meters',
@g.STLength() AS 'Length',
@g.STDimension() AS 'Dimensions'
GO
Conclusion
This is by no means an in depth article on Spatial data. I always thought spatial
data was fairly straightforward, so explaining what it was in depth was a little redundant.
However, it doesn’t always translate properly to using 