Spatial data formats

seekdb 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 Extended Backus-Naur Form (EBNF). WKT can be used both as a data format (referred to as WKT-Data in this document) and for defining spatial reference systems (SRS) in Geographic Information System (GIS) (referred to as WKT-SRS in this document).

Point

A point does not use commas as separators. Example format:

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 uses the WKT representation converted to point through 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. Example format:

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

Polygon

A polygon consists of at least one exterior ring (closed line) and any number (can be 0) of interior rings (closed lines). Example format:

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. Multiple connected points form a line, while discrete multiple points form a MultiPoint. Example format:

MULTIPOINT(0 0, 20 20, 60 60)

In the functions ST_MPointFromText() and ST_GeoFromText(), it is also valid to enclose points in a MultiPoint with parentheses. Example format:

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

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

MultiPolygon

A MultiPolygon is a collection of multiple polygons. Example format:

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 multiple basic 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 seven types (Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and Geometrycollection) with corresponding format definitions.

Point

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

Component	Specification	Type	Value
Byte order	1 byte	unsigned int	01
WKB type	4 bytes	unsigned int	01000000
X coordinate	8 bytes	double-precision	000000000000F03F
Y coordinate	8 bytes	double-precision	000000000000F0BF

Linestring

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

Component	Specification	Type	Value
Byte order	1 byte	unsigned int	01
WKB type	4 bytes	unsigned int	02000000
Num points	4 bytes	unsigned int	02000000
X coordinate	8 bytes	double-precision	000000000000F03F
Y coordinate	8 bytes	double-precision	000000000000F0BF
X coordinate	8 bytes	double-precision	000000000000F0BF
Y coordinate	8 bytes	double-precision	000000000000F03F

Polygon

Component	Specification	Type	Value
Byte order	1 byte	unsigned int	01
WKB type	4 bytes	unsigned int	03000000
Num rings	4 bytes	unsigned int	Greater than or equal to 1
repeat ring	-	-	-

MultiPoint

Component	Specification	Type	Value
Byte order	1 byte	unsigned int	01
WKB type	4 bytes	unsigned int	04000000
Num points	4 bytes	unsigned int	Num points >= 1
repeat POINT	-	-	-

MultiLineString

Component	Specification	Type	Value
Byte order	1 byte	unsigned int	01
WKB type	4 bytes	unsigned int	05000000
Num linestrings	4 bytes	unsigned int	Greater than or equal to 1
repeat LINESTRING	-	-	-

MultiPolygon

Component	Specification	Type	Value
Byte order	1 byte	unsigned int	01
WKB type	4 bytes	unsigned int	06000000
Num polygons	4 bytes	unsigned int	Greater than or equal to 1
repeat POLYGON	-	-	-

GeometryCollection

Component	Specification	Type	Value
Byte order	1 byte	unsigned int	01
WKB type	4 bytes	unsigned int	07000000
Num wkbs	4 bytes	unsigned int	-
repeat WKB	-	-	-

Note:

• Only GeometryCollection can be empty, indicating that 0 elements are stored. All 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 must satisfy 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's ring must have at least four points (the smallest polygon is a triangle, where the first and last points are the same).
• Except for GeometryCollection, other collection types cannot be empty.

Geometric validity

Geometric validity must satisfy the following conditions:

• A polygon cannot intersect with itself.
• The exterior ring of a Polygon must be outside the interior rings.
• Multipolygons cannot contain overlapping polygons.

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

GIS Examples

Insert data

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

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

// Conversion expressions are directly embedded in the parameters.
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-specific conversion functions are employed.
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));

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

Query data

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

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