ST_GeomFromText(text [, srid])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. For more information about particular functions, see the Open GIS specification or the PostGIS manual.
Most Geometry support is limited to two dimensions due to the WKB and WKT formats.
|
Note
|
Geometry support is still evolving. There might be minor differences between Teiid and pushdown results that will need to be further refined. |
ST_GeomFromText
Returns a geometry from a Clob in WKT format.
text is a clob, srid is an optional integer that represents a spatial reference identifier (SRID).
Return value is a geometry.
ST_GeogFromText
Returns a geography from a Clob in (E)WKT format.
ST_GeogFromText(text)text is a clob, srid is an optional integer.
Return value is a geography.
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_GeogFromWKB
Returns a geography from a blob in (E)WKB format.
ST_GeomFromEWKB(bin)bin is a blob.
Return value is a geography.
Only 2 dimensions are supported.
ST_GeomFromEWKT
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(geo)geo is a geometry or geography. 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(geo)geo 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
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.
&&
Returns true if the bounding boxes of geom1 and geom2 intersect.
geom1 && geom2geom1, geom2 are geometries.
Return value is a Boolean.
ST_Contains
Returns true if geom1 contains geom2.
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(geo1, geo2)geo1, geo2 are both geometries or geographies.
Return value is a double.
The geography variant must be pushed down for evaluation.
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 can 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(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
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.
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 returns 0.
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
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 geometry.
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 guaranteed 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(geo)geo is a geometry or geography.
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(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
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.
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 geometry.
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 geometries.
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 geometries.
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 geometries.
Return value is a geometry.
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.
ST_Point
Retuns the Point for the given coordinates.
ST_Point(x, y)x and y are doubles. Return value is a Point geometry.
ST_Polygon
Returns 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.