OceanBase AI Database supports two standard spatial data formats for representing geometric objects in queries:
Well-Know Text Format(WKT)
Well-Know Binary Format(WKB)
WKT
WKT is defined based on EBNF (Extended Backus-Naur Form). WKT can be used as a data format (referred to as WKT-Data in this topic) or for defining SRS (referred to as WKT-SRS in this topic).
Point
Points are not separated by commas. A sample format is as follows:
POINT(15 20)
The following example uses ST_X() to extract the X coordinate from a Point object. The first example directly generates a Point object using the Point() function. The second example converts a WKT representation into a Point using ST_GeomFromText().
obclient> SELECT ST_X(Point(15, 20));
+---------------------+
| ST_X(Point(15, 20)) |
+---------------------+
| 15 |
+---------------------+
1 row in set
obclient> SELECT ST_X(ST_GeomFromText('POINT(15 20)'));
+---------------------------------------+
| ST_X(ST_GeomFromText('POINT(15 20)')) |
+---------------------------------------+
| 15 |
+---------------------------------------+
1 row in set
Line
A line consists of multiple points, separated by commas. A sample format is as follows:
LINESTRING(0 0, 10 10, 20 25, 50 60)
Polygon
A polygon consists of at least one outer ring (a closed line) and several inner rings (closed lines), which can be zero. A sample format is as follows:
POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))
MultiPoint
A MultiPoint consists of multiple points and is similar to a line, but their semantics are different. Multiple connected points form a line, while multiple discrete points form a MultiPoint. A sample format is as follows:
MULTIPOINT(0 0, 20 20, 60 60)
In the ST_MPointFromText() and ST_GeoFromText() functions, it is also valid to enclose points within a MultiPoint object in parentheses. A sample format is as follows:
ST_MPointFromText('MULTIPOINT (1 1, 2 2, 3 3)')
ST_MPointFromText('MULTIPOINT ((1 1), (2 2), (3 3))')
MultiLineString
MultiLineString is a collection of multiple lines. A sample format is as follows:
MULTILINESTRING((10 10, 20 20), (15 15, 30 15))
MultiPolygon
A MultiPolygon is a collection of multiple polygons. A sample format is as follows:
MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5)))
GeometryCollection
A GeometryCollection can be a collection of multiple primitive types and collection types.
GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))
WKB
WKB is based on the OpenGIS specification and supports seven types (Point, Linestring, Polygon, Multipoint, Multilinestring, Multipolygon, and Geometrycollection) along with their corresponding format definitions.
Point
Taking POINT(1 -1) as an example, the format is defined in the following table.
Component name |
Specifications |
Type |
Valid values |
|---|---|---|---|
| Byte order | 1 byte | unsigned int | 01 |
| WKB type | 4 bytes | unsigned int | 01000000 |
| X coordinate | 8 bytes | double-precision | 000000000000F03F |
| Y coordinate | 8 bytes | double-precision | 000000000000F0BF |
Linestring
The following table describes the format of LINESTRING(1 -1, -1 1). Num points must be greater than or equal to 2.
Component name |
Specifications |
Type |
Valid values |
|---|---|---|---|
| Byte order | 1 byte | unsigned int | 01 |
| WKB type | 4 bytes | unsigned int | 02000000 |
| Num points | 4 bytes | unsigned int | 02000000 |
| X coordinate | 8 bytes | double-precision | 000000000000F03F |
| Y coordinate | 8 bytes | double-precision | 000000000000F0BF |
| X coordinate | 8 bytes | double-precision | 000000000000F0BF |
| Y coordinate | 8 bytes | double-precision | 000000000000F03F |
Polygon
Component name |
Specifications |
Type |
Valid values |
|---|---|---|---|
| Byte order | 1 byte | unsigned int | 01 |
| WKB type | 4 bytes | unsigned int | 03000000 |
| Num rings | 4 bytes | unsigned int | Greater than or equal to 1 |
| repeat ring | - | - | - |
MultiPoint
Component name |
Specifications |
Type |
Valid values |
|---|---|---|---|
| Byte order | 1 byte | unsigned int | 01 |
| WKB type | 4 bytes | unsigned int | 04000000 |
| Num points | 4 bytes | unsigned int | Require Num points >= 1 |
| repeat POINT | - | - | - |
MultilineString
Component name |
Specifications |
Type |
Valid values |
|---|---|---|---|
| Byte order | 1 byte | unsigned int | 01 |
| WKB type | 4 bytes | unsigned int | 05000000 |
| Num linestrings | 4 bytes | unsigned int | Greater than or equal to 1 |
| repeat LINESTRING | - | - | - |
MultiPolygon
Component name |
Specifications |
Type |
Valid values |
|---|---|---|---|
| Byte order | 1 byte | unsigned int | 01 |
| WKB type | 4 bytes | unsigned int | 06000000 |
| Num polygons | 4 bytes | unsigned int | Greater than or equal to 1 |
| repeat POLYGON | - | - | - |
GeometryCollection
Component name |
Specifications |
Type |
Valid values |
|---|---|---|---|
| Byte order | 1 byte | unsigned int | 01 |
| WKB type | 4 bytes | unsigned int | 07000000 |
| Num wkbs | 4 bytes | unsigned int | - |
| repeat WKB | - | - | - |
Note
- Only the geometrycollection type can be empty, indicating that no elements are stored. Other types cannot be empty.
- When
LENGTH()is applied to a spatial object, it returns the length of the stored binary data.
obclient [test]> SET @g = ST_GeomFromText('POINT(1 -1)');
Query OK, 0 rows affected
obclient [test]> SELECT LENGTH(@g);
+------------+
| LENGTH(@g) |
+------------+
| 25 |
+------------+
1 row in set
obclient [test]> SELECT HEX(@g);
+----------------------------------------------------+
| HEX(@g) |
+----------------------------------------------------+
| 000000000101000000000000000000F03F000000000000F0BF |
+----------------------------------------------------+
1 row in set
Syntax and Geometric Validity
Syntax validity
Syntax correctness requires the following conditions to be met:
- A linestring must have at least two points.
- A polygon must have at least one ring.
- A polygon must be closed (the first two points must be the same).
- A polygon must have at least four vertices. The smallest polygon is a triangle, where the first and last vertices are the same.
- All collection types except for GeometryCollection cannot be empty.
Geometric validity
A geometric figure is valid if it meets the following conditions:
- A polygon cannot intersect itself.
- The outer ring of the polygon must be outside the inner ring.
- No overlapping polygons are allowed in the Multipolygons field.
You can use the ST_IsValid() function to explicitly check the geometric validity of a geometry object.
Sample spatial data
Insert example
// Both the conversion function and the WKT are placed within the SQL statement
INSERT INTO geom VALUES (ST_GeomFromText('POINT(1 1)'));
// WKT is placed in the parameter
SET @g = 'POINT(1 1)';
INSERT INTO geom VALUES (ST_GeomFromText(@g));
//Place the conversion expression directly in the parameter
SET @g = ST_GeomFromText('POINT(1 1)');
INSERT INTO geom VALUES (@g);
//Use a unified conversion function
SET @g = 'LINESTRING(0 0,1 1,2 2)';
INSERT INTO geom VALUES (ST_GeomFromText(@g));
SET @g = 'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';
INSERT INTO geom VALUES (ST_GeomFromText(@g));
SET @g ='GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))';
INSERT INTO geom VALUES (ST_GeomFromText(@g));
//Use a type-based conversion function
SET @g = 'POINT(1 1)';
INSERT INTO geom VALUES (ST_PointFromText(@g));
SET @g = 'LINESTRING(0 0,1 1,2 2)';
INSERT INTO geom VALUES (ST_LineStringFromText(@g));
SET @g = 'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';
INSERT INTO geom VALUES (ST_PolygonFromText(@g));
SET @g =
'GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))';
INSERT INTO geom VALUES (ST_GeomCollFromText(@g));
// You can also insert data directly based on WKB.
INSERT INTO geom VALUES(ST_GeomFromWKB(X'0101000000000000000000F03F000000000000F03F'));
Sample query
//Query and convert to WKT format for output
SELECT ST_AsText(g) FROM geom;
//Query and convert to WKB for output
SELECT ST_AsBinary(g) FROM geom;