Linestring Functions - Ocient Documentation

LINESTRING functions can perform alterations or access descriptive information on LINESTRING objects.

ST_ADDPOINT

Adds a POINT to the given LINESTRING at the specified 0-indexed location. Syntax

SQL

ST_ADDPOINT(geo_linestring, geo_point_to_add [, location ] )

Argument	Data Type	Description
`geo_linestring`	`LINESTRING`	A linestring to be altered by adding the `geo_point_to_add` at a specified location. The function throws an error if `geo_linestring` is any data type except for `LINESTRING` or NULL.
`geo_point_to_add`	`POINT`	A point value to be added to the `geo_linestring`.
`location`	`INTEGER`	Optional. Represents a numeric 0-indexed location in the `geo_linestring` where `geo_point_to_add` is appended. If you do not specify this value, the function defaults to appending the `geo_point_to_add` argument to the end of `geo_linestring`. A location value of -1 also appends to the end. The function throws an error if the `location` argument is outside the bounds of the `geo_linestring` or if it is a value of less than -1.

Example

SQL

SELECT ST_ADDPOINT(
        ST_LINESTRING('LINESTRING(1 2, 1 3)'),
        ST_POINT(1, 4),
        1);

Output: LINESTRING(1.000000 2.000000, 1.000000 4.000000, 1.000000 3.000000)

ST_ENDPOINT

Returns the endpoint of a specified LINESTRING. The returned value is a POINT. Syntax

SQL

ST_ENDPOINT(geo)

Argument	Data Type	Description
`geo`	`LINESTRING`	A line to be computed to return its endpoint. If this value is a `POLYGON`, `POINT`, or is empty, then the function returns NULL. If this value is a non-geospatial type, the function throws an error.

Example

SQL

SELECT ST_ENDPOINT(ST_LINESTRING('LINESTRING(1 2, 1 3, 1 4)'));

Output: POINT(1,4)

ST_LINEINTERPOLATEPOINT

Returns a POINT along a LINESTRING based on a specified fraction of its total length. Syntax

SQL

ST_LINEINTERPOLATEPOINT(line, fraction)

Argument	Data Type	Description
`line`	`LINESTRING`	A line used to compute an interpolated point.
`fraction`	`DOUBLE`	A decimal value between `0` and `1` that represents a fraction of the total length of the `line`. The function returns a point at this fraction of the length. The function throws an error if this value is not between `0` and `1`.

Example In this example, the function computes a point halfway (0.5) along the specified line.

SQL

SELECT ST_LINEINTERPOLATEPOINT(ST_LINESTRING('LINESTRING(1 2, 2 1)'), 0.5);

Output: POINT(1.5 1.5)

ST_LINELOCATEPOINT

Similar to ST_LINEINTERPOLATEPOINT, this function computes a fraction based on where a specified POINT is located along the length of a specified LINESTRING. Syntax

SQL

ST_LINELOCATEPOINT(line, point)

Argument	Data Type	Description
`line`	`LINESTRING`	A `LINESTRING` used to compute a fraction based on the interpolated `point` value.
`point`	`POINT`	A `POINT` used to calculate a fraction based on where it is located along the `line` value. If the function cannot interpolate this value from the `line`, the function returns `0.0`.

Example In this example, the function computes a fraction that represents where the point (1.5, 1.5) lies along the line.

SQL

SELECT ST_LINELOCATEPOINT(
        ST_LINESTRING('LINESTRING(1 2, 2 1)'),
        ST_POINT(1.5, 1.5));

Output: 0.5

ST_LINESUBSTRING

Returns a LINESTRING that is a substring of a specified line that starts and ends at the specified fractions of its total length. Syntax

SQL

ST_LINESUBSTRING(line, start_fraction, end_fraction)

Argument	Data Type	Description
`line`	`LINESTRING`	A `LINESTRING` used to compute a substring line based on the `start_fraction` and `end_fraction` values.
`start_fraction`	`DOUBLE`	A decimal value between `0` and `1` that represents a fractional point of the total length of the `line`. The function uses this fractional point as the start point for the substring. The function throws an error if the `start_fraction` is greater than the `end_fraction`
`end_fraction`	`DOUBLE`	A decimal value between `0` and `1` that represents a fractional point of the total length of `line`. The function uses this fractional point as the endpoint for the substring.

