OGC methods on Geometry & Geography instances

Overview

Cinchy CQL supports the following on Open Geospatial Consortium (OGC) methods on geometry and geography instances.

All functions that have Geometry in parenthesis are only applicable to OGC methods on geometry instances.

This function isn't currently supported in PostgreSQL deployments of the Cinchy platform. Please check back at a later time. For a full list of in-progress function translations, see the CQL functions reference page.

The OGC Methods covered in this section are:

STArea

STArea() returns the total surface area of a geometry/geography instance.

Syntax

.STArea ( )

Return Types

CQL: Number

Remarks

When the geometry/geography instance contains only zero and one-dimensional figures, or if it's empty, STArea() returns 0.

Geometry Example

This example creates a Polygongeometry instance and computes the area of the Polygon:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0),(2 2, 2 1, 1 1, 1 2, 2 2))', 0);
SELECT @g.STArea();

Geography Example

This example creates a Polygon geography instance and computes the area of the Polygon:

DECLARE @g geography;
SET @g = geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326);
SELECT @g.STArea();

STAsBinary

STAsBinary() returns the Open Geospatial Consortium (OGC) Well-Known Binary (WKB) representation of a geometry/geography instance.

Syntax

.STAsBinary ( )

Return Types

CQL: Base64 Text

Geometry Example

This example creates a LineString geometry instance from (0,0) to (2,3) from text. STAsBinary() returns the result in WKB:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 0, 2 3)', 0);
SELECT @g.STAsBinary();

Geography Example

This example uses STAsBinary() to create a LineString geography instance from (-122.360, 47.656) to (-122.343, 47.656) from text. It then returns the result in WKB:

DECLARE @g geography;
SET @g = geography::STGeomFromText('LINESTRING( -122.360 47.656, -122.343 47.656)', 4326);
SELECT @g.STAsBinary();

STAsText

STAsText() returns the Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation of a geometry/geography instance.

Syntax

.STAsText ( )

Return Types

CQL: Text

Remarks

OGC type of a geography instance can be determined by invoking STGeometryType()

The return text won't containZ (elevation) and M (measure) values carried by the instance.

Geometry Example

This example creates a LineString geometry instance from (0,0) to (2,3) from text. STAsText() returns the result in text:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 0, 2 3)', 0);
SELECT @g.STAsText();

Geography Example

This example uses STAsText() to create a LineString geography instance from (-122.360, 47.656) to (-122.343, 47.656) from text. It then returns the result in text:

DECLARE @g geography;
SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);
SELECT @g.STAsText();

STBoundary (Geometry)

STBoundary() returns the boundary of a geometry instance.

Syntax

.STBoundary ( )

Return Types

CQL: geometry

Geometry Example

This example uses STBoundary() on a CurvePolygon instance. STBoundary() returns a CircularString instance:

 DECLARE @g geometry;
 SET @g = geometry::STGeomFromText('CURVEPOLYGON(CIRCULARSTRING(0 0, 2 2, 0 2, -2 2, 0 0))', 0);
 SELECT @g.STBoundary().ToString();

STBuffer

STBuffer()returns a geometric/geography object that represents the union of all points whose distance from a geometry/geography instance is less than or equal to a specified value.

Syntax

.STBuffer ( distance )

Arguments

distance A value of type float (double in the .NET Framework) specifying the distance from the geometry/geography instance around which to calculate the buffer.

Return Types

CQL: geometry/geography

Remarks

STBuffer() calculates a buffer specifying tolerance = distance * .001 and relative = false.

A negative buffer removes all points within the given distance of the boundary of the geometry/geography instance.

The error between the theoretical and computed buffer is max(tolerance, extents 1.E-7) where tolerance = distance * .001.

Geometry:

When distance > 0 then either a Polygonor MultiPolygoninstance is returned. When distance = 0, then a copy of the calling geometry instance is returned. When distance < 0, then:

  • When the dimensions of the instance are 0 or 1, an empty GeometryCollectioninstance is returned.

  • when the dimensions of the instance are 2 or more, a negative buffer is returned.

Geography:

STBuffer() will return a FullGlobeinstance in certain cases; for example, STBuffer() returns a FullGlobeinstance when the buffer distance is greater than the distance from the equator to the poles. A buffer can't exceed the full globe.

This method will throw an ArgumentExceptionin FullGlobeinstances where the distance of the buffer exceeds the following limitation: 0.999 * π * minorAxis * minorAxis / majorAxis (~0.999 * 1/2 Earth's circumference).

Geometry Example

This example returns a Polygon instance with a negative buffer from a CurvePolygon instance:

DECLARE @g geometry = 'CURVEPOLYGON(COMPOUNDCURVE(CIRCULARSTRING(0 4, 4 0, 8 4), (8 4, 0 4)))';
 SELECT @g.STBuffer(-1).ToString();

Geography Example

This example creates a LineString geography instance. It then uses STBuffer() to return the region within 1 meter of the instance:

DECLARE @g geography;
SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);
SELECT @g.STBuffer(1).ToString();

