Geospatial Functions

This article describes the built-in geospatial functions in Exasol.

Exasol provides several functions to execute operations and mathematical calculations on geospatial objects. The arguments in geospatial functions mainly consist of geospatial objects.

Function	Description
Point functions
`ST_X(P)`	`X` coordinate of a Point
`ST_Y(P)`	`Y` coordinate of a Point
(Multi)LineString functions
`ST_ENDPOINT(ls)`	End point of a LineString
`ST_ISCLOSED(mls)`	Defines whether all contained LineStrings are closed rings (their start and end points are identical)
`ST_ISRING(ls)`	Defines whether a LineString is a closed ring (its start and end points are identical)
`ST_LENGTH(mls)`	Length of a LineString or the sum of lengths of all objects of a MultiLineString
`ST_NUMPOINTS(ls)`	Number of Points within the LineString
`ST_POINTN(ls,n)`	The n^th point of a LineString, starting with 1 Returns `NULL` if `ST_NUMPOINTS (ls)` < `n`
`ST_STARTPOINT(ls)`	Start point of a LineString
(Multi)Polygon functions
`ST_AREA(mp)`	Area of a Polygon, or sum of areas of all objects of a MultiPolygon
`ST_EXTERIORRING(pg)`	Outer ring of the object
`ST_INTERIORRINGN(pg,n)`	The n^th hole of a Polygon, starting with 1 Returns `NULL` if `ST_NUMINTERIORRINGS(pg)` < `n`
`ST_NUMINTERIORRINGS(pg)`	Number of holes within a Polygon
GeometryCollection functions
`ST_GEOMETRYN(gc,n)`	The n^th object of a GeometryCollection, starting with 1 Returns `NULL` if `ST_NUMGEOMETRIES(gc)` < `n`
`ST_NUMGEOMETRIES(gc)`	Number of objects within a collection of geometry objects
General functions
`ST_BOUNDARY(g)`	Geometric boundary of a geospatial object (for example, the end points of a LineString or the outer LinearRing of a Polygon)
`ST_BUFFER(g,n)`	Returns a geospatial object whose points have maximal distance `n` to the first argument `g`. This is similar to an extension of the borders of an object. A divided circle, approximated by a number of points, is created around the edges of the object.
`ST_CENTROID(g)`	Geometric center of mass of an object
`ST_CONTAINS(g,g)`	Defines whether the first object fully contains the second one
`ST_CONVEXHULL(g)`	Convex hull of a geospatial object
`ST_CROSSES(g,g)`	Defines whether the two objects cross each other. This is the case if The intersection is not empty and not equal to one of the objects The dimension of the intersection is smaller than the maximal dimension of both arguments
`ST_DIFFERENCE(g,g)`	Difference set of two geospatial objects
`ST_DIMENSION(g)`	Dimension of a geospatial object. For example, 0 for Points, 1 for LineStrings, and 2 for Polygons.
`ST_DISJOINT(g,g)`	Defines whether two geospatial objects are disjointed (their intersection is empty)
`ST_DISTANCE(g,g)`	Minimal distance between two geospatial objects
`ST_ENVELOPE(g)`	Smallest rectangle that contains the geospatial object
`ST_EQUALS(g,g)`	Defines whether two geospatial objects describe the same geometric object. For two objects g1 and g2 this is the case if `ST_WITHIN(g1,g2)` and `ST_WITHIN(g2,g1)`
`ST_FORCE2D(g)`	Makes a two-dimensional object out of a three-dimensional object by ignoring the third coordinate. This function is sometimes needed as Exasol does not support three-dimensional objects.
`ST_GEOMETRYTYPE(g)`	The type of the geospatial object, as a string. For example, `'POINT'` or `'MULTIPOLYGON'`.
`ST_INTERSECTION(g,g)`	Intersection of two geospatial objects
`ST_INTERSECTION(g)`	Intersection of two geospatial objects (aggregated)
`ST_INTERSECTS(g,g)`	Defines whether an intersection of two geospatial objects exists
`ST_ISEMPTY(g)`	Defines whether the geospatial object is an empty geometry. See Examples for an example of usage.
`ST_ISSIMPLE(g)`	Defines whether a geospatial object is simple: POINT - always simple MULTIPOINT - simple if no points are identical LINESTRING - simple if it does not pass through the same Point twice (except start and end points) MULTILINESTRING - simple if all LineStrings are simple and the intersections are within the boundaries of both objects POLYGON - always simple MULTIPOLYGON - always simple
`ST_OVERLAPS(g,g)`	Defines whether two geospatial objects overlap. This is the case if the objects are not identical, their intersection is not empty, and the intersection has the same dimension as the two objects.
`ST_SETSRID(g,srid)`	Sets the SRID for a geometry object. For more information, see EXA_SPATIAL_REF_SYS.
`ST_SYMDIFFERENCE(g,g)`	Symmetric difference set of two geospatial objects
`ST_TOUCHES(g,g)`	Defines whether two geospatial objects touch each other. This is the case if the intersection is not empty and is only located on the boundaries of the objects. for more information, see `ST_BOUNDARY`.
`ST_TRANSFORM(g,srid)`	Converts a geospatial object into the given spatial reference coordinate system. For more information, see EXA_SPATIAL_REF_SYS .
`ST_UNION(g,g)`	Union set of two geospatial objects
`ST_UNION(g)`	Union set of two geospatial objects (aggregated)
`ST_WITHIN(g,g)`	Defines whether the first object is fully contained by the second one (the opposite of `ST_CONTAINS`)

When converting between GEOMETRY types and VARCHAR/CHAR data types, the maximum significant digits used when casting is defined with the ST_MAX_DECIMAL_DIGITS parameter and can be changed using the ALTER SYSTEM or ALTER SESSION statements.

Examples

SELECT ST_Y('POINT (1 2)');

SELECT ST_ENDPOINT('LINESTRING (0 0, 0 1, 1 1)');

SELECT ST_ISRING('LINESTRING (0 0, 0 1, 1 1, 0 0)');

SELECT ST_LENGTH('LINESTRING (0 0, 0 1, 1 1)');

SELECT ST_BOUNDARY('LINESTRING (0 0, 1 1, 2 2)');

SELECT ST_AREA('POLYGON ((0 0, 0 4, 4 4, 4 0, 0 0),
             (1 1, 1 2, 2 2, 2 1, 1 1))');

SELECT ST_DISTANCE('POLYGON ((0 0, 0 4, 4 4, 4 0, 0 0))',
            'POINT(12 10)');

SELECT ST_ISEMPTY('GEOMETRYCOLLECTION EMPTY');

// This is an example of how ST_ISEMPTY can be used to list empty objects:  
CREATE TABLE t1 (id INTEGER, geo GEOMETRY(4326));
INSERT INTO t1 VALUES (1, 'POINT (13.36963 52.52493)'),(2, 'POINT EMPTY'), (3, 'POINT (-0.1233 51.5309)'); 

SELECT id, ST_ISEMPTY(geo) Empty_Geometry from t1;
// Results: 
ID EMPTY_GEOMETRY 
-- -------------- 
1  false          
2  true           
3  false

Geospatial Functions

Examples

PRODUCT

RESOURCES