New Data Types in SQL Server 2008 Part 3

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.

Polygon
Polygon is the two-dimensional surface which contains multiple points connected to each other. Polygon is a closed shape.

DECLARE @g geometry;
SET @g = geometry::Parse('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))')

As Polygon is a closed shape, you can see from the example above that the start and end points are the same. As a Polygon is a closed object, you can use all the function you used with LineString shapes. There are a few additional functions which you can also use for Polygon shape.

Method	Usage	Syntax	Result for Above Sample
STArea()	Area of the Polygon	Select @g.STArea()	25
STCentroid ()	Centre point of the Polygon	Select @g. STCentroid().ToString();	POINT (2.5,2.5)

SELECT @g.STArea();
SELECT @g.STCentroid().ToString();

MultiPoint MultiLineString MultiPolygon
Multipoint is collection of zero or more points.

DECLARE @g geometry;
SET @g = geometry::STMPointFromText('MULTIPOINT((10 20), (3 4 5))', 0);

If you want to extract only one point you can use the following method.

SELECT @g.STGeometryN(1).ToString();

MultiLineString and MultiPolygon are also as same as MultiPoint. The following examples give you an understanding of these functions.

SET @g = geometry::Parse('MULTILINESTRING((0 0, 2 1), (3 1 0, 1 1))');
SELECT @g.STGeometryN(2).ToString();
SET @g = geometry::Parse('MULTIPOLYGON(((0 0, 0 3, 3 3, 3 0, 0 0)),((9 9, 9 10, 10 9, 9 9)))');
SELECT @g.STGeometryN(1).ToString();

For overlapping polygons you may need to execute an additional method named MakeValid(). Let us illustrate this with three polygons.

((0 0, 0 3, 3 3, 3 0, 0 0)), ((1 1, 1 2, 2 1, 1 1)), ((9 9, 9 10, 10 9, 9 9)))

You can see that the second polygon falls inside the first polygon and this does not follow the OGC standards. If you run the following script an error will be returned.

DECLARE @g geometry;
SET @g = geometry::Parse('MULTIPOLYGON(((0 0, 0 3, 3 3, 3 0, 0 0)), ((1 1, 1 2, 2 1, 1 1)), ((9 9, 9 10, 10 9, 9 9)))');
SELECT @g.STGeometryN(2).STAsText();

The Error is;

System.ArgumentException: 24144: This operation cannot be completed because the instance is not valid. Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a geometry instance to shift slightly.

A an error has been specified, we need to use the MakeValid function. The MakeValid function will shift the first two polygons into one polygon.

DECLARE @g geometry;
SET @g = geometry::Parse('MULTIPOLYGON(((0 0, 0 3, 3 3, 3 0, 0 0)), ((1 1, 1 2, 2 1, 1 1)), ((9 9, 9 10, 10 9, 9 9)))');
If @g.STIsValid() = 0
SET @g = @g.MakeValid();
SELECT @g.STGeometryN(2).STAsText();

The STisValid() method will verify whether the polygon follows the the OGC standards. If not MakeValid() method will convert the above polygon into a valid polygon.


Spatial Reference Identifies (SRID)
Each spatial instance has a spatial reference identifier (SRID). The SRID corresponds to a spatial reference system based on the specific ellipsoid used for either flat-earth mapping or round-earth mapping. A spatial column can contain objects with different SRIDs. However, only spatial instances with the same SRID can be used when performing operations with SQL Server spatial data methods on your data. The result of any spatial method derived from two spatial data instances is valid only if those instances have the same SRID that is based on the same unit of measurement, datum, and projection used to determine the coordinates of the instances. The most common units of measurement of a SRID are meters or square meters.

If two spatial instances do not have the same SRID, the results from a geometry or geography Data Type method used on the instances will return NULL.

SQL Server supports SRIDs based on the EPSG standards. A SQL Server-supported SRID for geography instances must be used when performing calculations or using methods with geography spatial data. The SRID must match one of the SRIDs displayed in the sys.spatial_reference_systems catalog view. As mentioned previously, when you perform calculations on your spatial data using the geography data type, your results will depend on which ellipsoid was used in the creation of your data, as each ellipsoid is assigned a specific spatial reference identifier (SRID).

Updating SQL Server Tables
Now that we understand the geometry types and their functions, it is time to insert data into SQL Server tables.

First let us create a simple table which can hold data for shapes.

CREATE TABLE GeometryData
(ID INT IDENTITY(1,1),
GeometryShape Geometry)

The Geometry data table consists of two columns, one is an incremental column for a key and the second column is the column where we are going to store the shapes.

Let us insert three records with Point, LineString and Polygon geometry.

INSERT INTO GeometryData (GeometryShape)
VALUES (geometry::STGeomFromText('POINT (100 100)', 0));

INSERT INTO GeometryData (GeometryShape)
VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));

INSERT INTO GeometryData (GeometryShape)
VALUES (geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0));

To retrieve these values you can use SELECT with ToString() method.

SELECT GeometryShape.ToString() FROM GeometryData

If you want to find the intersect between two shapes, you can use STIntersection method.

DECLARE @g1 geometry;
DECLARE @g2 geometry;
DECLARE @result geometry;

SELECT @g1 = GeometryShape FROM GeometryData WHERE id = 2;
SELECT @g2 = GeometryShape FROM GeometryData WHERE id = 3;
SELECT @result = @g1.STIntersection(@g2);
SELECT @result.STAsText();

Geography Data
The geography data type (geodetic) stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates. Geography data type is somewhat similar to geometry. The key difference is that with the geography data types you need to provide latitudes and longitudes.

DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('POLYGON((47.653 -122.358, 47.649 -122.348, 47.658 -122.348, 47.658 -122.358, 47.653 -122.358))',4326);
SET @h = geography::STGeomFromText('LINESTRING(47.656 -122.360, 47.656 -122.343)',4326);
SELECT @g.STIntersection(@h).ToString();

This examples show how to find the distance between two points:

DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('POINT(47.653 -122.358)',4326);
SET @h = geography::STGeomFromText('POINT(47.656 -122.360)',4326);
SELECT @g.STDistance(@h);

As geography data is almost similar to geometry, you can use all the example shown in the geometry section.

Point to Remember
All information about SQL Server 2008 presented in this article is based on SQL Server 2008 build 10.0.1075 (November CTP). All information is subject to change in future CTP releases and the final SQL Server 2008 release.