Spatial Operators - Ocient Documentation

spatial noperators perform geometry calculations on geospatial data to return a different type of geospatial data.

ST_BOUNDINGDIAGONAL

Returns the diagonal LINESTRING from the minimum point to the maximum point of the bounding box that ST_ENVELOPE returns. Syntax

SQL

ST_BOUNDINGDIAGONAL(geo)

Argument	Data Type	Description
`geo`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be measured for the diagonal length of its bounding box.

Example

SQL

SELECT ST_BOUNDINGDIAGONAL(
        ST_POLYGON(ST_LINESTRING('LINESTRING(1 2, 1 3, 1 3, 1 2)'))
    );

Output: LINESTRING(1 2,1 3)

ST_BUFFER

Returns a geography that contains all points where the distance from the geography is less than or equal to the specified distance. The function simplifies the geometry before buffering unless you specify the simplification_tolerance argument with a zero or negative value. The output of the function is an array of polygons. For POINT and LINESTRING data types, the returned array contains one polygon only. Syntax

SQL

ST_BUFFER(geo, radius [, num_segments_per_quarter_circle]
          [, simplification_tolerance] [, maximum_radius_error]
          [, join_style] [, miter_limit]
          [, endcap_style] [, side_style])

Argument	Data Type	Description
`geo`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object, specified as a point, linestring, or polygon, which the function buffers.
`radius`	`DOUBLE`	Radius, specified as a double, that indicates the distance to buffer the geospatial object in meters. Negative values cause polygons to shrink. Also, negative values cause the result to be empty for points and linestrings.
`num_segments_per_quarter_circle`	`BIGINT`	Optional. The number of segments to use per quarter circle when parts of the buffer are round, specified as a BIGINT. Only use if the `join_style` or `endcap_style` value is set to `ROUND`. Defaults to `8`.
`simplification_tolerance`	`DOUBLE`	Optional. Value that the function passes to the ST_SIMPLIFYARRAY function prior to buffering. If you specify a zero or negative value, the function skips simplification of the geometry. The unit is meters. Defaults to `500`.
`maximum_radius_error`	`DOUBLE`	Optional. Maximum radius error, specified as a double. The returned buffer might be slightly larger than the `radius` value by this amount. This value is required because arcs of great circles cannot be parallel. Use this argument for linestrings and polygons only. Defaults to `10.0`.
`join_style`	`STRING`	Optional. The type of join to use at the corners where segments of the geometry meet, specified as a string. Supported styles are `ROUND`, `BEVEL`, and `MITER`. Use this argument for linestrings and polygons only. Defaults to `ROUND`.
`miter_limit`	`DOUBLE`	Optional. Miter limit, specified as a double. Use this argument only if the `join_style` value is set to `MITER`. A mitered corner is cut off at a distance of `miter_ratio * radius` from the corner point of the geometry. Use this argument for linestrings and polygons only. Defaults to `1.0`.
`endcap_style`	`STRING`	Optional. The type of end cap for use at the ends of linestrings, specified as a string. Supported styles are `ROUND`, `SQUARE`, and `FLAT`. Use this argument for linestrings only. Defaults to `ROUND`.
`side_style`	`STRING`	Optional. The side, specified as a string, that denotes the side for buffering the geometry. Supported styles are `BOTH`, `LEFT`, and `RIGHT`. Use this argument for linestrings only. Defaults to `BOTH`.

Example

SQL

SELECT ST_BUFFER(
        ST_POLYGON('POLYGON((0 0, 1 0, 1 1, 0 0))'), 2000, 8, 500.0, 10.0,
        'MITER', 2.0);

Output:

[POLYGON((2.202700241764562E-18 -0.017986411844962975, 1.0179863625702694 -0.0179863616840571, 1.0179892379654352 1.031486286416526, 1.009550207729886 1.0349816974070565, -0.03498171178141835 -0.009548649645081482, -0.03148633724650228 -0.01798632287046757, 2.202700241764562E-18 -0.017986411844962975))]

ST_CLOSESTPOINT

Returns the two-dimensional POINT of one specified geospatial object that is closest to a second specified geospatial object. This function works with any geospatial type. If either geospatial argument is empty, the function returns NULL. Syntax

SQL

ST_CLOSESTPOINT(geo1, geo2 [, use_spheroid] )

