Spatial Functions

Spatial functions provide functionality for working with geospatial data. Teiid relies on the JTS Topology Suite to provide partial support for the OpenGIS Simple Features Specification For SQL Revision 1.1. Please refer to the specification or to PostGIS for more details about particular functions.

Most Geometry support is limited to two dimensions due to the WKB and WKT formats.

Note
 Geometry support is still evolving. There may be minor differences between Teiid and pushdown results that will need to be further refined.

Conversion Functions

ST_GeomFromText

Returns a geometry from a Clob in WKT format.

ST_GeomFromText(text [, srid])

text is a clob, srid is an optional integer. Return value is a geometry.

ST_GeomFromWKB/ST_GeomFromBinary

Returns a geometry from a blob in WKB format.

ST_GeomFromWKB(bin [, srid])

bin is a blob, srid is an optional integer. Return value is a geometry.

ST_GeomFromEWKB

Returns a geometry from a blob in EWKB format.

ST_GeomFromEWKB(bin)

bin is a blob. Return value is a geometry. Only 2 dimensions are supported.

ST_GeomFromText

Returns a geometry from a Clob in EWKT format.

ST_GeomFromEWKT(text)

text is a clob. Return value is a geometry. Only 2 dimensions are supported.

ST_GeomFromGeoJSON

Returns a geometry from a Clob in GeoJSON format.

ST_GeomFromGeoJson(text [, srid])

text is a clob, srid is an optional integer. Return value is a geometry.

ST_GeomFromGML

Returns a geometry from a Clob in GML2 format.

ST_GeomFromGML(text [, srid])

text is a clob, srid is an optional integer. Return value is a geometry.

ST_AsText

ST_AsText(geom)

geom is a geometry. Return value is clob in WKT format.

ST_AsBinary

ST_AsBinary(geom)

geom is a geometry. Return value is a blob in WKB format.

ST_AsEWKB

ST_AsEWKB(geom)

geom is a geometry. Return value is blob in EWKB format.

ST_AsGeoJSON

ST_AsGeoJSON(geom)

geom is a geometry. Return value is a clob with the GeoJSON value.

ST_AsGML

ST_AsGML(geom)

geom is a geometry. Return value is a clob with the GML2 value.

ST_AsEWKT

ST_AsEWKT(geom)

geom is a geometry. Return value is a clob with the EWKT value. The EWKT value is the WKT value with the SRID prefix.

ST_AsKML

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.

Operators

&&

Returns true if the bounding boxes of geom1 and geom2 intersect.

geom1 && geom2

geom1, geom2 are geometries. Return value is a boolean.

Relationship Functions

ST_Contains

Returns true if geom1 contains geom2 contains another.

ST_Contains(geom1, geom2)

geom1, geom2 are geometries. Return value is a boolean.

ST_Crosses

Returns true if the geometries cross.

ST_Crosses(geom1, geom2)

geom1, geom2 are geometries. Return value is a boolean.

ST_Disjoint

Returns true if the geometries are disjoint.

ST_Disjoint(geom1, geom2)

geom1, geom2 are geometries. Return value is a boolean.

ST_Distance

Returns the distance between two geometries.

ST_Distance(geom1, geom2)

geom1, geom2 are geometries. Return value is a double.

ST_DWithin

Returns true if the geometries are within a given distance of one another.

ST_DWithin(geom1, geom2, dist)

geom1, geom2 are geometries. dist is a double. Return value is a boolean.

ST_Equals

Returns true if the two geometries are spatially equal - the points and order may differ, but neither geometry lies outside of the other.

ST_Equals(geom1, geom2)

geom1, geom2 are geometries. Return value is a boolean.

ST_Intersects

Returns true if the geometries intersect.

ST_Intersects(geom1, geom2)

geom1, geom2 are geometries. Return value is a boolean.

ST_OrderingEquals

Returns true if geom1 and geom2 have the same structure and the same ordering of points.

ST_OrderingEquals(geom1, geom2)