STCentroid

STCentroid() returns the geometry/geography center of a geometry/geography instance that consists of one or more Polygons.

Syntax

.STCentroid ( )

Return Types

CQL: geometry/geography

Remarks

If the geometry/geography instance isn't a Polygon, CurvePolygon, or MultiPolygontypeSTCentroid() returns null.

Geometry Example

This example uses STCentroid() to compute the centroid of a polygongeography instance:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0),(2 2, 2 1, 1 1, 1 2, 2 2))', 0);
SELECT @g.STCentroid().ToString();

STContains (Geometry)

STContains()returns 1 if a geometry instance completely contains another geometry instance. Otherwise, returns 0.

Syntax

.STContains ( other_instance )

Arguments

other_instance Another geometry instance to compare against the instance on which STContains() is invoked.

Return Types

CQL: Yes/No

Remarks

If the spatial reference identifiers (SRIDs) of the geometry instances don't match, STContains() always returns null.

Geometry Example

This example uses STContains() to test two geometry instances to see if the first instance contains the second instance:

DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0);
SET @h = geometry::STGeomFromText('POINT(1 1)', 0);
SELECT @g.STContains(@h);

STConvexHull (Geometry)

STConvexHull() returns an object representing the convex hull of a geometry instance.

Syntax

.STConvexHull ( )

Return Types

CQL: geometry

Remarks

Points or co-linear LineStringinstances will produce an instance of the same type as that of the input. STConvexHull() returns the smallest convex Polygonthat contains the given geometry instance.

Geometry Example

This example uses STConvexHull() to find the convex hull of a non-convex Polygon geometry instance:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 0 2, 1 1, 2 2, 2 0, 0 0))', 0);
SELECT @g.STConvexHull().ToString();

STCrosses (Geometry)

STCrosses() returns 1 if a geometry instance crosses another geometry instance. Otherwise, returns 0.

Syntax

.STCrosses ( other_instance )

Arguments

other_instance Another geometry/geography instance to compare against the instance on which STCrosses() is invoked.

Return Types

CQL: Yes/No

Remarks

If the spatial reference IDs (SRIDs) of the geometry instances don't match, this method always returns null.

Both conditions must be true for two geometry instances to cross:

  • The intersection of the two geometry instances results in a geometry whose dimensions are less than the maximum dimension of the source geometry instances.

  • The intersection set is interior to both source geometry instances.

Geometry Examples

This example uses STCrosses() to test two geometry instances to see if they cross:

DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 2, 2 0)', 0);
SET @h = geometry::STGeomFromText('LINESTRING(0 0, 2 2)', 0);
SELECT @g.STCrosses(@h);

STCurveToLine (Geometry)

STCurveToLine()returns a polygonal approximation of a geometry instance that contains circular arc segments.

Syntax

.STCurveToLine ( )

Return Types

CQL: geometry

Remarks

Returns null for uninitialized geometry variables

The polygonal approximation that the method returns depends on the geometry instance used to call the method:

  • Returns a LineString instance for a CircularStringor CompoundCurveinstance.

  • Returns a Polygoninstance for a CurvePolygoninstance.

  • Returns a copy of the geometry instance if that instance isn't a CircularString, CompoundCurve, or CurvePolygoninstance.

Any z-coordinate values present in the calling geometry instance are ignored.

Geometry Example

In this example, the SELECT statement uses a LineString instance to call the STCurveToLinemethod. Thus, the method returns a LineStringinstance:

 DECLARE @g geometry;
 SET @g = geometry::Parse('LINESTRING(1 3, 5 5, 4 3, 1 3)');
 SET @g = @g.STCurveToLine();
 SELECT @g.STGeometryType();

STDifference (Geometry)

STDifference() returns an object that represents the point set from one geometry instance that doesn't lie within another geometry instance.

Syntax

.STDifference ( other_instance )

Arguments

other_instance Another geometry instance to compare against the instance on which STDifference() is invoked.

Return Types

CQL: geometry

Remarks

Returns null if the spatial reference IDs (SRIDs) of the geometry instances don't match.

Geometry Example

This example uses STDifference() to compute the difference between two Polygons:

DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 0);
SET @h = geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);
SELECT @g.STDifference(@h).ToString();

STDisjoint (Geometry)

STDisjoint()returns 1 if a geometry instance is spatially disjoint from another geometry instance. Otherwise, returns 0.

Syntax

.STDisjoint ( other_instance )

Arguments

other_instance Another geometry instance to compare against the instance on which STDisjoint() is invoked.

Return Types

CQL: Yes/No

Remarks

If the intersection of the two geometry instances point sets are empty, they disjoint.

Returns null if the spatial reference IDs (SRIDs) of the geometry instances don't match.

Geometry Example

