Spatial data formats

OceanBase Database supports two standard spatial data formats to represent geometric objects in a query:

• Well-known text (WKT)

• Well-known binary (WKB)

WKT

WKT is defined in the extended Backus–Naur form (EBNF). WKT can be used either as a data format (WKT data) or as a SRS definition in a GIS (referred to hereinafter as WKT-SRS).

Point

A point is non-comma-separated. Example:

POINT(15 20)

The following examples use the ST_X() function to get the X coordinate from a point object. The first example directly uses the Point() function to generate an object. The second example uses ST_GeomFromText() to convert a point into a WKT representation.

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 (,). Example:

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

Polygon

A polygon consists of at least one outer ring and zero or more inner ring. Example:

POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))

MultiPoint

A MultiPoint is a collection of points. It is similar to a line, but has different semantics. Multiple points can be connected to form a line, and can form a MultiPoint as a collection in dispersion. Example:

MULTIPOINT(0 0, 20 20, 60 60)

In the ST_MPointFromText() and ST_GeoFromText() functions, points in a MultiPoint can also be enclosed in parentheses. Example:

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 lines. Example:

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

MultiPolygon

A MultiPolygon is a collection of polygons. Example:

MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5)))

GeometryCollection

A GeometryCollection is a collection of multiple objects of various base types or collections of various base types.

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

WKB

WKB is a format defined based on the OpenGIS specification and supports seven object types, including Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection.

Point

The following table takes POINT(1 -1) as an example to describe the format.

Linestring

The following table takes LINESTRING(1 -1, -1 1) as an example to describe the format. Num points must be greater than or equal to 2.

Polygon

MultiPoint

MultiLineString

MultiPolygon

GeometryCollection

Note:

• Only GeometryCollection can be empty, which means that 0 elements are stored. All other types cannot be empty.
• LENGTH() for a GIS object returns the binary representation of the length.

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

Syntactic and geometric validity

Syntactic validity

The spatial data must meet the following syntactic validity requirements:

• A LineString has at least 2 points.
• A Polygon has at least one ring.
• A Polygon starts and ends at the same point to form a closed ring.
• A polygon has at least 4 points for its outer ring. A polygon with 3 points is a triangle, with the first and the last points being the same.
• Only a GeometryCollection can be empty, which means that 0 elements are stored. All other types cannot be empty.

Geometric validity

The spatial data must meet the following geometric validity requirements:

• A Polygon cannot intersect with itself.
• The outer ring of a Polygon is outside its inner ring.
• The polygons in a MultiPolygon do not overlap with each other.

You can use the ST_IsValid() function explicitly check the geometric validity of geometric objects.

GIS examples

Insert

// Place both the conversion function and WKT in the SQL statement.
INSERT INTO geom VALUES (ST_GeomFromText('POINT(1 1)'));

// Place WKT in the arguments.
SET @g = 'POINT(1 1)';
INSERT INTO geom VALUES (ST_GeomFromText(@g));

// Place the conversion expression directly in the arguments.
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));

// It can also be inserted directly based on WKB.
INSERT INTO geom VALUES(ST_GeomFromWKB(X'0101000000000000000000F03F000000000000F03F'));

Query

// Perform a query and convert the result into a WKT output.
SELECT ST_AsText(g) FROM geom;

// Perform a query and convert the result into a WKB output.
SELECT ST_AsBinary(g) FROM geom;