geom1, geom2 are geometries. Return value is a boolean.

ST_Overlaps

Returns true if the geometries overlap.

ST_Overlaps(geom1, geom2)

geom1, geom2 are geometries. Return value is a boolean.

ST_Relate

Test or return the intersection of geom1 and geom2.

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

Returns true if the geometries touch.

ST_Touches(geom1, geom2)

geom1, geom2 are geometries. Return value is a boolean.

ST_Within

Returns true if geom1 is completely inside geom2.

ST_Within(geom1, geom2)

geom1, geom2 are geometries. Return value is a boolean.

Attributes and Tests

ST_Area

Returns the area of geom.

ST_Area(geom)

geom is a geometry. Return value is a double.

ST_CoordDim

Returns the coordinate dimensions of geom.

ST_CoordDim(geom)

geom is a geometry. Return value is an integer between 0 and 3.

ST_Dimension

Returns the dimension of geom.

ST_Dimension(geom)

geom is a geometry. Return value is an integer between 0 and 3.

ST_EndPoint

Returns the end Point of the LineString geom. Returns null if geom is not a LineString.

ST_EndPoint(geom)

geom is a geometry. Return value is a geometry.

ST_ExteriorRing

Returns the exterior ring or shell LineString of the Polygon geom. Returns null if geom is not a Polygon.

ST_ExteriorRing(geom)

geom is a geometry. Return value is a geometry.

ST_GeometryN

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.

ST_GeometryN(geom, index)

geom is a geometry. index is an integer. Return value is a geometry.

ST_GeometryType

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

ST_GeometryType(geom)

geom is a geometry. Return value is a string.

ST_HasArc

Test if the geometry has a circular string. Will currently only report false as curved geometry types are not supported.

ST_HasArc(geom)

geom is a geometry. Return value is a geometry.

ST_InteriorRingN

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.

ST_InteriorRingN(geom, index)

geom is a geometry. index is an integer. Return value is a geometry.

ST_IsClosed

Returns true if LineString geom is closed. Returns false if geom is not a LineString

ST_IsClosed(geom)

geom is a geometry. Return value is a boolean.

ST_IsEmpty

Returns true if the set of points is empty.

ST_IsEmpty(geom)

geom is a geometry. Return value is a boolean.

ST_IsRing

Returns true if the LineString geom is a ring. Returns false if geom is not a LineString.

ST_IsRing(geom)

geom is a geometry. Return value is a boolean.

ST_IsSimple

Returns true if the geom is simple.

ST_IsSimple(geom)

geom is a geometry. Return value is a boolean.

ST_IsValid

Returns true if the geom is valid.

ST_IsValid(geom)

geom is a geometry. Return value is a boolean.

ST_Length

Returns the length of a (Multi)LineString otherwise 0.

ST_Length(geom)

geom is a geometry. Return value is a double.

ST_NumGeometries

Returns the number of geometries in geom. Will return 1 if not a geometry collection.

ST_NumGeometries(geom)

geom is a geometry. Return value is an integer.

ST_NumInteriorRings

Returns the number of interior rings in the Polygon geom. Returns null if geom is not a Polygon.

ST_NumInteriorRings(geom)

geom is a geometry. Return value is an integer.

ST_NunPoints

Returns the number of Points in geom.

ST_NunPoints(geom)

geom is a geometry. Return value is an integer.

ST_PointOnSurface

Returns a Point that is guarenteed to be on the surface of geom.

ST_PointOnSurface(geom)

geom is a geometry. Return value is a Point geometry.

ST_Perimeter

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

ST_Perimeter(geom)

geom is a geometry. Return value is a double.

ST_PointN

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.

ST_PointN(geom, index)

geom is a geometry. index is an integer. Return value is a geometry.

ST_SRID

Returns the SRID for the geometry.

ST_SRID(geom)

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

ST_SetSRID

Set the SRID for the given geometry.

ST_SetSRID(geom, srid)

geom is a geometry. srid is an integer. Return value is a geometry. Only the SRID metadata of the geometry is modified.

