Create, Construct, and Query geometry Instances

  • Article

The planar spatial data type, geometry, represents data in a Euclidean (flat) coordinate system. This type is implemented as a common language runtime (CLR) data type in SQL Server.

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

The geometry data type (planar) supported by SQL Server conforms to the Open Geospatial Consortium (OGC) Simple Features for SQL Specification version 1.1.0.

For more information on OGC specifications, see the following:

SQL Server supports a subset of the existing GML 3.1 standard which is defined in the following schema: https://schemas.microsoft.com/sqlserver/profiles/gml/SpatialGML.xsd.

In This Topic

  • Creating or constructing a new geometry instance

    • Creating a New geometry Instance from an Existing Instance

    • Constructing a geometry Instance from Well-Known Text Input

    • Constructing a geometry Instance from Well-Known Binary Input

    • Constructing a geometry Instance from GML Text Input

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

  • Querying the Properties and Behaviors of geometry Instances

    • Validity, Instance Type, and GeometryCollection Information

    • Number of Points

    • Dimension

    • Empty

    • Boundary, Interior, and Exterior

    • Envelope

    • Closure

    • Spatial Reference ID (SRID)

  • Determining Relationships Between geometry Instances

  • geometry Instances Default to Zero SRID

  • Examples

Creating or constructing a new geometry instance

Creating a New geometry Instance from an Existing Instance

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

[TOP]

Constructing a geometry Instance from Well-Known Text Input

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

[TOP]

Constructing a geometry Instance from Well-Known Binary Input

WKB is a binary format specified by the Open Geospatial Consortium (OGC) that permits geometry data to be exchanged between a client application and an SQL database. The following functions accept WKB input to construct geometries:

[TOP]

Constructing a geometry Instance from GML Text Input

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

[TOP]

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

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

[TOP]

Querying the Properties and Behaviors of geometry Instances

All geometry 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 geometry types, and the methods for querying each one.

Validity, Instance Type, and GeometryCollection Information

Once a geometry instance is constructed, you can use the following methods to determine if it is well-formed, return the instance type, or, if it is a collection instance, return a specific geometry instance.

[TOP]

Number of Points

All nonempty geometry instances are comprised of points. These points represent the X- and Y-coordinates of the plane on which the geometries are drawn. geometry provides numerous built-in methods for querying the points of an instance.

[TOP]

Dimension

A nonempty geometry instance can be 0-, 1-, or 2-dimensional. Zero-dimensional geometries, 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 will report a dimension of -1, and a GeometryCollection will report an area dependent on the types of its contents.

  • To return the dimension of an instance
    STDimension

  • To return the length of an instance
    STLength

  • To return the area of an instance
    STArea

[TOP]

Empty

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

  • To determine if an instance is empty
    STIsEmpty.

[TOP]

Simple

For a geometry of the instance to be simple, it must meet both of these requirements:

  • Each figure of the instance must not intersect itself, except at its endpoints.

  • No two figures of the instance can intersect each other at a point that is not in both of their boundaries.

Note

Empty geometries are always simple.

  • To determine if an instance is simple
    STIsSimple.

[TOP]

Boundary, Interior, and Exterior

The interior of a geometry instance is the space occupied by the instance, and the exterior is the space not occupied it.

Boundary is defined by the OGC as follows:

  • Point and MultiPoint instances do not have a boundary.

  • LineString and MultiLineString boundaries are formed by the start points and end points, removing those that occur an even number of times.

DECLARE @g geometry;
SET @g = geometry::Parse('MULTILINESTRING((0 1, 0 0, 1 0, 0 1), (1 1, 1 0))');
SELECT @g.STBoundary().ToString();

The boundary of a Polygon or MultiPolygon instance is the set of its rings.

DECLARE @g geometry;
SET @g = geometry::Parse('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1))');
SELECT @g.STBoundary().ToString();
  • To return the boundary of an instance
    STBoundary

[TOP]

Envelope

The envelope of a geometry instance, also known as the bounding box, is the axis-aligned rectangle formed by the minimum and maximum (X,Y) coordinates of the instance.

  • To return the envelope of an instance
    STEnvelope

[TOP]

Closure

A closed geometry 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.

[TOP]

Spatial Reference ID (SRID)

The spatial reference ID (SRID) is an identifier specifying which coordinate system the geometry instance is represented in. Two instances with different SRIDs are incomparable.

  • To set or return the SRID of an instance
    STSrid

This property can be modified.

[TOP]

Determining Relationships between geometry Instances

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

  • To determine if two instances comprise the same point set
    STEquals

  • To determine if two instances are disjoint
    STDisjoint

  • To determine if two instances intersect
    STIntersects

  • To determine if two instances touch
    STTouches

  • To determine if two instances overlap
    STOverlaps

  • To determine if two instances cross
    STCrosses

  • To determine if one instance is within another
    STWithin

  • To determine if one instance contains another
    STContains

  • To determine if one instance overlaps another
    STOverlaps

  • To determine if two instances are spatially related
    STRelate

  • To determine the shortest distance between points in two geometries
    STDistance

[TOP]

geometry Instances Default to Zero SRID

The default SRID for geometry instances in SQL Server is 0. With geometry spatial data, the specific SRID of the spatial instance is not required to perform calculations; thus, instances can reside in undefined planar space. To indicate undefined planar space in the calculations of geometry data type methods, the SQL Server Database Engine uses SRID 0.

Examples

The following two examples show how to add and query geometry data.

  • The first example creates a table with an identity column and a geometry column GeomCol1. A third column renders the geometry 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 geometry, 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),
    GeomCol1 geometry, 
    GeomCol2 AS GeomCol1.STAsText() );
GO

INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));

INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0));
GO

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

    DECLARE @geom1 geometry;
DECLARE @geom2 geometry;
DECLARE @result geometry;

SELECT @geom1 = GeomCol1 FROM SpatialTable WHERE id = 1;
SELECT @geom2 = GeomCol1 FROM SpatialTable WHERE id = 2;
SELECT @result = @geom1.STIntersection(@geom2);
SELECT @result.STAsText();

[TOP]

See Also

Concepts

Spatial Data (SQL Server)