This example uses STDisjoint() to test two geometry instances for spatial disjoint:

DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 2, 2 0, 4 2)', 0);
SET @h = geometry::STGeomFromText('POINT(1 1)', 0);
SELECT @g.STDisjoint(@h);

STDistance

STDistance() returns the shortest distance between a point in a geometry/geography instance and a point in another geometry/geography instance.

Syntax

.STDistance ( other_instance )

Arguments

other_instance Another geometry/geography instance to compare against the instance on which STDistance() is invoked.

Return Types

CQL: Number

Remarks

STDistance() always returns null if the spatial reference IDs (SRIDs) of the geometry/geography instances don't match.

Geometry Example

This example finds the distance between two geometry instances:

DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0);
SET @h = geometry::STGeomFromText('POINT(10 10)', 0);
SELECT @g.STDistance(@h);

Geography Example

This example finds the distance between two geography instances:

DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);
SET @h = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);
SELECT @g.STDistance(@h);

STEndpoint (Geometry)

STEndPoint()returns the end point of a geometry instance.

Syntax

.STEndPoint ( )

Return Types

CQL: geometry

Remarks

STEndPoint() is the equivalent of STPointN().

Returns null if called on an empty geometry instance.

Geometry Example

This example creates a LineString instance with STGeomFromText() and uses STEndpoint() to retrieve the end point of the LineString:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 0, 2 2, 1 0)', 0);
SELECT @g.STEndPoint().ToString();

STEnvelope (Geometry)

STEnvelope()returns the minimum axis-aligned bounding rectangle of the instance.

Syntax

STEnvelope ( )

Return Types

CQL: geometry

Geometry Example

This example uses STGeomFromText() to create a LineString instance from (0,0) to (2,3), and uses STEnvelope() to return the bounding box of the LineString:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 0, 2 3)', 0);
SELECT @g.STEnvelope().ToString();

STEquals (Geometry)

STEquals() returns 1 if a geometry instance represents the same point set as another geometry instance. Otherwise, returns 0.

Syntax

.STEquals ( other_instance )

Arguments

other_instance Another geometry instance to compare against the instance on which STEquals() is invoked.

Return Types

CQL: Yes/No

Remarks

Returns null if the spatial reference IDs (SRIDs) of the geometry instances don't match.

Geometry Example

This example creates two geometry instances with STGeomFromText() that are equal but not trivially equal, and uses STEquals() to test their equality:

DECLARE @g geometry
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 2, 2 0, 4 2)', 0);
SET @h = geometry::STGeomFromText('MULTILINESTRING((4 2, 2 0), (0 2, 2 0))', 0);
SELECT @g.STEquals(@h);

STExteriorRing (Geometry)

STExteriorRing()returns the exterior ring of a geometry instance that's a Polygon.

Syntax

.STExteriorRing ( )

Return Types

CQL: geometry

Remarks

Returns null if the geometry instance isn't a Polygon.

Geometry Example

This example creates a polygon instance and uses STExteriorRing() to return the exterior ring of the polygon as a LineString:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0),(2 2, 2 1, 1 1, 1 2, 2 2))', 0);
SELECT @g.STExteriorRing().ToString();

STGeometryN (Geometry)

STGeometryN() returns a specified geometry in a geometry collection.

Syntax

.STGeometryN ( expression )

Arguments

expression Is an int expression between 1 and the number of geometry instances in the GeometryCollection.

Return Types

CQL: geometry

Remarks

Returns null if the parameter is larger than the result of STGeometryN() and will throw an ArgumentOutOfRangeExceptionif the expression parameter is less than 1.

Geometry Example

This example creates a MultiPoint GeometryCollectionand uses STGeometryN() to find the second geometry instance of the collection:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('MULTIPOINT(0 0, 13.5 2, 7 19)', 0);
SELECT @g.STGeometryN(2).ToString();

STGeometryType (Geometry)

STGeometryType()returns the Open Geospatial Consortium (OGC) type name represented by geometry instance.

Syntax

.STGeometryType ( )

Return Types

CQL: Text

Remarks

The OGC type names that can be returned by STGeometryType() are Point, LineString, CircularString, CompoundCurve, Polygon, CurvePolygon, GeometryCollection, MultiPoint, MultiLineString, MultiPolygon, and FullGlobe.

Geometry Example

This example creates a Polygon instance and uses STGeometryType() to confirm that it's a Polygon:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0))', 0);
SELECT @g.STGeometryType();

STGeomCollFromText (Geometry)

Augmented with any Z (elevation) and M (measure) values carried by the instance, STGeomCollFromText()returns a geometry instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.

Syntax

STGeomCollFromText ( 'geometrycollection_tagged_text' , SRID )

Arguments

geometrycollection_tagged_text An nvarchar(max) expression that's the WKT representation of the geometry instance you wish to return.

SRID An int expression representing the spatial reference ID (SRID) of the geometry instance you wish to return.

