Introduction
This is the third article of the new data types in SQL Server 2008 series. We have already discussed the new date time data types in the first article and HierarchyID in second article. In this article, we are going to discuss spatial data types.
Spatial data represents information about the physical location and shape of geometric objects. These objects can be point locations or more complex objects such as countries, roads, or lakes.
SQL Server supports two spatial data types: the Geometry data type and the Geography data type. Both data types are implemented as .NET Common Language Runtime (CLR) data types in SQL Server.
Geometry Data
The Geometry data type (planar) supported by SQL Server conforms to the Open Geospatial Consortium (OGC) Simple Features for SQL Specification version 1.1.0.
First of all lets look at the available shapes in SQL Server 2008 for geometry. The following image illustrates the shapes that are available.
Source : Books On line, SQL Server 2008
Point
In SQL Server spatial data, a Point is a 0-dimensional object representing a single location and may contain Z (elevation) and M (measure) values.
The following example defines a point with coordinates of (2, 5):
DECLARE @g geometry;
SET @g = geometry::STGeomFromText(‘POINT (2 5)’, 0);
In the point, only X and Y coordinates are defined. The STGeomFromText function converts geometry tagged coordinates to the Geometry data type. The second parameter which is 0 in the above example is the Spatial Reference Identifier (SRID). Details of SRID will be discussed later in this article.
The following example will define a point with coordinates of (2, 5, 9) and with a measure of 8. You may notice that we have used new function named Parse. Parse is same as STGeomFromText but in the Parse function SRID is 0.
DECLARE @g geometry;
SET @g = geometry::Parse(‘POINT(2 5 9 8)’);
In a Point geometry you have four functions. They are STX, STY, Z and M which will return X, Y, Z coordinates and measure.
DECLARE @g geometry;
SET @g = geometry::Parse(‘POINT(2 5 9 8)’);
SELECT @g.STX; — Return 2
SELECT @g.STY; — Return 5
SELECT @g.Z; — Return 9
SELECT @g.M; — Return 8
LineString
A LineString is a one-dimensional object representing a sequence of points and the line segments connecting them. A LineString instance must be formed with at least two distinct points, and can also be empty.
DECLARE @g geometry;
SET @g = geometry::STGeomFromText(‘LINESTRING(1 1, 4 5, 10 13,19 25)’, 0);
The example above will create a shape which has joined points (1, 1), (4, 5), (10, 13) and (19, 25). Even though I have specified only X and Y coordinates, you have the option of providing Z and M parameters as well.
There are several valuable methods which you can use with the LineString shape.
|
Method |
Usage |
Syntax |
Result for Above Sample |
|
STLength() |
Total length |
Select @g.STLength() |
30 |
|
STStartPoint() |
Start point of the LineString |
Select @g.STStartPoint().ToString(); |
POINT (1, 1) |
|
STEndPoint() |
End point of the LineString |
Select @g.STEndPoint().ToString(); |
POINT( 19, 25) |
|
STPointN() |
Point of the given number of the LineString object. |
Select @g.STPointN(2).ToString(); |
POINT (4, 5) |
|
STNumPoints() |
Number of points |
Select @g.STNumPoints(); |
4 |
|
STIsSimple() |
If shape does not intersect itself then it is 1 |
Select @g.STIsSimple(); |
1 |
|
STIsClosed() |
If shape is closed then it is 1 |
Select @g.STIsClosed(); |
0 |
|
STIsRing() |
If it simple and closed |
Select @g.STIsRing(); |
0 |
Select @g.STLength()
Select @g.STStartPoint().ToString();
Select @g.STEndPoint().ToString();
Select @g.STPointN(2).ToString();
Select @g.STNumPoints();
Select @g.STIsSimple();
Select @g.STIsClosed()
Select @g.STIsRing();
In my testing I have found that there are some errors when the LineString shape is closed. These issues should be fixed in a coming release.