Spatial data formats

OceanBase Database supports two standard spatial data formats for representing geometric 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 be used as a data format (referred to as WKT-Data in this document) or for defining the SRS in GIS (referred to as WKT-SRS in this document).

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 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 is composed 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 valid 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 base 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) and their corresponding format definitions.

Point

For example, POINT(1 -1), the format definition is shown in the following table.

Linestring

For example, LINESTRING(1 -1, -1 1), the format definition is shown in the following table. 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 used on 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 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 in a ring (the minimum 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 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

Insert examples

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

//The WKT is provided as a parameter.
SET @g = 'POINT(1 1)';
INSERT INTO geom VALUES (ST_GeomFromText(@g));

//The conversion expression is directly provided as a 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));

//A type-specific conversion function is 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));

//You can also directly insert data based on WKB.
INSERT INTO geom VALUES(ST_GeomFromWKB(X'0101000000000000000000F03F000000000000F03F'));

Query examples

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

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