geography (Transact-SQL)

The geography spatial data type, geography, is implemented as a .NET common language runtime (CLR) data type in SQL Server. This type represents data in a round-earth coordinate system. The SQL Server geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.

SQL Server 2008 supports a set of methods for the geography spatial data type. These methods include methods on geography that are defined by the Open Geospatial Consortium (OGC) standard and a set of Microsoft extensions to that standard.

For more information on geography spatial Data Type methods, see the geography Data Type Method Reference.

Registering the geography Type

The geography type is predefined and available in each database. You can create table columns of type geography and operate on geography data in the same manner as you would use other system-supplied types.

Examples

The following examples show how to add and query geography data. The first example creates a table with an identity column and a geography column, GeogCol1. A third column renders the geography column into its Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation, and uses the STAsText() method. Two rows are then inserted: one row contains a LineString instance of geography, and one row contains a Polygon instance.

IF OBJECT_ID ( 'dbo.SpatialTable', 'U' ) IS NOT NULL 
    DROP TABLE dbo.SpatialTable;
GO

CREATE TABLE SpatialTable 
    ( id int IDENTITY (1,1),
    GeogCol1 geography, 
    GeogCol2 AS GeogCol1.STAsText() );
GO

INSERT INTO SpatialTable (GeogCol1)
VALUES (geography::STGeomFromText('LINESTRING(47.656 -122.360, 47.656 -122.343)', 4326));

INSERT INTO SpatialTable (GeogCol1)
VALUES (geography::STGeomFromText('POLYGON((47.653 -122.358, 47.649 -122.348, 47.658 -122.348, 47.658 -122.358, 47.653 -122.358))', 4326));
GO

The second example uses the STIntersection() method to return the points where the two previously inserted geography instances intersect.

DECLARE @geog1 geography;
DECLARE @geog2 geography;
DECLARE @result geography;

SELECT @geog1 = GeogCol1 FROM SpatialTable WHERE id = 1;
SELECT @geog2 = GeogCol1 FROM SpatialTable WHERE id = 2;
SELECT @result = @geog1.STIntersection(@geog2);
SELECT @result.STAsText();