Argument	Data Type	Description
`geo1`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be computed to determine its closest `POINT` value relative to `geo2`.
`geo2`	`POINT`, `LINESTRING`, or `POLYGON`	A second geospatial object to be compared to `geo1` to find the closest `POINT` value of `geo1`.
`use_spheroid`	`BOOLEAN`	Optional. If you set this value to `TRUE`, this function uses a spheroid model instead of a spherical model. Defaults to `FALSE`.

Example

SQL

SELECT ST_CLOSESTPOINT(
        ST_POLYGON(ST_LINESTRING('LINESTRING(2 3, 2 4, 2 4, 2 3)')),
        ST_POLYGON(ST_LINESTRING('LINESTRING(1 2, 1 4, 1 5, 1 2)')));

Output: POINT(2,3)

ST_CONVEXHULL

Returns a POLYGON representing the convex hull of the input geometry. The convex hull is the smallest convex geometry that encloses the input geometry. If the geo argument is empty, the function returns an empty POLYGON. Syntax

SQL

ST_CONVEXHULL(geo)

Argument	Data Type	Description
`geo`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be computed for a `POLYGON` of its convex hull. If `geo` is a `POINT` or a single-point `LINESTRING` or `POLYGON`, the function returns a `POLYGON` containing only that point value. If `geo` is NULL, the function returns NULL.

Example

SQL

SELECT ST_CONVEXHULL(
        ST_POLYGON('POLYGON((0 0,1 0,1 1,0.5 0.5,0.3 0.3,0 1,0 0))'));

Output: POLYGON((0.0 0.0, 1.0 0.0, 1.0 1.0, 0.0 1.0, 0.0 0.0))

ST_DIFFERENCEARRAY

Returns an array containing any geospatial objects that are present in the first specified geospatial argument that are not found in the second geospatial argument. Syntax

SQL

ST_DIFFERENCEARRAY(geo1, geo2)

Argument	Data Type	Description
`geo1`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be compared to `geo2` to find any unique geospatial values.
`geo2`	`POINT`, `LINESTRING`, or `POLYGON`	A second geospatial object used to compare with `geo1` to find unique values. The function captures only objects that are unique to `geo1`, and not those unique to `geo2`.

Example In this example, the function returns LINESTRING(2.0 2.0, 3.0 3.0) because that portion of the first LINESTRING argument is not present in the second argument.

SQL

SELECT ST_DIFFERENCEARRAY(
        ST_LINESTRING('LINESTRING(1 1, 2 2, 3 3)'),
        ST_LINESTRING('LINESTRING(1 1, 2 2)'));

Output: [LINESTRING(2.0 2.0, 3.0 3.0)]

ST_ENVELOPE

Returns a POLYGON that represents the minimum bounding box for the specified geography. If the specified argument is empty, the function returns an empty POLYGON. A single POINT argument results in a single POINT. Syntax

SQL

ST_ENVELOPE(geo)

Argument	Data Type	Description
`geo`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be computed for the vertices of its bounding box.

Example

SQL

SELECT ST_ENVELOPE(
        ST_POLYGON(ST_LINESTRING('LINESTRING(10 10, 0 0)')));

Output: POLYGON((0 0,10 0,10 10,0 10,0 0))

ST_EXPAND

Returns the bounding box of a specified geospatial value, which is expanded by a specified length. The unit of length is in meters. Syntax

SQL

ST_EXPAND(geo1, units_to_expand)

Argument	Data Type	Description
`geo`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be computed for the vertices of its bounding box, with the added `units_to_expand`.
`units_to_expand`	`DOUBLE`	An additional distance to expand the bounding box of `geo`. Specify this argument only in meters.

Example

SQL

ST_EXPAND(ST_POLYGON('POLYGON((1 1, 2 1, 2 2, 1 1))'), 1.0);

Output: POLYGON((0.99 0.99, 1.33 0.99, 1.66 0.99, 2.01 0.99, 2.00 2.00, 1.66 2.00, 1.33 2.00, 0.99 2.00, 0.99 0.99))

ST_EXTERIORRING

Returns a LINESTRING that represents the exterior ring of a provided POLYGON value. If you specify a geography value that is not a POLYGON, the function returns NULL. Syntax

SQL

ST_EXTERIORRING(polygon)