Return Types

CQL: geometry

Remarks

The OGC type of the geometry instance returned by STGeomCollFromText() is set to the corresponding WKT input.

Throws an ArgumentException if the input isn't valid.

Geometry Example

This example uses STGeomCollFromText() to create a geometry instance:

DECLARE @g geometry;
SET @g = geometry::STGeomCollFromText('GEOMETRYCOLLECTION ( POLYGON((5 5, 10 5, 10 10, 5 5)), POINT(10 10) )', 0);
SELECT @g.ToString();

STGeomFromText

Augmented with any Z (elevation) and M (measure) values carried by the instance, STGeomFromText()returns a geometry/geography instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.

Syntax

STGeomFromText ( 'instance_tagged_text' , SRID )

Arguments

instance_tagged_text An nvarchar(max) expression that's the WKT representation of the geometry/geography instance you wish to return.

SRID An int expression representing the spatial reference ID (SRID) of the geometry/geography instance you wish to return.

Return Types

CQL: geometry/geography

Remarks

The OGC type of the geometry/geography instance returned by STGeomFromText() is set to the corresponding WKT input.

If the input isn't well-formatted, method will throw a FormatException.

Geography:

Throws an ArgumentException if the input contains an antipodal edge.

Geometry Example

This example uses STGeomFromText() to create a geometry instance:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0);
SELECT @g.ToString();

Geography Example

This example uses STGeomCollFromText() to create a geography instance:

DECLARE @g geography;
SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);
SELECT @g.ToString();

STGeomFromWKB

STGeomFromWKB()returns a geometry/geography instance from an Open Geospatial Consortium (OGC) Well-Known Binary (WKB) representation.

Syntax

STGeomFromWKB ( 'WKB_instance' , SRID )

Arguments

WKB_instance An nvarchar(max) expression that's the WKB representation of the geometry/geography instance to return.

SRID An int expression representing the spatial reference ID (SRID) of the geometry/geography instance to return.

Return Types

CQL: geometry/geography

Remarks

The OGC type of the geometry/geography instance returned by STGeomFromWKB() is set to the corresponding WKB input.

If the input isn't well-formatted, method will throw a FormatException.

Geography:

Throws an ArgumentException if the input contains an antipodal edge.

Geometry Example

This example uses STGeomFromWKB() to create a geometry instance:

DECLARE @g geometry;
SET @g = geometry::STGeomFromWKB(0x010200000003000000000000000000594000000000000059400000000000003440000000000080664000000000008066400000000000806640, 0);
SELECT @g.STAsText();

Geography Example

This example uses STGeomFromWKB() to create a geography instance:

DECLARE @g geography;
SET @g = geography::STGeomFromWKB(0x010200000002000000D7A3703D0A975EC08716D9CEF7D34740CBA145B6F3955EC08716D9CEF7D34740, 4326);
SELECT @g.ToString();

STInteriorRingN (Geometry)

STInteriorRingN() returns the specified interior ring of a Polygongeometry instance.

Syntax

.STInteriorRingN ( expression )

Arguments

expression An int expression between 1 and the number of interior rings in the geometry instance.

Return Types

CQL: geometry

Remarks

Returns null if the geometry instance isn't a Polygon.

This method will throw an ArgumentOutOfRangeExceptionif the expression is larger than the number of rings. The number of rings can be returned using STNumInteriorRing().

Geometry Example

This example creates a Polygon instance and uses STInteriorRingN() to return the interior ring of the Polygonas a LineString:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0),(2 2, 2 1, 1 1, 1 2, 2 2))', 0);
SELECT @g.STInteriorRingN(1).ToString();

STIntersection

STIntersection() returns an object that represents the points where a geometry/geography instance intersects another geometry/geography instance.

Syntax

.STIntersection ( other_instance )

Arguments

other_instance Another geometry/geography instance to compare against the instance on which STIntersection() is invoked.

Return Types

CQL: geometry/geography

Remarks

If the spatial reference IDs (SRIDs) of the geometry/geography instances don't match, STIntersection() always returns null.

The result may contain circular arc segments only if the input instances contain them.

Geometry Example

This example uses STIntersection() to compute the intersection of two polygons:

DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 0);
SET @h = geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);
SELECT @g.STIntersection(@h).ToString();

Geography Example

This example uses STIntersection() to compute the intersection of a Polygon and a LineString:

DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326);
SET @h = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);
SELECT @g.STIntersection(@h).ToString();

STIntersects

STIntersects() returns 1 if a geometry instance intersects another geometry instance. Otherwise, returns 0.

Syntax

.STIntersects ( other_instance )

Arguments

other_instance Another geometry/geography instance to compare against the instance on which STIntersects() is invoked.

Return Types

CQL: Yes/No

Remarks

Returns null if the spatial reference IDs (SRIDs) of the geometry/geography instances don't match.

Geometry Example

