st_pointonsurface function

Applies to: check marked yes Databricks Runtime 18.3 and above

Important

This feature is in Public Preview.

Returns a point that is guaranteed to lie on or inside the input GEOMETRY value.

Syntax

st_pointonsurface ( geoExpr )

Arguments

  • geoExpr: A GEOMETRY value.

Returns

A 2D point GEOMETRY value that is guaranteed to lie on or inside the input GEOMETRY value. More precisely:

  • If the input GEOMETRY value is empty, the 2D empty point is returned.
  • If the input GEOMETRY value is a non-empty point, the 2D projection of the point is returned.
  • If the input GEOMETRY value is a non-empty linestring, the median vertex is returned.
  • If the input GEOMETRY value is a non-empty polygon, a point in the interior of the polygon is returned when possible; otherwise a point on its boundary.
  • If the input GEOMETRY value is a multipoint, the non-empty point closest to the bounding box center is returned.
  • If the input GEOMETRY value is a multilinestring, a point on the surface of the linestring with the largest length is returned.
  • If the input GEOMETRY value is a multipolygon, a point on the surface of the polygon with the largest area is returned.
  • If the input GEOMETRY value is a geometry collection, a point on the surface of one of the maximum-dimensional elements of the collection is returned.

The SRID value of the output GEOMETRY value is the same as that of the input value.

Examples

-- Example taking a 2D polygon.
> SELECT st_asewkt(st_pointonsurface(st_geomfromtext('POLYGON((0 0,10 0,10 10,0 10,0 0))')));
  POINT(5 5)

-- Example taking a 2D polygon with a hole: the result lies in the donut region.
> SELECT st_asewkt(st_pointonsurface(st_geomfromtext('POLYGON((0 0,30 0,30 30,0 30,0 0),(5 5,25 5,25 25,5 25,5 5))')));
  POINT(3.75 3.75)

-- Example taking a 3DZ linestring: the median vertex is returned and the Z coordinate is dropped.
> SELECT st_asewkt(st_pointonsurface(st_geomfromtext('LINESTRING Z (1 2 -1,3 4 -2,5 6 -3)')));
  POINT(3 4)

-- Example taking a multipolygon: a point on the polygon with the largest area is returned.
> SELECT st_asewkt(st_pointonsurface(st_geomfromtext('MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((100 100,101 100,101 101,100 101,100 100)))')));
  POINT(5 5)

-- Example showing that the SRID is preserved.
> SELECT st_asewkt(st_pointonsurface(st_geomfromtext('POLYGON((0 0,10 0,10 10,0 10,0 0))', 3857)));
  SRID=3857;POINT(5 5)

-- Example taking a NULL input.
> SELECT st_pointonsurface(NULL);
  NULL
  • Last updated on