Skip to main content

OGC Methods on Geometry & Geography Instances

Overview​

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

caution

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

caution

These functions aren'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:

EnvelopeAngle​

EnvelopeAngle returns the maximum angle between the point returned by EnvelopeCenter() and a point in the geography instance in degrees.

Return Type​

float

Syntax​

EnvelopeAngle( )

Example​

DECLARE @g geography = 'LINESTRING(-120 45, -120 0, -90 0)';   
SELECT @g.EnvelopeAngle();

EnvelopeAngle

EnvelopeCenter​

EnvelopeCenter returns a point that you can use as the bounding circle's center for the geography instance.

Return Type​

geography

Syntax​

EnvelopeCenter( )

Example​

DECLARE @g geography = 'LINESTRING(-120 45, -120 0, -90 0)';  
SELECT @g.EnvelopeCenter().ToString();

EnvelopeCenter

InstanceOf​

InstanceOf tests if the geography instance is the same as the specified type. Returns 1 if the type of a geography instance is the same as the specified type, or if the specified type is an ancestor of the instance type; otherwise, returns 0.

Syntax​

.InstanceOf ( 'geography_type')

Example​

The following example creates a MultiPoint instance and uses InstanceOf() to see whether the instance is a GeometryCollection. It confirm that the instance is a GeometryCollection and thus returns a 1.

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

InstanceOf

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

STCurveN​

STCurveN returns the curve specified from a geography instance that is a LineString, CircularString, or CompoundCurve.

Syntax​

.STCurveN( n )

Example​

DECLARE @g geography = 'CIRCULARSTRING(-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653)';  
 SELECT @g.STCurveN(2).ToString();

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 does not 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)​

caution

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)​

caution

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