Share via

st_setpoint function

Applies to: check marked yes Databricks Runtime 17.1 and above

Important

This feature is in Public Preview.

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 in any of the following cases:
    • The value of geo1Expr is not a linestring or is an empty linestring.
    • The value of geo2Expr is not a point.
    • 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_INDEX_VALUE

Examples

-- 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)