ST_StartPoint

Returns the start Point of the LineString geom. Returns null if geom is not a LineString.

ST_StartPoint(geom)

geom is a geometry. Return value is a geometry.

ST_X

Returns the X ordinate value, or null if the Point is empty. Throws an exception if the Geometry is not a Point.

ST_X(geom)

geom is a geometry. Return value is a double.

ST_Y

Returns the Y ordinate value, or null if the Point is empty. Throws an exception if the Geometry is not a Point.

ST_Y(geom)

geom is a geometry. Return value is a double.

ST_Z

Returns the Z ordinate value, or null if the Point is empty. Throws an exception if the Geometry is not a Point. Will typically return null as 3 dimensions are not fully supported.

ST_Z(geom)

geom is a geometry. Return value is a double.

Misc. Functions

ST_Boundary

Computes the boundary of the given geometry.

ST_Boundary(geom)

geom is a geometry. Return value is a geometry.

ST_Buffer

Computes the geometry that has points within the given distance of geom.

ST_Buffer(geom, distance)

geom is a geometry. distance is a double. Return value is a geometry.

ST_Centroid

Computes the geometric center Point of geom.

ST_Centroid(geom)

geom is a geometry. Return value is a geometry.

ST_ConvexHull

Return the smallest convex Polygon that contains all of the points in geom.

ST_ConvexHull(geom)

geom is a geometry. Return value is a geometry.

ST_CurveToLine

Converts a CircularString/CurvedPolygon to a LineString/Polygon. Not currently implemented in Teiid.

ST_CurveToLine(geom)

geom is a geometry. Return value is a geometry.

ST_Difference

Computes the closure of the point set of the points contained in geom1 that are not in geom2

ST_Difference(geom1, geom2)

geom1, geom2 are geometry. Return value is a geometry.

ST_Envelope

Computes the 2D bounding box of the given geometry.

ST_Envelope(geom)

geom is a geometry. Return value is a geometry.

ST_Force_2D

Removes the z coordinate value if present.

ST_Force_2D(geom)

geom is a geometry. Return value is a geometry.

ST_Intersection

Computes the point set intersection of the points contained in geom1 and in geom2

ST_Intersection(geom1, geom2)

geom1, geom2 are geometry. Return value is a geometry.

ST_Simplify

Simplifies a Geometry using the Douglas-Peucker algorithm, but may oversimplify to an invalid or empty geometry.

ST_Simplify(geom, distanceTolerance)

geom is a geometry. distanceTolerance is a double. Return value is a geometry.

ST_SimplifyPreserveTopology

Simplifies a Geometry using the Douglas-Peucker algorithm. Will always return a valid geometry.

ST_SimplifyPreserveTopology(geom, distanceTolerance)

geom is a geometry. distanceTolerance is a double. Return value is a geometry.

ST_SnapToGrid

Snaps all points in the geometry to grid of given size.

ST_SnapToGrid(geom, size)

geom is a geometry. size is a double. Return value is a geometry.

ST_SymDifference

Return the part of geom1 that does not intersect with geom2 and vice versa.

ST_SymDifference(geom1, geom2)

geom1, geom2 are geometry. Return value is a geometry.

ST_Transform

Transforms the geometry value from one coordinate system to another.

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

Return a geometry that represents the point set containing all of geom1 and geom2.

ST_Union(geom1, geom2)

geom1, geom2 are geometry. Return value is a geometry.

Aggregate Functions

ST_Extent

Computes the 2D bounding box around all of the geometry values. All values should have the same srid.

ST_Extent(geom)

geom is a geometry. Return value is a geometry.

Construction Functions

ST_Point

Retuns the Point for the given cooridinates.

ST_Point(x, y)

x and y are doubles. Return value is a Point geometry.

ST_Polygon

Retuns the Polygon with the given shell and srid.

ST_Polygon(geom, srid)

geom is a linear ring geometry and srid is an integer. Return value is a Polygon geometry.

results matching ""

    No results matching ""