st_addpoint 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

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.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 if the value of geo1Expr is an empty linestring.
• The function returns ST_INVALID_ARGUMENT.EMPTY_POINT 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 plus one, the function returns ST_INVALID_ARGUMENT.INVALID_INDEX_VALUE.

Examples

SQL

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

Related functions

• st_pointn function
• st_removepoint function
• st_setpoint function