Geometry processing functions operate or transform geometry objects, such as modifying shapes, calculating geometric relations, performing spatial queries, and conducting other spatial analysis.
OceanBase Database supports the _ST_MakeValid() function of this type.
_ST_MakeValid
In a geographic information system (GIS), spatial data often needs to meet certain validity criteria. For example, a valid polygon must be simple (not self-intersecting) and correctly encircled (with clockwise outer rings and counterclockwise inner rings). A polygon is invalid when it intersects with itself, has overlapping rings, or has an exposed hole.
The _ST_MakeValid() function fixes invalid geometries, such as polygons. _ST_MakeValid() supports projected coordinate systems (PCSs) only, and does not support geographic coordinate systems (GCSs).
_ST_MakeValid()accepts only input geometries that use a PCS. A PCS is a planar coordinate system that represents a location on Earth using two coordinates.A GCS is based on the Earth’s latitude and longitude system, which takes into account the curvature of the Earth.
_ST_MakeValid()does not support GCSs, so input geometries of the function cannot use coordinates based on latitude and longitude.
The syntax is as follows:
_ST_MakeValid(geometry input)
where:
geometry inputindicates the input parameter of the function, which represents the geometry object to be fixed. It can be a geometry object of any type, such as a Point, LineString, or Polygon object.
Here is an example:
obclient> select st_astext(_st_makevalid(st_geomfromtext('POLYGON((0 0,1 1,2 2,0 2,1 1,2 0,0 0))')));
In this example, st_geomfromtext('POLYGON((0 0,1 1,2 2,0 2,1 1,2 0,0 0))') converts a polygon in text format into a geometry object. The text string 'POLYGON((0 0,1 1,2 2,0 2,1 1,2 0,0 0))' represents a sequence of vertex coordinates of a polygon. The polygon is invalid because it has self-intersecting vertices.
Then, _ST_MakeValid(...) fixes the invalid polygon passed in to make it a valid one.
Finally, ST_AsText(...) converts the valid polygon back to its WKT representation.
The return result is as follows:
+-------------------------------------------------------------------------------------+
| st_astext(_st_makevalid(st_geomfromtext('POLYGON((0 0,1 1,2 2,0 2,1 1,2 0,0 0))'))) |
+-------------------------------------------------------------------------------------+
| MULTIPOLYGON(((1 1,2 2,0 2,1 1)),((1 1,0 0,2 0,1 1))) |
+-------------------------------------------------------------------------------------+
1 row in set