Argument	Data Type	Description
`polygon`	`POLYGON`	A `POLYGON` object to be computed for a `LINESTRING` of its perimeter.

Example

SQL

SELECT ST_EXTERIORRING(ST_POLYGON('POLYGON((1 1, 3 1, 2 2, 1 1))'));

Output: LINESTRING(1.0 1.0, 3.0 1.0, 2.0 2.0, 1.0 1.0)

ST_FLIPCOORDINATES

Returns a new geographic object with the X and Y coordinates switched using the specified argument. The return type is the same as the specified type. Syntax

SQL

ST_FLIPCOORDINATES(geo)

Argument	Data Type	Description
`geo`	`POINT`, `LINESTRING`, or `POLYGON`	A geographic object for switching its X and Y coordinates.

Example

SQL

ST_FLIPCOORDINATES(ST_POINT('POINT(1 3)'));

Output: POINT(3.0 1.0)

ST_FORCE2D

Convert a geographic object into a two-dimensional geography. The output representations only have the X and Y coordinates. The returned type is the same as the specified type. Syntax

SQL

ST_FORCE2D(geo1)

Argument	Data Type	Description
`geo`	`POINT`, `LINESTRING`, or `POLYGON`	A geographic object to be converted to a two-dimensional object.

Example

SQL

SELECT ST_FORCE2D(ST_POINT('POINT(1 1)'));

Output: POINT(1.0 1.0)

ST_INTERIORRINGN

Returns a LINESTRING representing the interior ring of the specified POLYGON, which is specified by its index. (1-indexed) Returns NULL if the index is out of range, or if you specify a non-POLYGON geography. Syntax

SQL

ST_INTERIORRINGN(polygon, index)

Argument	Data Type	Description
`polygon`	`POLYGON`	A `POLYGON` object to be computed for its interior ring.
`index`	`INTEGER`	An index that represents the specific interior ring of the `polygon` value to be captured. Interior-ring indexes are 1-based, meaning the first object has an index of `1`.

Example

SQL

SELECT ST_INTERIORRINGN(
        ST_POLYGON(
            ST_LINESTRING(ST_POINT [](ST_POINT(1, 2))),
            ST_LINESTRING [](ST_LINESTRING(ST_POINT [](ST_POINT(1, 2))))),
        1);

Output: LINESTRING(1 2)

ST_INTERSECTALL

Returns the intersection of all geographies in the specified array. All geographies in the array must be the same type. The ST_INTERSECTALL function ignores NULL values in the array. The output geography has the same type as the input geometries. Syntax

SQL

ST_INTERSECTALL(array)

Argument	Data Type	Description
`array`	`ARRAY` of `POINT`, `LINESTRING`, or `POLYGON`	An array of geographic objects, which all must be the same type.

Example

SQL

SELECT ST_INTERSECTALL(
        ST_POINT [](ST_POINT(0, 0), ST_POINT(1, 1), ST_POINT(2, 2)));

Output: POINT[]

ST_INTERSECTIONARRAY

Returns a geography that represents the point-set intersection of two geographies. If the two geographies are of different types, the function returns an array of the smallest geography. For example, if geo1 is a POINT, and geo2 is a POLYGON, the function returns an array of POINT values. Syntax

SQL

ST_INTERSECTIONARRAY(geo1, geo2)

Argument	Data Type	Description
`geo1`	`POINT`, `LINESTRING`, or `POLYGON`	A geographic object to be compared to `geo2` to find a set of intersections.
`geo2`	`POINT`, `LINESTRING`, or `POLYGON`	A geographic object to be compared to `geo1` to find a set of intersections.

Example This example compares two polygons to find any intersections. Because both objects are POLYGON types, the result is also a POLYGON containing all intersection points.

SQL

SELECT ST_INTERSECTIONARRAY(
    ST_POLYGON(ST_LINESTRING(ST_POINT(1,2),ST_POINT(3,4))),
    ST_POLYGON(ST_LINESTRING(ST_POINT(1,2),ST_POINT(4,5))));

Output: [POLYGON((1, 2))]

ST_LONGESTLINE

Returns the longest LINESTRING between two specified geospatial arguments. Syntax

SQL

ST_LONGESTLINE(geo1, geo2, use_spheroid)