This example uses STIntersects() to determine if two geometry instances intersect each other:

DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 2, 2 0, 4 2)', 0);
SET @h = geometry::STGeomFromText('POINT(1 1)', 0);
SELECT @g.STIntersects(@h);

Geography Example

This example uses STIntersects() to determine whether two geography instances intersect each other:

DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326);
SET @h = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);

SELECT CASE @g.STIntersects(@h)
WHEN 1 THEN '@g intersects @h'
ELSE '@g doesn't intersect @h'
END;

STIsClosed (Geometry)

STIsClosed()returns 1 if the start and end points of the given geometry instance are the same. Returns 1 for GeometryCollection types if each contained geometry instance is closed. Returns 0 if the instance isn't closed.

Syntax

.STIsClosed ( )

Return Types

CQL: Yes/No

Remarks

Returns 0 if any figures of a geometry instance are points, or if the instance is empty.

All Polygoninstances are considered closed.

Geometry Example

This example creates a LineString instance and uses STIsClosed() to test if the LineString is closed:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 0, 2 2, 1 0)', 0);
SELECT @g.STIsClosed();

STIsEmpty (Geometry)

STIsEmpty()returns 1 if a geometry instance is empty. Returns 0 if a geometry instance isn't empty.

Syntax

.STIsEmpty ( )

Return Types

CQL: Yes/No

Geometry Example

This example creates an empty geometry instance and uses STIsEmpty() to test whether the instance is empty:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POLYGON EMPTY', 0);
SELECT @g.STIsEmpty();

STIsRing (Geometry)

STIsRing() returns 1 if a geometry instance fulfills the following requirements:

  • It's a LineStringinstance.

  • It's closed (for a geometry to be closed, STIsClosed() needs to return 1 when invoked on the instance).

  • It's simple (for a geometry to be simple, STIsSimple() needs to return 1 when invoked on the instance).

  • Returns 0 if the LineStringinstance doesn't meet the requirements.

Syntax

.STIsRing ( )

Return Types

CQL: Yes/No

Remarks

Returns null if the instance isn't a LineString.

Geometry Example

This example creates a LineString instance and uses STIsRing() to test whether the instance is a ring:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 0, 2 2, 1 0, 0 0)', 0);
SELECT @g.STIsRing();

STIsSimple (Geometry)

STIsSimple() returns 1 if a geometry instance is simple, as defined by the Open Geospatial Consortium (OGC). Returns 0 if a geometry instance isn't simple.

Syntax

.STIsSimple ( )

Return Types

CQL: Yes/No

Remarks

To be simple a geometry instance must meet the requirements:

  • Except at the endpoints, each figure of the instance must not intersect itself.

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

Geometry Example

This example creates a non-simple LineString instance that intersects itself and uses STIsSimple() to test whether the LineString is simple:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 0, 2 2, 0 2, 2 0)', 0);
SELECT @g.STIsSimple();

STIsValid (Geometry)

STIsValid()returns true if a geometry instance is well-formed, based on its Open Geospatial Consortium (OGC) type. Returns false if a geometry instance isn't well-formed.

Syntax

.STIsValid ( )

Return Types

CQL: Yes/No

Remarks

The OGC type of a geometry instance can be determined by invoking STGeometryType().

SQL Server produces only valid geometry instances, but allows for the storage and retrieval of invalid instances.

Geometry Example

This example creates a geometry instance and uses STIsValid() to test if the instance is valid:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 0, 2 2, 1 0)', 0);
SELECT @g.STIsValid();

STLength

STLength()returns the total length of the elements in a geometry/geography instance or the geometry/geography instances within a GeometryCollection.

Syntax

.STLength ( )

Return Types

CQL: Yes/No

Remarks

If a geometry/geography instance is closed, its length is calculated as the total length around the instance

The length of a GeometryCollectionis found by calculating the sum of the lengths of all of the geometry/geography instances contained within the collection.

STLength() works on both valid and invalid LineString.

Geometry Example

This example creates a LineString instance and uses STLength() to find the length of the instance:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 0, 2 2, 1 0)', 0);
SELECT @g.STLength();

Geography Example

This example creates a LineString instance and uses STLength() to find the length of the instance:

DECLARE @g geography;
SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);
SELECT @g.STLength();

STLineFromText (Geometry)

Augmented with any Z (elevation) and M (measure) values carried by the instance,STLineFromText()returns a geometry instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.

Syntax

STLineFromText ( 'linestring_tagged_text' , SRID )

Arguments

linestring_tagged_text An nvarchar(max) expression that's the WKT representation of the geometry LineStringinstance you wish to return.

SRID An int expression representing the spatial reference ID (SRID) of the geometry LineString instance you want to return.

Return Types

CQL: geometry

Remarks

If the input isn't well-formatted, method will throw a FormatException.

Geometry Example

This example uses STLineFromText() to create a geometry instance:

