Spatial data formats

OceanBase Database supports two standard spatial data formats for representing geometric objects in queries:

• Well-Known Text (WKT)

• Well-Known Binary (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 text) or for defining SRS (referred to as WKT-SRS in this text).

Point

Points are not separated by commas. The 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 the object using the Point() function. The second example converts the WKT representation of a Point to a Point object 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 is composed of multiple points, separated by commas. The format is as follows:

LINESTRING(0 0, 10 10, 20 25, 50 60)

Polygon

A polygon consists of at least one exterior ring (a closed line) and any number of interior rings (closed lines), which can be zero. The 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, similar to a line but with different semantics. A line is formed by connecting multiple points, while a MultiPoint is a set of discrete points. The format is as follows:

MULTIPOINT(0 0, 20 20, 60 60)

In the functions ST_MPointFromText() and ST_GeoFromText(), it is also valid for the points in a MultiPoint to be enclosed in parentheses. The format is as follows:

ST_MPointFromText('MULTIPOINT (1 1, 2 2, 3 3)')
ST_MPointFromText('MULTIPOINT ((1 1), (2 2), (3 3))')

MultiLineString

A MultiLineString is a collection of multiple lines. The format is as follows:

MULTILINESTRING((10 10, 20 20), (15 15, 30 15))

MultiPolygon

A MultiPolygon is a collection of multiple polygons. The 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 various base types and collections.

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) and their corresponding format definitions.

Point

For example, the POINT(1 -1) format is defined as follows:

Linestring

For example, the LINESTRING(1 -1, -1 1) format is defined as follows. Num points must be greater than or equal to 2.

Polygon

MultiPoint

MultilineString

MultiPolygon

GeometryCollection

Note:

• Only GeometryCollection can be empty, indicating that it stores zero elements. 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 validity requires the following conditions:

• A Linestring must have at least two points.
• A Polygon must have at least one ring.
• A Polygon must be closed (the first and last points are the same).
• A Polygon must have at least four points (the smallest polygon is a triangle, with the first and last points being the same).
• Except for GeometryCollection, other collection types cannot be empty.

Geometric validity

Geometric validity requires the following conditions:

• A Polygon cannot intersect with itself.
• The outer ring of a Polygon must be outside the inner ring.
• Multipolygons cannot contain overlapping polygons.

You can explicitly check the geometric validity of a geometric object by using the ST_IsValid() function.

Spatial data examples

Insertion examples

//Both conversion functions and WKT are included in 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));

//Conversion expressions are directly placed in the parameter
SET @g = ST_GeomFromText('POINT(1 1)');
INSERT INTO geom VALUES (@g);

//A unified conversion function is used
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));

//Type-based conversion functions are used
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));

//WKB can also be used directly for insertion
INSERT INTO geom VALUES(ST_GeomFromWKB(X'0101000000000000000000F03F000000000000F03F'));

Query examples

//Query and convert to WKT output
SELECT ST_AsText(g) FROM geom;

//Query and convert to WKB output
SELECT ST_AsBinary(g) FROM geom;