Argument	Data Type	Description
`geo1`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object for the determination of the longest `LINESTRING` relative to `geo2`.
`geo2`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object for the determination of the longest `LINESTRING` relative to `geo1`.
`use_spheroid`	`BOOLEAN`	Optional. If you set this value to `TRUE`, this function uses a spheroid model instead of a spherical model. Defaults to `FALSE`.

Example

SQL

SELECT ST_LONGESTLINE(
    ST_MAKELINE('LINESTRING(0 3, 1 3)'),
    ST_MAKELINE('LINESTRING(2 3, 0 3)'));

Output: LINESTRING(0.0 3.0, 2.0 3.0)

To use ST_LONGESTLINE as a spatiotemporal function to calculate the longest line between two points at a concurrent time, see the ST_LONGESTLINE function.

ST_MAKEENVELOPE

Alias for ST_ENVELOPE(ST_LINESTRING(ST_POINT(xmin, ymin), ST_POINT(xmax, ymax))). Returns a POLYGON with vertices representing the minimum bounding box for the specified coordinates. Syntax

SQL

ST_MAKEENVELOPE(xmin, ymin, xmax, ymax)

Argument	Data Type	Description
`xmin`	`DOUBLE`	The minimum x value for the bounding box.
`ymin`	`DOUBLE`	The minimum y value for the bounding box.
`xmax`	`DOUBLE`	The maximum x value for the bounding box.
`ymax`	`DOUBLE`	The maximum y value for the bounding box.

Example

SQL

SELECT ST_MAKEENVELOPE(0, 0, 10, 10);

Output: POLYGON((0.0 0.0, 10.0 0.0, 10.0 10.0, 0.0 10.0, 0.0 0.0))

ST_MINIMUMBOUNDINGCIRCLE

Returns the smallest circle POLYGON that contains the specified geographic object. The function requires an additional argument to define the number of segments for inclusion in each quarter of the returned circle. Syntax

SQL

ST_MINIMUMBOUNDINGCIRCLE(geo, num_segs_per_qt_circ)

Argument	Data Type	Description
`geo`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object for the determination of the smallest possible circle `POLYGON` to encompass it.
`num_segs_per_qt_circ`	`INTEGER`	The number of segments to be included in each quarter of the returned circle `POLYGON`.

Example

SQL

SELECT ST_MINIMUMBOUNDINGCIRCLE(ST_POLYGON('POLYGON((1 1, 2 1, 2 2, 1 1))'), 3);

Output: POLYGON((1.49 2.91, 0.27 0.79, 2.72 0.79, 1.49 2.91))

ST_MULTIDIFFERENCEARRAY

Returns an array of geographies representing the parts of the union of the geographies in the first array that do not intersect with the union of geographies in the second array. This operation is not symmetric, meaning ST_MULTIDIFFERENCEARRAY(array1, array2) does not produce the same results as ST_MULTIDIFFERENCEARRAY(array2, array1) unless both arrays are identical. The function ignores NULL types. For types besides NULL, all geographies in both arrays must be of the same type. The output geographies are the same type as the input geographies. Syntax

SQL

ST_MULTIDIFFERENCEARRAY(array1, array2)

Argument	Data Type	Description
`array1`	`ARRAY` of `POINT`, `LINESTRING`, or `POLYGON` values.	An array of geographic objects to be compared to the geographic objects in `array2`. The function returns any values in this array that do not intersect with `array2`.
`array2`	`ARRAY` of `POINT`, `LINESTRING`, or `POLYGON` values.	An array of geographic objects to be compared to the geographic objects in `array1`. The function returns only values that do not intersect in `array1`.

Example

SQL

ST_MULTIDIFFERENCEARRAY(
    POINT[](ST_POINT(0, 0), ST_POINT(1, 1)),
    POINT[](ST_POINT(1, 1), ST_POINT(2, 2)));

Output: ['POINT(0.0 0.0)']

ST_MULTIINTERSECTIONARRAY

When you specify two arrays of geospatial objects, this function returns an array of any intersections. The function treats each specified array as a union of all its geospatial values. All geographies in both arrays must be of the same type, not counting NULL values, which the function ignores. The output geographies are the same type as the input geographies. Syntax

SQL

ST_MULTIINTERSECTIONARRAY(array1, array2)

