Export (0) Print
Expand All

Create, Construct, and Query geography Instances

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

Parse (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 an 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)

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)

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

To determine if an instance is empty

STIsEmpty (geography Data Type)

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.

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.

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)

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.

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();
    
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft