Spatial processing functions are used to perform various operations and transformations on spatial geometric objects, including shape modification, geometric relationship calculation, spatial queries, and other types of spatial analysis.
Currently, OceanBase Database supports the _ST_MakeValid() function.
_ST_MakeValid
In a geographic information system (GIS), spatial data often needs to meet certain validity standards. A valid polygon should be simple (non-intersecting) and correctly oriented (outer ring clockwise, inner ring counterclockwise), while an invalid polygon may include geometric errors such as self-intersections, overlapping rings, or exposed holes.
The _ST_MakeValid function is used to fix invalid geometric shapes (such as polygons) to make them valid. However, this function only supports projected coordinate systems and does not support geographic coordinate systems.
Supported: Projected coordinate systems. This function expects the input geometry to be in a projected coordinate system, not a geographic coordinate system. A projected coordinate system is a planar coordinate system where positions on the ground are represented by two coordinates.
Not supported: Geographic coordinate systems. Geographic coordinate systems are based on the Earth's latitude and longitude system, which takes into account the Earth's curvature. The
_ST_MakeValidfunction does not support geographic coordinate systems, and the input geometry cannot be based on latitude and longitude coordinates.
Syntax:
_ST_MakeValid(geometry input)
Parameter description:
geometry input: This is the input parameter of the function, representing the geometry object to be fixed. It can be any type of geometry, such as a point (Point), line (LineString), or polygon (Polygon).
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 the text-formatted polygon 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 for a polygon, but this polygon is invalid because it has self-intersecting vertices.
Then, the _st_makevalid(...) function fixes the invalid polygon geometry to make it valid.
Finally, st_astext(...) converts the fixed geometry object back to its Well-Known Text (WKT) format.
The 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