Argument	Data Type	Description
`array1`	`ARRAY` of `POINT`, `LINESTRING`, or `POLYGON` values.	An array of geographic objects to be compared to the geographic objects in `array2` for any intersections. The data types in this array must match the data types in `array2` unless they are NULL.
`array2`	`ARRAY` of `POINT`, `LINESTRING`, or `POLYGON` values.	An array of geographic objects to be compared to the geographic objects in `array1` for any intersections. The data types in this array must match the data types in `array1` unless they are NULL.

Example

SQL

SELECT ST_MULTIINTERSECTIONARRAY(
    POINT[](ST_POINT(0, 0), ST_POINT(1, 1)),
    POINT[](ST_POINT(1, 1), ST_POINT(2, 2)));

Output: ['POINT(1.0 1.0)']

ST_MULTISYMDIFFERENCEARRAY

Alias for ST_MULTIUNIONARRAY(array1, array2) - ST_MULTIINTERSECTIONARRAY(array1, array2). When you specify two arrays of geospatial objects, this function returns an array of any geospatial values that do not intersect. The function treats each specified array as a union of all its geospatial values. All geographies in both arrays must be of the same type, not counting NULL values, which the function ignores. The output geographies are the same type as the input geographies. Syntax

SQL

ST_MULTISYMDIFFERENCEARRAY(array1, array2)

Argument	Data Type	Description
`array1`	`ARRAY` of `POINT`, `LINESTRING`, or `POLYGON` values.	An array of geographic objects to be compared to the geographic objects in `array2` for any values that do not intersect. The data types in this array must match the data types in `array2` unless they are NULL.
`array2`	`ARRAY` of `POINT`, `LINESTRING`, or `POLYGON` values.	An array of geographic objects to be compared to the geographic objects in `array1` for any values that do not intersect. The data types in this array must match the data types in `array1` unless they are NULL.

Example

SQL

SELECT ST_MULTISYMDIFFERENCEARRAY(
    POINT[](ST_POINT(0, 0), ST_POINT(1, 1)),
    POINT[](ST_POINT(1, 1), ST_POINT(2, 2)));

Output: ['POINT(0.0 0.0)','POINT(2.0 2.0)']

ST_MULTIUNIONARRAY

When you specify two arrays of geospatial objects, this function returns one array that represents a union of all geographies in both arrays. All geographies in both arrays must be the same type. The function ignores NULL values in the arrays. The output geographies are the same type as the input geographies. Syntax

SQL

ST_MULTIUNIONARRAY(array1, array2)

Argument	Data Type	Description
`array1`	`ARRAY` of `POINT`, `LINESTRING`, or `POLYGON` values.	An array of geographic objects to be combined with `array2`. The data types in this array must match the data types in `array2` unless they are NULL.
`array2`	`ARRAY` of `POINT`, `LINESTRING`, or `POLYGON` values.	An array of geographic objects to be combined with `array1`. The data types in this array must match the data types in `array1` unless they are NULL.

Example

SQL

SELECT ST_MULTIUNIONARRAY(
    POINT[](ST_POINT(0, 0), ST_POINT(1, 1)),
    POINT[](ST_POINT(1, 1), ST_POINT(2, 2)));

Output: ['POINT(1.0 1.0)','POINT(2.0 2.0)','POINT(0.0 0.0)']

ST_NRINGS

Returns the number of rings of the specified POLYGON, including both interior and exterior rings. If you specify a geography object that is not a POLYGON, this function returns 0. Syntax

SQL

ST_NRINGS(geo)

Argument	Data Type	Description
`geo`	`POLYGON`	An object to be computed to return its total number of rings.

Example

SQL

SELECT ST_NRINGS(
        ST_POLYGON('POLYGON((1.0 2.0, 1.0 4.0, 1.0 5.0, 1.0 2.0))'));

Output: 1

ST_NUMINTERIORRINGS or ST_NUMINTERIORRING

Returns the number of interior rings of the specified POLYGON. If you specify a geography object that is not a POLYGON, this function returns 0. Syntax

SQL

ST_NUMINTERIORRINGS(polygon)

SQL

ST_NUMINTERIORRING(polygon)

Argument	Data Type	Description
`polygon`	`POLYGON`	An object to be computed to return its total number of interior rings.

Example

SQL

