Create, Construct, and Query geometry Instances
Applies To: SQL Server 2016
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.
Creating a New geometry Instance from an Existing Instance
The geometry data type provides numerous builtin 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)
In This Topic
Constructing a geometry Instance from WellKnown Text Input
The geometry data type provides several builtin 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)
In This Topic
Constructing a geometry Instance from WellKnown 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)
In This Topic
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)
In This Topic
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)
In This Topic
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 wellformed, 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 wellformed for its instance type
STIsValid (geometry Data Type)
To convert a geometry instance to a wellformed 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)
In This Topic
Number of Points
All nonempty geometry instances are comprised of points. These points represent the X and Ycoordinates of the plane on which the geometries are drawn. geometry provides numerous builtin 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 Xcoordinate of a Point instance
STX (geometry Data Type)
To return the Ycoordinate of a Point instance
STY
To return the geometric center point of a Polygon, CurvePolygon, or MultiPolygon instance
STCentroid
In This Topic
Dimension
A nonempty geometry instance can be 0, 1, or 2dimensional. Zerodimensional geometries, such as Point and MultiPoint, have no length or area. Onedimensional objects, such as LineString, CircularString, CompoundCurve, and MultiLineString, have length. Twodimensional 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
In This Topic
Empty
An emptygeometry 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.
In This Topic
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.



To determine if an instance is simple
STIsSimple.
In This Topic
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
In This Topic
Envelope
The envelope of a geometry instance, also known as the bounding box, is the axisaligned rectangle formed by the minimum and maximum (X,Y) coordinates of the instance.
To return the envelope of an instance
STEnvelope
In This Topic
Closure
A closedgeometry 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
In This Topic
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.
In This Topic
The geometry data type provides many builtin 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
In This Topic
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
columnGeomCol1
. A third column renders thegeometry
column into its Open Geospatial Consortium (OGC) WellKnown Text (WKT) representation, and uses theSTAsText()
method. Two rows are then inserted: one row contains aLineString
instance ofgeometry
, and one row contains aPolygon
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 insertedgeometry
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();
In This Topic