Spatial operation functions are used to perform various operations and transformations on spatial geometric objects, including shape modification, geometric relationship calculation, spatial query, and other types of spatial analysis.
The OceanBase AI Database currently supports the geometric processing function _ST_MakeValid().
_ST_MakeValid
In Geographic Information Systems (GIS), spatial data often needs to meet certain validity criteria. A valid polygon should be simple (non-self-intersecting) and correctly enclosed (with the outer ring clockwise and the inner ring counterclockwise), while an invalid polygon may contain geometric errors such as self-intersection, overlapping rings, or exposed holes.
The _ST_MakeValid function is used to repair invalid geometric shapes (such as polygons) into valid ones. It only supports the projected coordinate system and does not support the geographic coordinate system.
Projected coordinate system: The function expects the input geometric object to be in a projected coordinate system, not a geographic coordinate system. A projected coordinate system is a planar coordinate system in which positions on the ground are represented by two coordinates.
Geographic coordinate system: A geographic coordinate system is based on the Earth's latitude and longitude and takes into account the Earth's curvature. The
_ST_MakeValidfunction does not support geographic coordinate systems; therefore, the input geometric objects cannot be latitude/longitude coordinates.
The syntax is as follows:
_ST_MakeValid(geometry input)
Parameter description:
geometry input: The input parameter of the function, representing the geometry object to be repaired. It can be any type of geometric figure, such as a point (Point), line (LineString), polygon (Polygon), etc.
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))') first converts the text-based polygon to a geometric 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 contains self-intersecting vertices.
Then, the _st_makevalid(...) function fixes the invalid polygon geometry object passed in to make it a valid polygon.
Finally, st_astext(...) converts the repaired geometric object back into its Well-Known Text (WKT) format.
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