Create, Construct, and Query geography Instances
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
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.
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.
To create a buffer around a geography
STBuffer (geography Data Type)
To create a buffer around a geography, allowing for a tolerance
BufferWithTolerance (geography Data Type)
To create a geography from the intersection of two geography instances
STIntersection (geography Data Type)
To create a geography from the union of two geography instances
STUnion (geography Data Type)
To create a geography from the points where one geography does not overlap another
STDifference (geography Data Type)
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.
To construct any type of geography instance from WKT input
STGeomFromText (geography Data Type)
To construct a geography Point instance from WKT input
STPointFromText (geography Data Type)
To construct a geography MultiPoint instance from WKT input
STMPointFromText (geography Data Type)
To construct a geography LineString instance from WKT input
STLineFromText (geography Data Type)
To construct a geography MultiLineString instance from WKT input
STMLineFromText (geography Data Type)
To construct a geography Polygon instance from WKT input
STPolyFromText (geography Data Type)
To construct a geography MultiPolygon instance from WKT input
STMPolyFromText (geography Data Type)
To construct a geography GeometryCollection instance from WKT input
STGeomCollFromText (geography Data Type)
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 a SQL database. The following functions accept WKB input to construct geography instances:
To construct any type of geography instance from WKB input
STGeomFromWKB (geography Data Type)
To construct a geography Point instance from WKB input
STPointFromWKB (geography Data Type)
To construct a geography MultiPoint instance from WKB input
STMPointFromWKB (geography Data Type)
To construct a geography LineString instance from WKB input
STLineFromWKB (geography Data Type)
To construct a geography MultiLineString instance from WKB input
STMLineFromWKB (geography Data Type)
To construct a geography Polygon instance from WKB input
STPolyFromWKB (geography Data Type)
To construct a geography MultiPolygon instance from WKB input
STMPolyFromWKB (geography Data Type)
To construct a geography GeometryCollection instance from WKB input
STGeomCollFromWKB (geography Data Type) STGeomCollFromWKB (geography Data Type)
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.
To construct any type of geography instance from GML input
GeomFromGML (geography Data Type)
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:
To return the WKT representation of a geography instance
STAsText (geography Data Type)
ToString (geography Data Type)
To return the WKT representation of a geography instance including any Z and M values
AsTextZM (geography Data Type)
To return the WKB representation of a geography instance
STAsBinary (geography Data Type)
To return a GML representation of a geography instance
AsGml (geography Data Type)
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.
To return the instance type of a geography
STGeometryType (geography Data Type)
To determine if a geography is a given instance type
InstanceOf (geography Data Type)
To determine if a geography instance is well-formed for its instance type
STNumGeometries (geography Data Type)
To return a specific geography in a GeometryCollection instance
STGeometryN (geography Data Type) STGeometryN (geography Data Type)
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.
To return the number of points that comprise an instance
STNumPoints (geography Data Type)
To return a specific point in an instance
STPointN (geometry Data Type)
To return the start point of an instance
STStartPoint (geography Data Type)
To return the end point of an instance
STEndpoint (geography Data Type)
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.
To return the dimension of an instance
STDimension (geography Data Type)
To return the length of an instance
STLength (geography Data Type)
To return the area of an instance
STArea (geography Data Type)
Empty
An emptygeography 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.
To determine if an instance is empty
STIsEmpty (geography Data Type)
Closure
A closedgeography 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.
To determine if an instance is closed
STIsClosed (geography Data Type)
To return the number of rings in a Polygon instance
NumRings (geography Data Type)
To return a specified ring of a geography instance
RingN (geography Data Type)
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.
To set or return the SRID of an instance
STSrid (geography Data Type)
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.
To determine if two instances comprise the same point set
STEquals (geometry Data Type)
To determine if two instances are disjoint
STDisjoint (geometry Data Type)
To determine if two instances intersect
STIntersects (geometry Data Type)
To determine the point or points where two instances intersect
STIntersection (geography Data Type)
To determine the shortest distance between points in two geography instances
STDistance (geometry Data Type)
To determine the difference in points between two geography instances
STDifference (geography Data Type)
To derive the symmetric difference, or unique points, of one geography instance compared with another instance
STSymDifference (geography Data Type)
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.
Example A.
This 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
Example B.
This 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();