st_setpoint function

Applies to: Databricks SQL Databricks Runtime 17.1 and above

Preview

This feature is in Public Preview.

note

This feature is not available on Databricks SQL Classic warehouses. To learn more about Databricks SQL warehouses, see SQL warehouse types

Sets the n-th point of the input linestring GEOGRAPHY or GEOMETRY.

Syntax

st_setpoint ( geo1Expr, indexExpr, geo2Expr )

Arguments

• geo1Expr: A GEOGRAPHY or GEOMETRY value representing a linestring.
• indexExpr: An INTEGER value indicating the 1-based position in the linestring where the new point should be set.
• geo2Expr: A GEOGRAPHY or GEOMETRY value representing a point.

Returns

A value of type GEOGRAPHY if both geo1Expr and geo2Expr are of type GEOGRAPHY, or a value of type GEOMETRY if both geo1Expr and geo2Expr are of type GEOMETRY. If indexExpr is positive, the returned GEOGRAPHY or GEOMETRY value is a new linestring whose indexExpr-th point (counted from the left) is set to be geo2Expr. If indexExpr is negative, the 1-based position of the linestring where the point is added is measured from the right.

• The function returns NULL if any of the inputs is NULL.
• The SRID value of the output linestring is equal to the common SRID value of the input GEOGRAPHY or GEOMETRY values.
• The dimension of the output GEOGRAPHY or GEOMETRY linestring is the same as that of geo1Expr. If geo2Expr contains coordinates whose dimension does not exist in geo1Expr the corresponding coordinates are set to 0.

Error conditions

• If geo1Expr is of type GEOGRAPHY and geo2Expr is of type GEOMETRY, or the other way around, the function returns DATATYPE_MISMATCH.
• If the SRID values of geo1Expr and geo2Expr differ, the function returns ST_DIFFERENT_SRID_VALUES.
• The function returns ST_INVALID_ARGUMENT.INVALID_TYPE in any of the following cases:
  • The value of geo1Expr is not a linestring.
  • The value of geo2Expr is not a point.
• The function returns ST_INVALID_ARGUMENT.EMPTY_LINESTRING in if the value of geo1Expr is an empty linestring.
• The function returns ST_INVALID_ARGUMENT.EMPTY_POINT in if the value of geo2Expr is an empty point.
• If the absolute value of the value of indexExpr is 0 or larger than the number of points in the linestring, the function returns ST_INVALID_ARGUMENT.INVALID_INDEX_VALUE.

Examples

SQL

-- We set the first point of the linestring counting from the left.
> SELECT st_astext(st_setpoint(st_geomfromtext('LINESTRING(1 2,3 4)'), 1, st_geomfromtext('POINT(7 8)')));
  LINESTRING(7 8,3 4)

-- We set the first point of the linestring counting from the right.
-- The point is missing a Z coordinate. This is set to 0 when we update the linestring.
> SELECT st_asewkt(st_setpoint(st_geogfromtext('LINESTRING ZM (1 2 3 4,5 6 7 8)'), -1, st_geogfromtext('POINT M (0 9 99)')));
  SRID=4326;LINESTRING ZM (1 2 3 4,0 9 0 99)

Related functions

• st_addpoint function
• st_pointn function
• st_removepoint function