Spatial data formats

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

• Well-Known Text (WKT) format

• Well-Known Binary (WKB) format

WKT

WKT is defined based on EBNF (Extended Backus Naur Form). WKT can serve as a data format (referred to as WKT-Data in this text) or be used in GIS 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 uses the Point() function to generate the object. The second example converts a WKT representation 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 outer ring (a closed line) and zero or more inner rings (closed lines). 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 collection 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 to enclose the points in parentheses within a MultiPoint. 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 collection types.

GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))

WKB

WKB is developed based on the OpenGIS specification and supports 7 types (Point, Linestring, Polygon, Multipoint, Multilinestring, Multipolygon, and Geometrycollection) and their corresponding format definitions.

Point

The format definition of POINT(1 -1) is shown in the following table.

Linestring

The format definition of LINESTRING(1 -1, -1 1) is shown in the following table. Num points must be greater than or equal to 2.

Polygon

MultiPoint

MultilineString

MultiPolygon

GeometryCollection

Notice:

• Only GeometryCollection can be empty, indicating that no elements are stored. Other types cannot be empty.
• When LENGTH() is applied to a GIS 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 to be met:

• A Linestring must have at least 2 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 4 points in a ring (the minimum number of points for a polygon is 3, 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 to be met:

• 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 use the ST_IsValid() function to explicitly check the geometric validity of a geometry object.

GIS examples

Insertion examples

//Conversion functions and WKT are both 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

//Queries and converts to WKT output
SELECT ST_AsText(g) FROM geom;

//Queries and converts to WKB output
SELECT ST_AsBinary(g) FROM geom;