Article Topics

All Articles

Performance Tuning

Audit

Business Intelligence

Clustering

Reporting Services

Developer

General DBA

ASP.NET / ADO.NET

Write for Us

Share you SQL Server knowledge with others and raise your profile in the community More...

New Data Types in SQL Server 2008 Part 3

By : Dinesh Asanka
Feb 01, 2008

Page 3 / 3

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.

<< Prev Page