OGC Methods on Geometry & Geography Instances
PreviousGeometry and Geography Data Type and FunctionsNextExtended Methods on Geometry & Geography Instances
Last updated
Last updated
Cinchy CQL supports the following on Open Geospatial Consortium (OGC) methods on geometry and geography instances.
Please note that all functions that have been denoted with Geometry in parenthesis are only applicable to OGC methods on geometry instances.
These function are not currently supported in PostgreSQL deployments of the Cinchy platform.
New function translations are actively being worked on by the development team; please check back at a later time.
You can review the full list of in-progress function translations here.
The OGC Methods covered in this section are:
STArea() returns the total surface area of a geometry/geography instance.
CQL: Number
When the geometry/geography instance contains only zero- and one-dimensional figures, or if it's empty, STArea() returns 0.
This example creates a Polygongeometry instance and computes the area of the Polygon:
This example creates a Polygon geography instance and computes the area of the Polygon:
STAsBinary() returns the Open Geospatial Consortium (OGC) Well-Known Binary (WKB) representation of a geometry/geography instance.
CQL: Base64 Text
This example creates a LineString geometry instance from (0,0) to (2,3) from text. STAsBinary() returns the result in WKB:
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:
STAsText() returns the Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation of a geometry/geography instance.
CQL: Text
OGC type of a geography instance can be determined by invoking STGeometryType()
The return text will not containZ (elevation) and M (measure) values carried by the instance.
This example creates a LineString geometry instance from (0,0) to (2,3) from text. STAsText() returns the result in text:
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:
STBoundary() returns the boundary of a geometry instance.
CQL: geometry
This example uses STBoundary() on a CurvePolygon instance. STBoundary() returns a CircularString instance:
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.
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.
CQL: geometry/geography
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 cannot 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).
This example returns a Polygon instance with a negative buffer from a CurvePolygon instance:
This example creates a LineString geography instance. It then uses STBuffer() to return the region within 1 meter of the instance:
STCentroid() returns the geometry/geography center of a geometry/geography instance that consists of one or more Polygons.
CQL: geometry/geography
If the geometry/geography instance is not a Polygon, CurvePolygon, or MultiPolygontypeSTCentroid() returns null.
This example uses STCentroid() to compute the centroid of a polygongeography instance:
STContains()returns 1 if a geometry instance completely contains another geometry instance, returns 0 if it does not.
other_instance
Another geometry instance to compare against the instance on which STContains() is invoked.
CQL: Yes/No
If the spatial reference IDs (SRIDs) of the geometry instances do not match, STContains() always returns null.
This example uses STContains() to test two geometry instances to see if the first instance contains the second instance:
STConvexHull() returns an object representing the convex hull of a geometry instance.
CQL: geometry
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.
This example uses STConvexHull() to find the convex hull of a non-convex Polygon geometry instance:
STCrosses() returns 1 if a geometry instance crosses another geometry instance. Returns 0 if it does not.
other_instance
Another geometry/geography instance to compare against the instance on which STCrosses() is invoked.
CQL: Yes/No
If the spatial reference IDs (SRIDs) of the geometry instances do not 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.
This example uses STCrosses() to test two geometry instances to see if they cross:
STCurveToLine()returns a polygonal approximation of a geometry instance that contains circular arc segments.
CQL: geometry
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 is not a CircularString, CompoundCurve, or CurvePolygoninstance.
Any z-coordinate values present in the calling geometry instance are ignored.
In this example, the SELECT statement uses a LineString instance to call the STCurveToLinemethod. Thus, the method returns a LineStringinstance:
STDifference() returns an object that represents the point set from one geometry instance that does not lie within another geometry instance.
other_instance
Another geometry instance to compare against the instance on which STDifference() is invoked.
CQL: geometry
Returns null if the spatial reference IDs (SRIDs) of the geometry instances do not match.
This example uses STDifference() to compute the difference between two Polygons:
STDisjoint()returns 1 if a geometry instance is spatially disjoint from another geometry instance, returns 0 if it is not.
other_instance
Another geometry instance to compare against the instance on which STDisjoint() is invoked.
CQL: Yes/No
If the intersection of the two geometry instances point sets are empty, they are disjoint.
Returns null if the spatial reference IDs (SRIDs) of the geometry instances do not match.
This example uses STDisjoint() to test two geometry instances for spatial disjoint:
STDistance() returns the shortest distance between a point in a geometry/geography instance and a point in another geometry/geography instance.
other_instance
Another geometry/geography instance to compare against the instance on which STDistance() is invoked.
CQL: Number
STDistance() always returns null if the spatial reference IDs (SRIDs) of the geometry/geography instances do not match.
This example finds the distance between two geometry instances:
This example finds the distance between two geography instances:
STEndPoint()returns the end point of a geometry instance.
CQL: geometry
STEndPoint() is the equivalent of STPointN().
Returns null if called on an empty geometry instance.
This example creates a LineString instance with STGeomFromText() and uses STEndpoint() to retrieve the end point of the LineString:
STEnvelope()returns the minimum axis-aligned bounding rectangle of the instance.
CQL: geometry
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:
STEquals() returns 1 if a geometry instance represents the same point set as another geometry instance, returns 0 if it does not.
other_instance
Another geometry instance to compare against the instance on which STEquals() is invoked.
CQL: Yes/No
Returns null if the spatial reference IDs (SRIDs) of the geometry instances do not match.
This example creates two geometry instances with STGeomFromText() that are equal but not trivially equal, and uses STEquals() to test their equality:
STExteriorRing()returns the exterior ring of a geometry instance that is a Polygon.
CQL: geometry
Returns null if the geometry instance is not a Polygon.
This example creates a polygon instance and uses STExteriorRing() to return the exterior ring of the polygon as a LineString:
STGeometryN() returns a specified geometry in a geometry collection.
expression
Is an int expression between 1 and the number of geometry instances in the GeometryCollection.
CQL: geometry
Returns null if the parameter is larger than the result of STGeometryN() and will throw an ArgumentOutOfRangeExceptionif the expression parameter is less than 1.
This example creates a MultiPoint GeometryCollectionand uses STGeometryN() to find the second geometry instance of the collection:
STGeometryType()returns the Open Geospatial Consortium (OGC) type name represented by geometry instance.
CQL: Text
The OGC type names that can be returned by STGeometryType() are Point, LineString, CircularString, CompoundCurve, Polygon, CurvePolygon, GeometryCollection, MultiPoint, MultiLineString, MultiPolygon, and FullGlobe.
This example creates a Polygon instance and uses STGeometryType() to confirm that it is a Polygon:
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.
geometrycollection_tagged_text An nvarchar(max) expression that is 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.
CQL: geometry
The OGC type of the geometry instance returned by STGeomCollFromText() is set to the corresponding WKT input.
Throws an ArgumentException if the input is not valid.
This example uses STGeomCollFromText() to create a geometry instance:
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.
inctance_tagged_text An nvarchar(max) expression that is 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.
CQL: geometry/geography
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.
This example uses STGeomFromText() to create a geometry instance:
This example uses STGeomCollFromText() to create a geography instance:
STGeomFromWKB()returns a geometry/geography instance from an Open Geospatial Consortium (OGC) Well-Known Binary (WKB) representation.
WKB_instance An nvarchar(max) expression that is 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.
CQL: geometry/geography
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.
This example uses STGeomFromWKB() to create a geometry instance:
This example uses STGeomFromWKB() to create a geography instance:
STInteriorRingN() returns the specified interior ring of a Polygongeometry instance.
expression An int expression between 1 and the number of interior rings in the geometry instance.
CQL: geometry
Returns null if the geometry instance is not 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().
This example creates a Polygon instance and uses STInteriorRingN() to return the interior ring of the Polygonas a LineString:
STIntersection() returns an object that represents the points where a geometry/geography instance intersects another geometry/geography instance.
other_instance
Another geometry/geography instance to compare against the instance on which STIntersection() is invoked.
CQL: geometry/geography
If the spatial reference IDs (SRIDs) of the geometry/geography instances do not match, STIntersection() always returns null.
The result may contain circular arc segments only if the input instances contain them.
This example uses STIntersection() to compute the intersection of two polygons:
This example uses STIntersection() to compute the intersection of a Polygon and a LineString:
STIntersects() returns 1 if a geometry instance intersects another geometry instance. Returns 0 if it does not.
other_instance
Another geometry/geography instance to compare against the instance on which STIntersects() is invoked.
CQL: Yes/No
Returns null if the spatial reference IDs (SRIDs) of the geometry/geography instances do not match.
This example uses STIntersects() to determine if two geometry instances intersect each other:
This example uses STIntersects() to determine whether two geography instances intersect each other:
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 is not closed.
CQL: Yes/No
Returns 0 if any figures of a geometry instance are points, or if the instance is empty.
All Polygoninstances are considered closed.
This example creates a LineString instance and uses STIsClosed() to test if the LineString is closed:
STIsEmpty()returns 1 if a geometry instance is empty. Returns 0 if a geometry instance is not empty.
CQL: Yes/No
This example creates an empty geometry instance and uses STIsEmpty() to test whether the instance is empty:
STIsRing() returns 1 if a geometry instance fulfills the following requirements:
It is a LineStringinstance.
It is closed (for a geometry to be closed, STIsClosed() needs to return 1 when invoked on the instance).
It is simple (for a geometry to be simple, STIsSimple() needs to return 1 when invoked on the instance).
Returns 0 if the LineStringinstance does not meet the requirements.
CQL: Yes/No
Returns null if the instance is not a LineString.
This example creates a LineString instance and uses STIsRing() to test whether the instance is a ring:
STIsSimple() returns 1 if a geometry instance is simple, as defined by the Open Geospatial Consortium (OGC). Returns 0 if a geometry instance is not simple.
CQL: Yes/No
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 is not in both of their boundaries.
This example creates a non-simple LineString instance that intersects itself and uses STIsSimple() to test whether the LineString is simple:
STIsValid()returns true if a geometry instance is well-formed, based on its Open Geospatial Consortium (OGC) type. Returns false if a geometry instance is not well-formed.
CQL: Yes/No
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.
This example creates a geometry instance and uses STIsValid() to test if the instance is valid:
STLength()returns the total length of the elements in a geometry/geography instance or the geometry/geography instances within a GeometryCollection.
CQL: Yes/No
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.
This example creates a LineString instance and uses STLength() to find the length of the instance:
This example creates a LineString instance and uses STLength() to find the length of the instance:
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.
linestring_tagged_text
An nvarchar(max) expression that is 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.
CQL: geometry
If the input isn't well-formatted, method will throw a FormatException.
This example uses STLineFromText() to create a geometry instance:
STLineFromWKB()returns a geometry LineStringinstance from an Open Geospatial Consortium (OGC) Well-Known Binary (WKB) representation.
WKB_linestring
A varbinary(max) expression that is the WKB representation of the geometry LineStringinstance to return.
SRID
An int expression representing the spatial reference ID (SRID) of the geometry LineStringinstance you want to return.
CQL: geometry
If the input isn't well-formatted, method will throw a FormatException.
This example uses STLineFromWKB() to create a geometry instance:
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.
multilinestring_tagged_text
An nvarchar(max) expression that is 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.
CQL: geometry
If the input isn't well-formatted, method will throw a FormatException.
This example uses STMLineFromText() to create a geometry instance:
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.
multipoint_tagged_text
An nvarchar(max) expression that is 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.
CQL: geometry
If the input isn't well-formatted, method will throw a FormatException.
This example uses STMPointFromText() to create a geometry instance:
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.
multipolygon_tagged_text
An nvarchar(max) expression that is 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.
CQL: geometry
If the input isn't well-formatted, method will throw a FormatException.
This example usesSTMPolyFromText() to create a geometry instance:
STNumCurves() returns the number of curves in a one-dimensional geometry instance.
CQL: geometry
An empty one-dimensional geometry instance returns 0.
Null is returned when the geometry instance is not 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 does not work with geometry collections like MultiLineString.
This example shows how to get the number of curves in a CircularString instance:
STNumGeometries() returns the number of geometries that comprise a geometry instance.
CQL: Number
This method returns 1 if the geometry instance is not a MultiPoint, MultiLineString, MultiPolygon, or GeometryCollectioninstance, and 0 if the geometry instance is empty.
This example creates a MultiPoint instance and uses STNumGeometries() to find out how many geometries the instance contains:
STNumInteriorRing() returns the number of interior rings of a Polygongeometry instance.
CQL: Number
Returns null if the geometry instance is not a Polygon.
This example creates a Polygon instance and uses STNumInteriorRing() to find how many interior rings the instance has:
STNumPoints() returns the sum of the number of points in each of the figures in a geometry instance.
CQL: Number
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.
This example creates a LineString instance and uses STNumPoints() to determine how many points were used in the description of the instance:
STOveralps()returns 1 if a geometry instance overlaps another geometry instance. Returns 0 if it does not.
other_instance
Another geometry instance to compare against the instance on which STOverlaps() is invoked.
CQL: Yes/No
If the points where the geometry instances intersect are not in the same dimension, STOverlaps() always returns 0.
If the spatial reference IDs (SRIDs) of the geometry instances do not 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 does not equal either instance.
This example uses STOverlaps() to test two geometry instances for overlap:
Only available in SQLServer 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.
point_tagged_text
An nvarchar(max) expression that is 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.
CQL: geometry
If the input isn't well-formatted, method will throw a FormatException.
This example usesSTPointFromText() to create a geometry instance:
STPointFromWKB()returns a geometry Point instance from an Open Geospatial Consortium (OGC) Well-Known Binary (WKB) representation.
WKB_point
A varbinary(max) expression that is 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.
CQL: geometry
If the input isn't well-formatted, method will throw a FormatException.
This example usesSTPointFromWKB() to create a geometry instance:
STPointN()returns a specified point in a geometry instance.
expression An int expression between 1 and the number of points in the geometry instance.
CQL: geometry
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).
This example creates a LineString instance and uses STPointN() to retrieve the second point in the description of the instance:
STPointOnSurface()returns an arbitrary point located within the interior of a geometry instance.
CQL: geometry
If the instance is empty, method returns null.
This example creates a Polygon instance and uses STPointOnSurface() to find a point on the instance:
Only available in SQLServer 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.
polygon_tagged_text
An nvarchar(max) expression that is 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.
CQL: geometry
If the input isn't well-formatted, method will throw a FormatException.
This example usesSTPolyFromText() to create a geometry instance:
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)
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.
CQL: Yes/No
If the spatial reference IDs (SRIDs) of the geometry instances do not match, method returns null.
If matrix is not well-formed, an ArgumentExceptionwill be thrown.
This example uses STRelate() to test two geometry instances for spatial disjoint using an explicit DE-9IM pattern:
STStartPoint()returns the start point of a geometry instance.
CQL: geometry
STStartPoint() is the equivalent of STPointN().
This example uses STStartPoint() to retrieve the start point of the instance and creates a LineString instance:
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.
other_instance
Another geometry instance to compare against the instance on which STSymDifference() is invoked.
CQL: geometry/geography
If the spatial reference IDs (SRIDs) of the geometry instances do not match, method returns null.
Result may contain circular arc segments (only if the input instances contain circular arc segments).
This example uses STSymDifference() to compute the symmetric difference of two Polygon instances:
STTouches()returns 1 if a geometry instance spatially touches another geometry instance. Returns 0 if it does not.
other_instance
Another geometry instance to compare against the instance on which STTouches() is invoked.
CQL: Yes/No
If two geometry instances point sets intersect, they are touching but their interiors do not intersect.
If the spatial reference IDs (SRIDs) of the geometry instances do not match, method returns null.
This example uses STTouches() to test two geometry instances to see if they touch:
STUnion()returns an object that represents the union of a geometry instance with another geometry instance.
other_instance
Another geometry instance to compare against the instance on which STUnion() is invoked.
CQL: geometry
If the spatial reference IDs (SRIDs) of the geometry instances do not match, method returns null.
Result may contain circular arc segments (only if the input instances contain circular arc segments).
This example uses STUnion() to compute the union of two Polygon instances:
STWithin()returns 1 if a geometry instance is completely within another geometry instance; otherwise, returns 0.
other_instance
Another geometry instance to compare against the instance on which STWithin() is invoked.
CQL: Yes/No
The STWithin command is case-sensitive.
If the spatial reference IDs (SRIDs) of the geometry instances do not match, method returns null.
This example uses STWithin() to test two geometry instances to see if the first instance is completely within the second instance: