Spatial Functions

Spatial functions provide functionality for working with geospatial data. For more information about particular functions, see the Open GIS specification or the PostGIS manual.

Most geometry capabilities are limited to two dimensions due to the WKB and WKT formats.

There might be minor differences between CData Virtuality and pushdown results that will need to be further refined.

ST_GeomFromText

This function returns a geometry from a Clob in WKT format.

Syntax

SQL

ST_GeomFromText(text [, srid])

• text is a CLOB;
• srid is an optional integer that represents a spatial reference identifier (SRID).

Return value is a geometry.

ST_GeogFromText

This funtion returns a geography from a Clob in (E)WKT format.

Syntax

SQL

ST_GeogFromText(text)

• text is a CLOB;
• srid is an optional integer.

Return value is a geography.

ST_GeomFromWKB(bin [, srid])

ST_GeomFromEWKB(bin)

ST_GeomFromEWKB(bin)

ST_GeomFromEWKT(text)

ST_GeomFromGeoJson(`text` [, srid])

ST_GeomFromGML(text [, srid])

ST_AsText(geom)

ST_AsBinary(geo)

ST_AsText(geom)

ST_AsBinary(geo)

ST_AsEWKB(geom)

ST_AsGeoJSON(geom)

ST_AsGML

Syntax

ST_AsGML(geom)

• geom is a geometry.

Return value is a CLOB with the GML2 value.

ST_AsEWKT

Syntax

ST_AsEWKT(geo)

• geom is a geometry or geography.

Return value is a CLOB with the EWKT value. The EWKT value is the WKT value with the SRID prefix.

ST_AsKML

Syntax

ST_AsKML(geom)

• geom is a geometry.

Return value is a CLOB with the KML value. The KML value is effectively a simplified GML value and projected into SRID 4326.

&&

This function returns TRUE if the bounding boxes of geom1 and geom2 intersect.

Syntax

geom1 && geom2

• geom1, geom2 are geometries.

Return value is a boolean.

ST_Contains

This function returns TRUE if geom1 contains geom2.

Syntax

ST_Contains(geom1, geom2)

• geom1, geom2 are geometries.

Return value is a boolean.

ST_Crosses

This function returns TRUE if the geometries cross.

Syntax

ST_Crosses(geom1, geom2)

• geom1, geom2 are geometries.

Return value is a boolean.

ST_Disjoint

This function returns TRUE if the geometries are disjointed.

Syntax

ST_Disjoint(geom1, geom2)

• geom1, geom2 are geometries.

Return value is a boolean.

ST_Distance

This function returns the distance between two geometries.

Syntax

ST_Distance(geo1, geo2)

• geom1, geom2 are both geometries or geographies. 

Return value is a double. The geography variant must be pushed down for evaluation.

ST_DWithin

This function returns TRUE if the geometries are within a given distance of one another.

Syntax

ST_DWithin(geom1, geom2, dist)

• geom1, geom2 are geometries;
• dist is a double.

Return value is a boolean.

ST_Equals

This function returns TRUE if the two geometries are spatially equal. The points and order can differ, but neither geometry lies outside of the other.

Syntax

ST_Equals(geom1, geom2)

• geom1, geom2 are geometries.

Return value is a boolean.

ST_Intersects

This function returns TRUE if the geometries intersect.

Syntax

ST_Intersects(geo1, geo2)

• geo1, geo2 are both geometries or geographies.

Return value is a boolean. The geography variant must be pushed down for evaluation.

ST_OrderingEquals

This function returns TRUE if geom1 and geom2 have the same structure and the same ordering of points.

Syntax

ST_OrderingEquals(geom1, geom2)

• geom1, geom2 are geometries.

Return value is a boolean. 

ST_Overlaps

This function returns TRUE if the geometries overlap.

Syntax

ST_Overlaps(geom1, geom2)

• geom1, geom2 are geometries.

Return value is a boolean. 

ST_Relate

This function test or return the intersection of geom1 and geom2.

Syntax

ST_Relate(geom1, geom2, pattern)

• geom1, geom2 are geometries;
• pattern is a nine-character DE-9IM pattern string.

Return value is a boolean. 

ST_Relate(geom1, geom2)

• geom1, geom2 are geometries.

Return value is the nine-character DE-9IM intersection string.

ST_Touches

This function returns TRUE if the geometries touch.

Syntax

ST_Touches(geom1, geom2)

• geom1, geom2 are geometries.

Return value is a boolean. 

ST_Within

his function returns TRUE if geom1 is completely inside geom2.

Syntax

ST_Within(geom1, geom2)

• geom1, geom2 are geometries.

Return value is a boolean. 