DECLARE @g geometry;
SET @g = geometry::STLineFromText('LINESTRING (100 100, 200 200)', 0);
SELECT @g.ToString();

STLineFromWKB (Geometry)

STLineFromWKB()returns a geometry LineStringinstance from an Open Geospatial Consortium (OGC) Well-Known Binary (WKB) representation.

Syntax

STLineFromWKB ( 'WKB_linestring' , SRID )

Arguments

WKB_linestring A varbinary(max) expression that's the WKB representation of the geometry LineString instance to return.

SRID An int expression representing the spatial reference ID (SRID) of the geometry LineString instance you want to return.

Return Types

CQL: geometry

Remarks

If the input isn't well-formatted, method will throw a FormatException.

Geometry Example

This example uses STLineFromWKB() to create a geometry instance:

DECLARE @g geometry;
SET @g = geometry::STLineFromWKB(0x0102000000020000000000000000005940000000000000594000000000000069400000000000006940, 0);
SELECT @g.STAsText();

STMLineFromText (Geometry)

Augmented with any Z (elevation) and M (measure) values carried by the instance, STMLineFromText() returns a geometry instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.

Syntax

STMLineFromText ( 'multilinestring_tagged_text' , SRID )

Arguments

multilinestring_tagged_text An nvarchar(max) expression that's the WKT representation of the geometryMultiLineStringinstance you wish to return.

SRID An int expression representing the spatial reference ID (SRID) of the geometry MultiLineString instance you wish to return.

Return Types

CQL: geometry

Remarks

If the input isn't well-formatted, method will throw a FormatException.

Geometry Example

This example uses STMLineFromText() to create a geometry instance:

DECLARE @g geometry;
SET @g = geometry::STMLineFromText('MULTILINESTRING ((100 100, 200 200), (3 4, 7 8, 10 10))', 0);
SELECT @g.ToString();

STMPointFromText (Geometry)

Augmented with any Z (elevation) and M (measure) values carried by the instance, STMPointFromText()returns a geometry instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.

Syntax

STMPointFromText ( 'multipoint_tagged_text', SRID )

Arguments

multipoint_tagged_text An nvarchar(max) expression that's the WKT representation of the geometry MultiPoint instance you wish to return.

SRID An int expression representing the spatial reference ID (SRID) of the geometryMultiPoint instance you wish to return.

Return Types

CQL: geometry

Remarks

If the input isn't well-formatted, method will throw a FormatException.

Geometry Example

This example uses STMPointFromText() to create a geometry instance:

DECLARE @g geometry;
SET @g = geometry::STMPointFromText('MULTIPOINT ((100 100), (200 200))', 0);
SELECT @g.ToString();

STMPolyFromText (Geometry)

Augmented with any Z (elevation) and M (measure) values carried by the instance, STMPolyFromText()returns a geometry instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.

Syntax

STMPolyFromText ( 'multipolygon_tagged_text' , SRID )

Arguments

multipolygon_tagged_text An nvarchar(max) expression that's the WKT representation of the geometry MultiPolygon instance you wish to return.

SRID Is an int expression representing the spatial reference ID (SRID) of the geometry MultiPolygon instance you wish to return.

ReturnTypes

CQL: geometry

Remarks

If the input isn't well-formatted, method will throw a FormatException.

Example

This example usesSTMPolyFromText() to create a geometry instance:

DECLARE @g geometry;
SET @g = geometry::STMPolyFromText('MULTIPOLYGON (((5 5, 10 5, 10 10, 5 5)), ((10 10, 100 10, 200 200, 30 30, 10 10)))', 0);
SELECT @g.ToString();

STNumCurves (Geometry)

STNumCurves() returns the number of curves in a one-dimensional geometry instance.

Syntax

.STNumCurves()

Return Types

CQL: geometry

Remarks

An empty one-dimensional geometry instance returns 0.

Null is returned when the geometry instance isn't a one-dimensional instance or is an uninitialized instance.

One-dimensional spatial data types include LineString, CircularString, and CompoundCurve. STNumCurves() works only on simple types; it doesn't work with geometry collections like MultiLineString.

Example

This example shows how to get the number of curves in a CircularString instance:

 DECLARE @g geometry;
 SET @g = geometry::Parse('CIRCULARSTRING(10 0, 0 10, -10 0, 0 -10, 10 0)');
 SELECT @g.STNumCurves();

STNumGeometries (Geometry)

STNumGeometries() returns the number of geometries that comprise a geometry instance.

Syntax

.STNumGeometries ( )

Return Types

CQL: Number

Remarks

This method returns 1 if the geometry instance isn't a MultiPoint, MultiLineString, MultiPolygon, or GeometryCollectioninstance, and 0 if the geometry instance is empty.

Example

This example creates a MultiPoint instance and uses STNumGeometries() to find out how many geometries the instance contains:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('MULTIPOINT((-122.360 47.656), (-122.343 47.656))', 4326);
SELECT @g.STNumGeometries();