SELECT ST_NUMINTERIORRINGS(
        ST_POLYGON(
            ST_LINESTRING(ST_POINT [](ST_POINT(1, 2))),
            ST_LINESTRING [](ST_LINESTRING(ST_POINT [](ST_POINT(1, 2))))));

Output: 1

ST_POINTONSURFACE

Returns a POINT guaranteed to intersect the specified geospatial object. Syntax

SQL

ST_POINTONSURFACE(geo1)

Argument	Data Type	Description
`geo`	`POINT`, `LINESTRING`, or `POLYGON`	An object to be computed for an intersection `POINT` value.

Example

SQL

SELECT ST_POINTONSURFACE(ST_POLYGON('POLYGON((1 1, 2 1, 2 2, 1 1))'));

Output: POINT(1.0 1.0)

ST_PROJECT

Returns a POINT by projecting a distance and an azimuth value from the specified starting POINT value. The distance must be in meters, and azimuth must be in radians. Syntax

SQL

ST_PROJECT(point, distance, azimuth)

Argument	Data Type	Description
`point`	`POINT`	A starting point for locating a returned destination by using the `distance` and `azimuth` values.
`distance`	`DOUBLE`	A distance value in meters.
`azimuth`	`DOUBLE`	An azimuth value in radians.

Example

SQL

SELECT ST_PROJECT(ST_POINT(1, 3), 100, 100);

Output: POINT(0.99 3.00)

ST_REDUCEPRECISION

Returns a new geospatial object with all POINT values rounded to the specified decimal precision. Syntax

SQL

ST_REDUCEPRECISION(geo, precision)

Argument	Data Type	Description
`geo`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be rounded to the specified `precision` value.
`precision`	`DOUBLE`	A decimal value that defines the level of precision for returned values. For example, a precision of `0.01` returns values rounded to the hundredth decimal. Do not use digits other than `0` and `1` to represent the precision.

Example

SQL

SELECT ST_REDUCEPRECISION(ST_POINT('POINT(1.21 1.34)'), 0.1);

Output**:** POINT(1.2 1.3)

ST_REMOVEREPEATEDPOINTS

Returns a new geospatial object with no repeated POINT values. The returned type is the same as the specified type. Syntax

SQL

ST_REMOVEREPEATEDPOINTS(geo)

Argument	Data Type	Description
`geo`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object for the removal of any duplicate `POINT` values.

Example

SQL

SELECT ST_REMOVEREPEATEDPOINTS(ST_LINESTRING('LINESTRING(1 2, 1 2, 2 1, 1 0)'));

Output: LINESTRING(1.0 2.0, 2.0 1.0, 1.0 0.0)

ST_REVERSE

Returns a new geospatial object with the vertexes reversed. The returned type is the same as the specified type. Syntax

SQL

ST_REVERSE(geo)

Argument	Data Type	Description
`geo`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object for vertex reversal.

Example

SQL

SELECT ST_REVERSE(ST_LINESTRING('LINESTRING(1 2,1 10)'));

Output: LINESTRING(1.0 10.0, 1.0 2.0)

ST_SEGMENTIZE

Returns a geospatial object that the function modifies to have no segment longer than the specified max_segment_length in meters. Syntax

SQL

ST_SEGMENTIZE(geo, max_segment_length)

Argument	Data Type	Description
`geo`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object for segment modification.
`max_segment_length`	`DOUBLE`	The maximum length of a segment to modify the specified `geo` object. This value must be in METERS.

Example

SQL

SELECT ST_SEGMENTIZE(
        ST_POLYGON('POLYGON((1 1, 2 1, 2 2, 1 1))'),
        150000.0);

Output: POLYGON((1.0 1.0, 2.0 1.0, 2.0 2.0, 1.49 1.50, 1.0 1.0))

ST_SHORTESTLINE

Returns the shortest LINESTRING between two specified geospatial arguments. Syntax

SQL

ST_SHORTESTLINE(geo1, geo2, use_spheroid)

Argument	Data Type	Description
`geo1`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object for the determination of the shortest `LINESTRING` relative to `geo2`.
`geo2`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object for the determination of the shortest `LINESTRING` relative to `geo1`.
`use_spheroid`	`BOOLEAN`	Optional. If you set this argument to `TRUE`, this function uses a spheroid model instead of a spherical model. Defaults to `FALSE`.

Example

SQL

