SQL Reference
Geospatial Functions

Linestring Functions

 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


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


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


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


Output: POINT(1,4)

ST_LINEINTERPOLATEPOINT

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

Syntax

SQL


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


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


Argument

Data Type

Description

line

LINESTRING

A LINESTRING used to compute a fraction based off 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


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


Argument

Data Type

Description

line

LINESTRING

A LINESTRING used to compute a substring line, based off 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 end point 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 end point.

SQL


Output: LINESTRING(1.50 1.50, 2.0 1.0)

ST_POINTN

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

Syntax

SQL


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 backwards from the end of the linestring. In other words, an index value of -1 returns that last point value.

Example

SQL


Output: ST_POINT(1,4)

ST_REMOVEPOINT

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

Syntax

SQL


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


Output: LINESTRING(1.0 2.0, 1.0 4.0)

ST_SETPOINT

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

Syntax

SQL


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 backwards 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 that is located at the -2 index location, meaning it is the second from the last in the sequence.

SQL


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


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


Output: ST_POINT(1,2)

Related Links