STNumInteriorRing (Geometry)

STNumInteriorRing() returns the number of interior rings of a Polygongeometry instance.

Syntax

.STNumInteriorRing ( )

Return Types

CQL: Number

Remarks

Returns null if the geometry instance isn't a Polygon.

Example

This example creates a Polygon instance and uses STNumInteriorRing() to find how many interior rings the instance has:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0),(2 2, 2 1, 1 1, 1 2, 2 2))', 0);
SELECT @g.STNumInteriorRing();

STNumPoints (Geometry)

STNumPoints() returns the sum of the number of points in each of the figures in a geometry instance.

Syntax

.STNumPoints ( )

Return Types

CQL: Number

Remarks

STNumPoints() counts the points (duplicate points are counted) in the description of a geometry instance. If this instance is a collection type, this method returns the sum of the points in each of its elements.

Example

This example creates a LineString instance and uses STNumPoints() to determine how many points were used in the description of the instance:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 0, 2 2, 1 0)', 0);
SELECT @g.STNumPoints();

STOverlaps (Geometry)

STOveralps()returns 1 if a geometry instance overlaps another geometry instance. Otherwise, returns 0.

Syntax

.STOverlaps ( other_instance )

Arguments

other_instance Another geometry instance to compare against the instance on which STOverlaps() is invoked.

Return Types

CQL: Yes/No

Remarks

If the points where the geometry instances intersect aren't in the same dimension, STOverlaps() always returns 0.

If the spatial reference IDs (SRIDs) of the geometry instances don't match, STOverlaps() returns null.

Two geometry instances overlap if the region representing their intersection has the same dimension as the instances do and the region doesn't equal either instance.

Example

This example uses STOverlaps() to test two geometry instances for overlap:

DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0);
SET @h = geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);
SELECT @g.STOverlaps(@h);

STPointFromText (Geometry)

Only available in SQL Server implementations.

Augmented with any Z (elevation) and M (measure) values carried by the instance, STPointFromText() returns a geometry instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.

Syntax

STPointFromText ( 'point_tagged_text' , SRID )

Arguments

point_tagged_text An nvarchar(max) expression that's the WKT representation of the geometry Point instance you wish to return.

SRID An int expression representing the spatial reference ID (SRID) of the geometry Pointinstance you wish to return.

ReturnTypes

CQL: geometry

Remarks

If the input isn't well-formatted, method will throw a FormatException.

Example

This example usesSTPointFromText() to create a geometry instance:

DECLARE @g geometry;
SET @g = geometry::STPointFromText('POINT (100 100)', 0);
SELECT @g.ToString();

STPointFromWKB (Geometry)

STPointFromWKB()returns a geometry Point instance from an Open Geospatial Consortium (OGC) Well-Known Binary (WKB) representation.

Syntax

STPointFromWKB ( 'WKB_point' , SRID )

Arguments

WKB_point A varbinary(max) expression that's the WKB representation of the geometry Point instance you wish to return.

SRID An int expression representing the spatial reference ID (SRID) of the geometry Point instance you wish to return.

Return Types

CQL: geometry

Remarks

If the input isn't well-formatted, method will throw a FormatException.

Example

This example usesSTPointFromWKB() to create a geometry instance:

DECLARE @g geometry;
SET @g = geometry::STPointFromWKB(0x010100000000000000000059400000000000005940, 0);
SELECT @g.STAsText();

STPointN (Geometry)

STPointN()returns a specified point in a geometry instance.

Syntax

.STPointN ( expression )

Arguments

expression An int expression between 1 and the number of points in the geometry instance.

Return Types

CQL: geometry

Remarks

Throws an ArgumentOutOfRangeException, if this method is called with a value less than 1.

Returns null if this method is called with a value greater than the number of points in the instance.

STPointN() returns the point specified by expression, if a geometry instance is user created. (occurs by ordering the points in which they were originally input).

STPointN() returns the point specified by expression, if a geometry instance was constructed by the system (by ordering all the points in the same order they would be output: first by geometry, then by ring within the instance (if appropriate), and then by point within the ring).

Example

This example creates a LineString instance and uses STPointN() to retrieve the second point in the description of the instance:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 0, 2 2, 1 0)', 0);
SELECT @g.STPointN(2).ToString();

STPointOnSurface (Geometry)

STPointOnSurface()returns an arbitrary point located within the interior of a geometry instance.

Syntax

.STPointOnSurface ( )

Return Types

CQL: geometry

Remarks

If the instance is empty, method returns null.

Example

This example creates a Polygon instance and uses STPointOnSurface() to find a point on the instance:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0),(2 2, 2 1, 1 1, 1 2, 2 2))', 0);
SELECT @g.STPointOnSurface().ToString();

STPolyFromText (Geometry)

Only available in SQL Server implementations.

Augmented with any Z (elevation) and M (measure) values carried by the instance, STPolyFromText()returns a geometry instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.