ST_Area

This function returns the area of geom.

Syntax

ST_Area(geom)

• geom is a geometry.

Return value is a double.

 ST_CoordDim

This function returns the coordinate dimensions of geom.

Syntax

ST_CoordDim(geom)

• geom is a geometry.

Return value is an integer between 0 and 3.

ST_Dimension

This function returns the dimension of geom.

Syntax

ST_Dimension(geom)

• geom is a geometry.

Return value is an integer between 0 and 3.

ST_EndPoint

This function returns the end point of the LineString geom. It returns null if geom is not a LineString.

Syntax

ST_EndPoint(geom)

• geom is a geometry.

Return value is a geometry.

ST_ExteriorRing

This function returns the exterior ring or shell LineString of the polygon geom. Returns null if geom is not a polygon.

Syntax

ST_ExteriorRing(geom)

• geom is a geometry.

Return value is a geometry.

ST_GeometryN

This function returns the nth geometry at the given 1-based index in geom. Returns null if a geometry at the given index does not exist. Non-collection types return themselves at the first index.

Syntax

ST_GeometryN(geom, index)

• geom is a geometry;
• index is an integer.

Return value is a geometry.

ST_GeometryType

This function returns the type name of geom as ST_name. Where name will be LineString, Polygon, Point etc.

Syntax

ST_GeometryType(geom)

• geom is a geometry.

Return value is a string.

ST_HasArc

This function tests if the geometry has a circular string. Returns FALSE because the translator does not work with curved geometry types.

Syntax

ST_HasArc(geom)

• geom is a geometry.

Return value is a geometry.

ST_InteriorRingN

This function returns the nth interior ring LinearString geometry at the given 1-based index in geom. Returns null if a geometry at the given index does not exist, or if geom is not a polygon.

Syntax

ST_InteriorRingN(geom, index)

• geom is a geometry;
• index is an integer.

Return value is a geometry.

ST_IsClosed

This function returns TRUE if LineString geom is closed and FALSE if geom is not a LineString.

Syntax

ST_IsClosed(geom)

• geom is a geometry.

Return value is a boolean.

ST_IsEmpty

This function returns TRUE if the set of points is empty.

Syntax

ST_IsEmpty(geom)

• geom is a geometry.

Return value is a boolean.

ST_IsRing

This function returns TRUE if the LineString geom is a ring and FALSE if geom is not a LineString.

Syntax

ST_IsRing(geom)

• geom is a geometry.

Return value is a boolean.

ST_IsSimple

This function returns TRUE if the geom is simple.

Syntax

ST_IsSimple(geom)

• geom is a geometry.

Return value is a boolean.

ST_IsValid

This function returns TRUE if the geom is valid.

Syntax

ST_IsValid(geom)

• geom is a geometry.

Return value is a boolean.

ST_Length

This function returns the length of a (Multi)LineString, otherwise returns 0.

Syntax

ST_Length(geo)

• geo is a geometry or a geography.

Return value is a double. The geography variant must be pushed down for evaluation.

ST_NumGeometries

This function returns the number of geometries in geom. Will return 1 if not a geometry collection.

Syntax

ST_NumGeometries(geom)

• geom is a geometry.

Return value is an integer.

ST_NumInteriorRings

This function returns the number of interior rings in the polygon geometry. Returns null if geom is not a polygon.

Syntax

ST_NumInteriorRings(geom)

• geom is a geometry.

Return value is an integer.

ST_NunPoints

This function returns the number of points in geom.

Syntax

ST_NunPoints(geom)

• geom is a geometry.

Return value is an integer.

ST_PointOnSurface

This function returns a point that is guaranteed to be on the surface of geom.

Syntax

ST_PointOnSurface(geom)

• geom is a geometry.

Return value is an point geometry.

ST_Perimeter

This function returns the perimeter of the (Multi)Polygon geom. Will return 0 if geom is not a (Multi)Polygon.

Syntax

ST_Perimeter(geom)

• geom is a geometry.

Return value is a double.

ST_PointN

This function returns the nth point at the given 1-based index in geom. Returns null if a point at the given index does not exist or if geom is not a LineString.

Syntax

ST_PointN(geom, index)

• geom is a geometry;
• index is an integer.

Return value is a geometry.

ST_SRID

This function returns the SRID for the geometry.

Syntax

ST_SRID(geo)

• geo is a geometry or geography.

Return value is an integer. A 0 rather than null will be returned for an unknown SRID on a non-null geometry.

ST_SetSRID

This function sets the SRID for the given geometry.

Syntax

ST_SetSRID(geo, srid)

• geo is a geometry or geography;
• srid is an integer.

