Share via

st_addpoint function

Applies to: check marked yes Databricks Runtime 17.1 and above

Important

This feature is in Public Preview.

Adds a new point to the n-th position in the input linestring GEOGRAPHY or GEOMETRY.

Syntax

st_addpoint ( geo1Expr, geo2Expr[, indexExpr] )

Arguments

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

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 one plus the number of points in the linestring, the function returns ST_INVALID_INDEX_VALUE

Examples

-- We do not specify a position; the point is appended at the end (right) of the linestring.
> SELECT st_asewkt(st_addpoint(st_geomfromtext('LINESTRING(1 2,3 4)', 4326), st_geomfromtext('POINT(7 8)', 4326)));
  SRID=4326;LINESTRING(1 2,3 4,7 8)

-- A positive index indicates the position. We add the point at that position in the linestring.
> SELECT st_astext(st_addpoint(st_geomfromtext('LINESTRING(1 2,3 4)'), st_geomfromtext('POINT(7 8)'), 3))
  LINESTRING(1 2,3 4,7 8)

-- The position is specified as a negative index. The point is added at that position counting from the right.
-- The point is missing a Z coordinate. This is set to 0 when the point is added in the linestring.
> SELECT st_asewkt(st_addpoint(st_geogfromtext('LINESTRING ZM (1 2 3 4,5 6 7 8)'), st_geogfromtext('POINT M (0 9 99)'), -1))
  SRID=4326;LINESTRING ZM (1 2 3 4,5 6 7 8,0 9 0 99)