Syntax

STPolyFromText ( 'polygon_tagged_text' , SRID )

Arguments

polygon_tagged_text An nvarchar(max) expression that's the WKT representation of the geometry Polygoninstance you wish to return.

SRID An int expression representing the spatial reference ID (SRID) of the geometry Polygon instance you wish to return.

Return Types

CQL: geometry

Remarks

If the input isn't well-formatted, method will throw a FormatException.

Example

This example usesSTPolyFromText() to create a geometry instance:

DECLARE @g geometry;
SET @g = geometry::STPolyFromText('POLYGON ((5 5, 10 5, 10 10, 5 5))', 0);
SELECT @g.ToString();

STRelate (Geometry)

STRelate()returns 1 if a geometry instance is related to another geometry instance, otherwise, returns 0. (The relationship between the geometry instances is defined by a Dimensionally Extended 9 Intersection Model (DE-9IM) pattern matrix value)

Syntax

.STRelate ( other_instance, intersection_pattern_matrix )

Arguments

other_instance Another geometry instance to compare against the instance on which STRelate() is invoked.

intersection_pattern_matrix Is a string of type nchar(9) encoding acceptable values for the DE-9IM pattern matrix device between the two geometry instances.

Return Types

CQL: Yes/No

Remarks

If the spatial reference IDs (SRIDs) of the geometry instances don't match, method returns null.

If matrix isn't well-formed, an ArgumentExceptionwill be thrown.

Example

This example uses STRelate() to test two geometry instances for spatial disjoint using an explicit DE-9IM pattern:

DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 2, 2 0, 4 2)', 0);
SET @h = geometry::STGeomFromText('POINT(5 5)', 0);
SELECT @g.STRelate(@h, 'FF*FF****');

STStartPoint (Geometry)

STStartPoint()returns the start point of a geometry instance.

Syntax

.STStartPoint ( )

Return Types

CQL: geometry

Remarks

STStartPoint() is the equivalent of STPointN().

Example

This example uses STStartPoint() to retrieve the start point of the instance and creates a LineString instance:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 0, 2 2, 1 0)', 0);
SELECT @g.STStartPoint().ToString();

STSymDifference (Geometry)

STSymDifference()returns an object that represents all points that are either in one geometry instance or another geometry instance, but not those points that lie in both instances.

Syntax

.STSymDifference ( other_instance )

Arguments

other_instance Another geometry instance to compare against the instance on which STSymDifference() is invoked.

Return Types

CQL: geometry/geography

Remarks

If the spatial reference IDs (SRIDs) of the geometry instances don't match, method returns null.

Result may contain circular arc segments (only if the input instances contain circular arc segments).

Example

This example uses STSymDifference() to compute the symmetric difference of two Polygon instances:

DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 0);
SET @h = geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);
SELECT @g.STSymDifference(@h).ToString();

STTouches (Geometry)

STTouches()returns 1 if a geometry instance spatially touches another geometry instance. Returns 0 if it doesn't.

Syntax

.STTouches ( other_instance )

Arguments

other_instance Another geometry instance to compare against the instance on which STTouches() is invoked.

Return Types

CQL: Yes/No

Remarks

If two geometry instances point sets intersect, they're touching but their interiors don't intersect.

If the spatial reference IDs (SRIDs) of the geometry instances don't match, method returns null.

Example

This example uses STTouches() to test two geometry instances to see if they touch:

DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 2, 2 0, 4 2)', 0);
SET @h = geometry::STGeomFromText('POINT(1 1)', 0);
SELECT @g.STTouches(@h);

STUnion (Geometry)

STUnion()returns an object that represents the union of a geometry instance with another geometry instance.

Syntax

.STUnion ( other_instance )

Arguments

other_instance Another geometry instance to compare against the instance on which STUnion() is invoked.

Return Types

CQL: geometry

Remarks

If the spatial reference IDs (SRIDs) of the geometry instances don't match, method returns null.

Result may contain circular arc segments (only if the input instances contain circular arc segments).

Example

This example uses STUnion() to compute the union of two Polygon instances:

DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 0);
SET @h = geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);
SELECT @g.STUnion(@h).ToString();

STWithin (Geometry)

STWithin()returns 1 if a geometry instance is completely within another geometry instance; otherwise, returns 0.

Syntax

STWithin ( other_instances )

Arguments

other_instance Another geometry instance to compare against the instance on which STWithin() is invoked.

Return Types

CQL: Yes/No

Remarks

The STWithin command is case-sensitive.

If the spatial reference IDs (SRIDs) of the geometry instances don't match, method returns null.

Example

This example uses STWithin() to test two geometry instances to see if the first instance is completely within the second instance:

DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0);
SET @h = geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);
SELECT @g.STWithin(@h);
PreviousGeometry and Geography data type and functionsNextExtended methods on Geometry & Geography instances

Last updated