Return value is the same as the value of geo. Only the SRID metadata of is modified. No transformation is performed.

ST_StartPoint

This function returns the Start Point of the LineString geom. Returns null if geom is not a LineString.

Syntax

ST_StartPoint(geom)

• geom is a geometry.

Return value is a geometry.

ST_X

This function returns the X ordinate value, or null if the point is empty. Throws an exception if the geometry is not a point.

Syntax

ST_X(geom)

• geom is a geometry.

Return value is a double.

ST_Y

This function returns the Y ordinate value, or null if the point is empty. Throws an exception if the geometry is not a point.

Syntax

ST_Y(geom)

• geom is a geometry.

Return value is a double.

ST_Z

This function returns the Z ordinate value, or null if the point is empty. Throws an exception if the geometry is not a point. Typically returns null because the translator does not work with more than two dimensions.

Syntax

ST_Z(geom)

• geom is a geometry.

Return value is a double.

ST_Boundary

This function computes the boundary of the given geometry.

Syntax

ST_Boundary(geom)

• geom is a geometry.

Return value is a geometry.

ST_Buffer

This function computes the geometry that has points within the given distance of geom.

Syntax

ST_Buffer(geom, distance)

• geom is a geometry;
• distance is a double.

Return value is a geometry.

ST_Centroid

This function computes the geometric center point of geom.

Syntax

ST_Centroid(geom)

• geom is a geometry.

Return value is a geometry.

ST_ConvexHull

This function returns the smallest convex polygon that contains all of the points in geometry.

Syntax

ST_ConvexHull(geom)

• geom is a geometry.

Return value is a geometry.

ST_CurveToLine

This function converts a CircularString/CurvedPolygon to a LineString/Polygon. Not currently implemented in CData Virtuality.

Syntax

ST_CurveToLine(geom)

• geom is a geometry.

Return value is a geometry.

ST_Difference

This function computes the closure of the point set of the points contained in geom1 that are not in geom2.

Syntax

ST_Difference(geom1, geom2)

• geom1, geom2 are geometries.

Return value is a geometry.

ST_Envelope

This function computes the 2D bounding box of the given geometry.

Syntax

ST_Envelope(geom)

• geom is a geometry.

Return value is a geometry.

ST_Force_2D

This function removes the Z coordinate value if present.

Syntax

ST_Force_2D(geom)

• geom is a geometry.

Return value is a geometry.

ST_Intersection

This function computes the point set intersection of the points contained in geom1 and in geom2.

Syntax

ST_Intersection(geom1, geom2)

• geom1, geom2 are geometries.

Return value is a geometry.

ST_Simplify

This function simplifies a geometry using the Douglas-Peucker algorithm, but may oversimplify to an invalid or empty geometry.

Syntax

ST_Simplify(geom, distanceTolerance)

• geom is a geometry;
• distanceTolerance is a double.

Return value is a geometry.

ST_SimplifyPreserveTopology

This function simplifies a geometry using the Douglas-Peucker algorithm. Will always return a valid geometry.

Syntax

ST_SimplifyPreserveTopology(geom, distanceTolerance)

• geom is a geometry;
• distanceTolerance is a double.

Return value is a geometry.

ST_SnapToGrid

This function snaps all points in the geometry to grid of given size.

Syntax

ST_SnapToGrid(geom, size)

• geom is a geometry;
• size is a double.

Return value is a geometry.

ST_SymDifference

This function returns the part of geom1 that does not intersect with geom2, and vice versa.

Syntax

ST_SymDifference(geom1, geom2)

• geom1, geom2 are geometries.

Return value is a geometry.

ST_Transform

This function transforms the geometry value from one coordinate system to another.

Syntax

ST_Transform(geom, srid)

• geom is a geometry;
• srid is an integer.

Return value is a geometry. The srid value and the SRID of the geometry value must exist in the SPATIAL_REF_SYS view.

ST_Union

This function returns a geometry that represents the point set containing all of geom1 and geom2.

Syntax

ST_SymDifference(geom1, geom2)

• geom1, geom2 are geometries.

Return value is a geometry.

ST_Extent

This function computes the 2D bounding box around all of the geometry values. All values should have the same SRID.

Syntax

ST_Extent(geom)

• geom is a geometry.

Return value is a geometry.

ST_Point

This function returns the Point for the given coordinates.

Syntax

ST_Point(x, y)

• x, y are doubles.

Return value is a Point geometry.

ST_Polygon

This function returns the Polygon with the given shell and SRID.

Syntax

ST_Polygon(geom, srid)

• geom is a linear ring geometry;
• srid is an integer.

Return value is a Polygon geometry.