SELECT ST_SHORTESTLINE(
    ST_MAKELINE('LINESTRING(4 3, 10 3)'),
    ST_MAKELINE('LINESTRING(0 1, 0 3)'));

Output: LINESTRING(0.0 3.0, 4.0 3.0)

To use ST_SHORTESTLINE as a spatiotemporal operator to calculate the shortest line between two points at a concurrent time, see the ST_SHORTESTLINE function in the Spatiotemporal section.

ST_SIMPLIFY

Returns a simplified version of the specified geography, which is either a POINT or LINESTRING. The function simplifies the input geography by replacing nearly straight edges that contain multiple points with a single straight edge. The input geography does not change by more than the specified tolerance, which you specify as a DOUBLE in meters. Syntax

SQL

ST_SIMPLIFY(geo, tolerance)

Argument	Data Type	Description
`geo`	`POINT` or `LINESTRING`	A geospatial object to be simplified.
`tolerance`	`DOUBLE`	A value that represents the maximum number of meters to simplify in the specified geography.

Example

SQL

SELECT ST_SIMPLIFY(
    ST_LINESTRING('LINESTRING(0 0, 0.05 0, 0.1 0, 0.15 0, 2 0)'), 1);

Output: LINESTRING(0 0, 2 0)

ST_SIMPLIFYARRAY

Returns a POLYGON array that represents a simplified version of the specified geography, which is either a POINT, LINESTRING, or POLYGON. The function simplifies the input geography by replacing nearly straight edges that contain multiple points with a single straight edge. The input geography does not change by more than the specified tolerance, which you specify as a DOUBLE in meters. Syntax

SQL

ST_SIMPLIFYARRAY(geo, tolerance)

Argument	Data Type	Description
`geo`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be simplified.
`tolerance`	`DOUBLE`	A value that represents the maximum number of meters to simplify in the specified geography.

Example In this example, ST_SIMPLIFYARRAY evaluates a POLYGON with an outer ring of 13 points and an inner ring with four points. The example sets a tolerance argument of 10,000 meters, meaning that is the maximum distance that the function simplifies.

SQL

SELECT ST_SIMPLIFYARRAY(
        ST_POLYGON(
            'POLYGON((0 0, 1 0, 1 0.49, 1.01 0.49, 1.01 0.51, 1 0.51, 1 1, 0 1, 0 0.51, -0.01 0.51, -0.01 0.49, 0 0.49, 0 0), (-0.001 0.5, 1.001 0.5, 0.5 0.4, -0.001 0.5))'
            ),
        10000);

Output:

[POLYGON((0.0 0.5000000762285838, 1.0 0.500000076228584, 1.0 1.0, 0.0 1.0, 0.0 0.5000000762285838)), POLYGON((1.0 0.4998004375649452, 0.5 0.4, 0.0 0.4998004375649452, 0.0 0.0, 1.0 0.0, 1.0 0.4998004375649452))]

As shown in the output, the ST_SIMPLIFYARRAY function has reduced the size of the outer POLYGON from 13 points to 5. Note that the function actually increases the number of points in the inner ring, going from four to six. The increase is due to how close the inner ring is to the edges of the outer ring. Taken altogether, the function still reduced the total number of points in the POLYGON.

ST_SNAPTOGRID

Returns a new geography value with all POINT values rounded to the specified precision. This function is similar to ST_REDUCEPRECISION but it allows you to specify the precision for the x and y coordinates separately. Syntax

SQL

ST_SNAPTOGRID(geo1, precision_x, precision_y)

Argument	Data Type	Description
`geo`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be rounded to the specified precision values.
`precision_x`	`DOUBLE`	A decimal value representing the level of precision for the x value. For example, a precision of `0.01` returns values rounded to the hundredth decimal. Do not use digits other than `0` and `1` to represent the precision.
`precision_y`	`DOUBLE`	A decimal value representing the level of precision for the y value. For example, a precision of `0.1` returns values rounded to the tenth decimal. Do not use digits other than `0` and `1` to represent the precision.

Example

SQL

SELECT ST_SNAPTOGRID(
    ST_MAKEPOINT(1.1, 3.11), 1, 0.1);

Output: POINT(1.0 3.1)

ST_SYMDIFFERENCEARRAY

Returns a geographic array that contains the parts that are not common between two geographic objects, geo1 and geo2. Syntax

