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 in GIS (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 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 consists 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 consists 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 legal to enclose the points in parentheses in 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 basic 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 in the table below.

Linestring

For example, the LINESTRING(1 -1, -1 1) format is defined in the table below. The Num points value 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 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 geometry validity

Syntax validity

Syntax validity requires the following conditions:

• 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 (the smallest polygon is a triangle, with the first and last points being the same).
• Except for GeometryCollection, other collection types cannot be empty.

Geometry validity

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

GIS examples

Insertion examples

//Convert the function and WKT into an SQL statement
INSERT INTO geom VALUES (ST_GeomFromText('POINT(1 1)'));

//Place the WKT 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 directly insert based on WKB.
INSERT INTO geom VALUES(ST_GeomFromWKB(X'0101000000000000000000F03F000000000000F03F'));

Query examples

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

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