# Create, Construct, and Query geometry Instances

**APPLIES TO:** SQL Server Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse

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:

OGC Specifications, Simple Feature Access Part 1 - Common Architecture

OGC Specifications, Simple Feature Access Part 2 - SQL Options

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.

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

**To create a buffer around a geometry**

STBuffer (geometry Data Type)

BufferWithTolerance (geometry Data Type)

**To create a simplified version of a geometry**

Reduce (geometry Data Type)

**To create the convex hull of a geometry**

STConvexHull (geometry Data Type)

**To create a geometry from the intersection of two geometries**

STIntersection (geometry Data Type)

**To create a geometry from the union of two geometries**

STUnion (geometry Data Type)

**To create a geometry from the points where one geometry does not overlap another**

STDifference (geometry Data Type)

**To create a geometry from the points where two geometries do not overlap**

STSymDifference (geometry Data Type)

**To create an arbitrary Point instance that lies on an existing geometry**

STPointOnSurface (geometry Data Type)

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

**To construct any type of geometry instance from WKT input**

STGeomFromText (geometry Data Type)

**To construct a geometry Point instance from WKT input**

STPointFromText (geometry Data Type)

**To construct a geometry MultiPoint instance from WKT input**

STMPointFromText (geometry Data Type)

**To construct a geometry LineString instance from WKT input**

STLineFromText (geometry Data Type)

**To construct a geometry MultiLineString instance from WKT input**

STMLineFromText (geometry Data Type)

**To construct a geometry Polygon instance from WKT input**

STPolyFromText (geometry Data Type)

**To construct a geometry MultiPolygon instance from WKT input**

STMPolyFromText (geometry Data Type)

**To construct a geometry GeometryCollection instance from WKT input**

STGeomCollFromText (geometry Data Type)

### 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:

**To construct any type of geometry instance from WKB input**

STGeomFromWKB (geometry Data Type)

**To construct a geometry Point instance from WKB input**

STPointFromWKB (geometry Data Type)

**To construct a geometry MultiPoint instance from WKB input**

STMPointFromWKB (geometry Data Type)

**To construct a geometry LineString instance from WKB input**

STLineFromWKB (geometry Data Type)

**To construct a geometry MultiLineString instance from WKB input**

STMLineFromWKB (geometry Data Type)

**To construct a geometry Polygon instance from WKB input**

STPolyFromWKB (geometry Data Type)

**To construct a geometry MultiPolygon instance from WKB input**

STMPolyFromWKB (geometry Data Type)

**To construct a geometry GeometryCollection instance from WKB input**

STGeomCollFromWKB (geometry Data Type)

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

**To construct any type of geometry instance from GML input**

GeomFromGml (geometry Data Type)

## 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:

**To return the WKT representation of a geometry instance**

STAsText (geometry Data Type)

**To return the WKT representation of a geometry instance including any Z and M values**

AsTextZM (geometry Data Type)

**To return the WKB representation of a geometry instance**

STAsBinary (geometry Data Type)

**To return a GML representation of a geometry instance**

AsGml (geometry Data Type)

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

**To return the instance type of a geometry**

STGeometryType (geometry Data Type)

**To determine if a geometry is a given instance type**

InstanceOf (geometry Data Type)

**To determine if a geometry instance is well-formed for its instance type**

STIsValid (geometry Data Type)

**To convert a geometry instance to a well-formed geometry instance with an instance type**

MakeValid (geometry Data Type)

**To return the number of geometries in a geometry collection instance**

STNumGeometries (geometry Data Type)

To return a specific geometry in a geometry collection instance

STGeometryN (geometry Data Type)STGeometryN (geometry Data type)

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

**To return the number of points that comprise an instance**

STNumPoints (geometry Data Type)

**To return a specific point in an instance**

STPointN

**To return an arbitrary point that lies on an instance**

STPointOnSurface

**To return the start point of an instance**

STStartPoint

**To return the end point of an instance**

STEndpoint

**To return the X-coordinate of a Point instance**

STX (geometry Data Type)

**To return the Y-coordinate of a Point instance**

STY

**To return the geometric center point of a Polygon, CurvePolygon, or MultiPolygon instance**

STCentroid

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

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

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

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

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

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

**To determine if an instance is closed**

STIsClosed

**To determine if an instance is a ring**

STIsRing

**To return the exterior ring of a Polygon instance**

STExteriorRing

**To return the number of interior rings in a Polygon**

STNumInteriorRing

**To return a specified interior ring of a Polygon**

STInteriorRingN

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

Note

This property can be modified.

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

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

### Example A.

This 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
```

### Example B.

This 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();
```