# Create, Construct, and Query geometry Instances

**SQL Server 2014**

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: http://schemas.microsoft.com/sqlserver/profiles/gml/SpatialGML.xsd.

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
- To create a simplified version of a geometry
- To create the convex hull of a geometry
- To create a geometry from the intersection of two geometries
- To create a geometry from the union of two geometries
- To create a geometry from the points where one geometry does not overlap another
- To create a geometry from the points where two geometries do not overlap
- To create an arbitrary Point instance that lies on an existing geometry

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
- To construct a geometry Point instance from WKT input
- To construct a geometry MultiPoint instance from WKT input
- To construct a geometry LineString instance from WKT input
- To construct a geometry MultiLineString instance from WKT input
- To construct a geometry Polygon instance from WKT input
- To construct a geometry MultiPolygon instance from WKT input
- To construct a geometry GeometryCollection instance from WKT 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
- To construct a geometry Point instance from WKB input
- To construct a geometry MultiPoint instance from WKB input
- To construct a geometry LineString instance from WKB input
- To construct a geometry MultiLineString instance from WKB input
- To construct a geometry Polygon instance from WKB input
- To construct a geometry MultiPolygon instance from WKB input
- To construct a geometry GeometryCollection instance from WKB 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

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
- To return the WKT representation of a geometry instance including any Z and M values
- To return the WKB representation of a geometry instance
- To return a GML representation of a geometry instance

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.

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
- To determine if a geometry is a given instance type
- To determine if a geometry instance is well-formed for its instance type
- To convert a geometry instance to a well-formed geometry instance with an instance type
- To return the number of geometries in a geometry collection instance
- To return a specific geometry in a geometry collection instance
STGeometryN (geometry Data Type)STGeometryN (geometry Data type)

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
- To return a specific point in an instance
- To return an arbitrary point that lies on an instance
- To return the start point of an instance
- To return the end point of an instance
- To return the X-coordinate of a Point instance
- To return the Y-coordinate of a Point instance
- To return the geometric center point of a Polygon, CurvePolygon, or MultiPolygon instance

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
- To return the length of an instance
- To return the area of an instance

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

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

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

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
- To determine if an instance is a ring
- To return the exterior ring of a Polygon instance
- To return the number of interior rings in a Polygon
- To return a specified interior ring of a Polygon

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
- To determine if two instances are disjoint
- To determine if two instances intersect
- To determine if two instances touch
- To determine if two instances overlap
- To determine if two instances cross
- To determine if one instance is within another
- To determine if one instance contains another
- To determine if one instance overlaps another
- To determine if two instances are spatially related
- To determine the shortest distance between points in two geometries

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.

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