SQL

ST_SYMDIFFERENCEARRAY(geo1, geo2)

Argument	Data Type	Description
`geo1`	`POINT`, `LINESTRING`, or `POLYGON`	A geographic object to be compared to the geographic objects in `geo2`.
`geo2`	`POINT`, `LINESTRING`, or `POLYGON`	A geographic object to be compared to the geographic objects in `geo1`.

Example

SQL

SELECT ST_SYMDIFFERENCEARRAY(
    ST_LINESTRING('LINESTRING(1 1, 2 2)'),
    ST_LINESTRING('LINESTRING(1 1, 2 2, 3 3)'));

Output: [LINESTRING(2.0 2.0, 3.0 3.0)]

ST_UNIONARRAY

Performs a union of the input geography values to produce a geographic array. Syntax

SQL

ST_UNIONARRAY(geo1, geo2)

Argument	Data Type	Description
`geo1`	`POINT`, `LINESTRING`, or `POLYGON`	A geographic object to be unified with the geographic values in `geo2`.
`geo2`	`POINT`, `LINESTRING`, or `POLYGON`	A geographic object to be unified with the geographic values in `geo1`.

Example

SQL

SELECT ST_UNIONARRAY(
    ST_LINESTRING('LINESTRING(0 0, 1 1, 2 2)'),
    ST_LINESTRING('LINESTRING(1 1, 2 2, 3 3)'));

Output: ['LINESTRING(0.0 0.0, 1.0 1.0, 2.0 2.0, 3.0 3.0)'] Geospatial Data Types Spatial Measurement Spatial Relationships

OcientAIQ Unified Data Platform

Documentation Index

​ST_BOUNDINGDIAGONAL

​ST_BUFFER

​ST_CLOSESTPOINT

​ST_CONVEXHULL

​ST_DIFFERENCEARRAY

​ST_ENVELOPE

​ST_EXPAND

​ST_EXTERIORRING

​ST_FLIPCOORDINATES

​ST_FORCE2D

​ST_INTERIORRINGN

​ST_INTERSECTALL

​ST_INTERSECTIONARRAY

​ST_LONGESTLINE

​ST_MAKEENVELOPE

​ST_MINIMUMBOUNDINGCIRCLE

​ST_MULTIDIFFERENCEARRAY

​ST_MULTIINTERSECTIONARRAY

​ST_MULTISYMDIFFERENCEARRAY

​ST_MULTIUNIONARRAY

​ST_NRINGS

​ST_NUMINTERIORRINGS or ST_NUMINTERIORRING

​ST_POINTONSURFACE

​ST_PROJECT

​ST_REDUCEPRECISION

​ST_REMOVEREPEATEDPOINTS

​ST_REVERSE

​ST_SEGMENTIZE

​ST_SHORTESTLINE

​ST_SIMPLIFY

​ST_SIMPLIFYARRAY

​ST_SNAPTOGRID

​ST_SYMDIFFERENCEARRAY

​ST_UNIONARRAY

​Related Links

ST_BOUNDINGDIAGONAL

ST_BUFFER

ST_CLOSESTPOINT

ST_CONVEXHULL

ST_DIFFERENCEARRAY

ST_ENVELOPE

ST_EXPAND

ST_EXTERIORRING

ST_FLIPCOORDINATES

ST_FORCE2D

ST_INTERIORRINGN

ST_INTERSECTALL

ST_INTERSECTIONARRAY

ST_LONGESTLINE

ST_MAKEENVELOPE

ST_MINIMUMBOUNDINGCIRCLE

ST_MULTIDIFFERENCEARRAY

ST_MULTIINTERSECTIONARRAY

ST_MULTISYMDIFFERENCEARRAY

ST_MULTIUNIONARRAY

ST_NRINGS

ST_NUMINTERIORRINGS or ST_NUMINTERIORRING

ST_POINTONSURFACE

ST_PROJECT

ST_REDUCEPRECISION

ST_REMOVEREPEATEDPOINTS

ST_REVERSE

ST_SEGMENTIZE

ST_SHORTESTLINE

ST_SIMPLIFY

ST_SIMPLIFYARRAY

ST_SNAPTOGRID

ST_SYMDIFFERENCEARRAY

ST_UNIONARRAY

Related Links