Share via

Create, Construct, and Query geography Instances

  • Article

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

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.

In This Topic

  • Creating or constructing a new geography instance

    • Creating a New geography Instance from an Existing Instance

    • Constructing a geography Instance from Well-Known Text Input

    • Constructing a geography Instance from Well-Known Binary Input

    • Constructing a geography Instance from GML Text Input

  • Returning Well-Known Text and Well-Known Binary from a geography Instance

  • Querying the Properties and Behaviors of geography Instances

    • Validity, Instance Type, and GeometryCollection Information

    • Number of Points

    • Dimension

    • Empty

    • Closure

    • Spatial Reference ID (SRID)

  • Determining Relationships between geography Instances

  • geography Instances Must Use Supported SRID

  • Examples

[TOP]

Creating or constructing a new geography instance

Creating a New geography Instance from an Existing Instance

The geography data type provides numerous built-in methods you can use to create new geography instances based on existing instances.

Constructing a geography Instance from Well-Known Text Input

The geography data type provides several built-in methods that generate a geography from the Open Geospatial Consortium (OGC) WKT representation. The WKT standard is a text string that allows geography data to be exchanged in textual form.

Constructing a geography Instance from Well-Known Binary Input

WKB is a binary format specified by the OGC that permits Geography data to be exchanged between a client application and an SQL database. The following functions accept WKB input to construct geography instances:

Constructing a geography Instance from GML Text Input

The geography data type provides a method that generates a geography instance from GML, an XML representation of a geography instance. SQL Server supports a subset of GML.

For more information on Geography Markup Language, see the OGC Specification: OGC Specifications, Geography Markup Language.

Returning Well-Known Text and Well-Known Binary from a geography Instance

You can use the following methods to return either the WKT or WKB format of a geography instance:

Querying the Properties and Behaviors of geography Instances

All geography instances have a number of properties that can be retrieved through methods that SQL Server provides. The following topics define the properties and behaviors of geography types, and the methods for querying each one.

Validity, Instance Type, and GeometryCollection Information

After a geography instance is constructed, you can use the following methods to return the instance type, or if it is a GeometryCollection instance, return a specific geography instance.

Number of Points

All nonempty geography instances are comprised of points. These points represent the latitude and longitude coordinates of the earth on which the geography instances are drawn. The data type geography provides numerous built-in methods for querying the points of an instance.

Dimension

A nonempty geography instance can be 0-, 1-, or 2-dimensional. Zero-dimensional geography instances, such as Point and MultiPoint, have no length or area. One-dimensional objects, such as LineString, CircularString, CompoundCurve, and MultiLineString, have length. Two-dimensional instances, such as Polygon, CurvePolygon, and MultiPolygon, have area and length. Empty instances report a dimension of -1, and a GeometryCollection reports the maximum dimension of its contents.

Empty

An empty geography instance does not have any points. The length of empty LineString, CircularString, CompoundCurve, and MultiLineString instances is 0. The area of empty Polygon, CurvePolygon and MultiPolygon instances is 0.

Closure

A closed geography instance is a figure whose start points and end points are the same. Polygon instances are considered closed. Point instances are not closed.

A ring is a simple, closed LineString instance.

Spatial Reference ID (SRID)

The spatial reference ID (SRID) is an identifier specifying which ellipsoidal coordinate system the geography instance is represented in. Two geography instances with different SRIDs cannot be compared.

This property can be modified.

Determining Relationships between geography Instances

The geography data type provides many built-in methods you can use to determine relationships between two geography instances.

geography Instances Must Use Supported SRID

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).

SQL Server uses the default SRID of 4326, which maps to the WGS 84 spatial reference system, when using methods on geography instances. If you use data from a spatial reference system other than WGS 84 (or SRID 4326), you will need to determine the specific SRID for your geography spatial data.

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(-122.360 47.656, -122.343 47.656)', 4326));

INSERT INTO SpatialTable (GeogCol1)
VALUES (geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 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();

See Also

Concepts

Spatial Data (SQL Server)