Example In this example, the function generates a subsection of the provided linestring, spanning from the start_fraction value (0.5) that represents the halfway point of the original linestring, to the end_fraction value (1.0) that represents the endpoint.

SQL

SELECT ST_LINESUBSTRING(ST_LINESTRING('LINESTRING(1 2, 2 1)'), 0.5, 1.0);

Output: LINESTRING(1.50 1.50, 2.0 1.0)

ST_POINTN

Returns the POINT value at a specified index of the specified LINESTRING. If out of bounds, the function returns NULL. Syntax

SQL

ST_POINTN(geo, index)

Argument	Data Type	Description
`geo`	`LINESTRING`	A `LINESTRING` that is used to return one of its point values. If you specify a `POINT` or `POLYGON` value, this function returns NULL.
`index`	`INTEGER`	An index location of one of the point values in the `geo` value. This is one-based, meaning the first index location starts at `1`. Negative values count backward from the end of the linestring. In other words, an index value of `-1` returns the last point value.

Example

SQL

SELECT ST_POINTN(
        ST_LINESTRING(
            ST_POINT [](ST_POINT(1, 2), ST_POINT(1, 3), ST_POINT(1, 4))),
        3);

Output: ST_POINT(1,4)

ST_REMOVEPOINT

Removes a POINT value at a specified index from the specified line. Syntax

SQL

ST_REMOVEPOINT(geo, index)

Argument	Data Type	Description
`geo`	`LINESTRING`	A `LINESTRING` that is to be altered by removing one of its point values.
`index`	`INTEGER`	An index of one of the point values in the `geo` value. This is zero-based, meaning the first index location starts at `0`, and the last index is `length(geo)-1`.

Example

SQL

SELECT ST_REMOVEPOINT(ST_LINESTRING('LINESTRING(1 2, 1 3, 1 4)'), 1);

Output: LINESTRING(1.0 2.0, 1.0 4.0)

ST_SETPOINT

Replaces a POINT value in a specified LINESTRING at a specified index. The function returns the altered LINESTRING with the replaced point. Syntax

SQL

ST_SETPOINT(geo, index, geo_point_to_replace)

Argument	Data Type	Description
`geo`	`LINESTRING`	A `LINESTRING` that is to be altered with a new point value.
`index`	`INTEGER`	An index location of one of the point values in the `geo` linestring. This is zero-based, meaning the first index location starts at `0`. Negative values count backward from the end of the `geo` value. In other words, an index value of `-1` returns the last point value.
`geo_point_to_replace`	`POINT`	A `POINT` value to replace the value at the specified `index` location. If this value is not a `POINT` type or NULL, the function throws an error.

Example In this example, the function replaces the point value located at the -2 index location, meaning it is the second from the last in the sequence.

SQL

SELECT ST_SETPOINT(
        ST_LINESTRING('LINESTRING(1 2, 1 3, 1 4)'),
        -2,
        ST_POINT(1, 5));

Output: LINESTRING(1.000000 2.000000, 1.000000 5.000000, 1.000000 4.000000)

ST_STARTPOINT

Returns the starting POINT value of the line. If this value is empty, the function returns NULL. If you specify a POLYGON, POINT, or NULL, then the function returns NULL. If you specify a non-geography value, the function throws an error. Syntax

SQL

ST_STARTPOINT(geo)

Argument	Data Type	Description
`geo`	`LINESTRING`	A line to be computed to return its starting point. If this value is a `POLYGON`, `POINT`, or is empty, then the function returns NULL. If this value is a non-geospatial type, the function throws an error.

Example

SQL

SELECT ST_STARTPOINT(ST_LINESTRING('LINESTRING(1 2, 1 3, 1 4)'));

Output: ST_POINT(1,2) Geospatial Data Types Linestring Constructors

​ST_ADDPOINT

​ST_ENDPOINT

​ST_LINEINTERPOLATEPOINT

​ST_LINELOCATEPOINT

​ST_LINESUBSTRING

​ST_POINTN

​ST_REMOVEPOINT

​ST_SETPOINT

​ST_STARTPOINT

​Related Links

ST_ADDPOINT

ST_ENDPOINT

ST_LINEINTERPOLATEPOINT

ST_LINELOCATEPOINT

ST_LINESUBSTRING

ST_POINTN

ST_REMOVEPOINT

ST_SETPOINT

ST_STARTPOINT

Related Links