Spatial data formats

seekdb supports two standard spatial data formats for representing geometric objects in queries:

• Well-Know Text Format (WKT)

• Well-Know Binary Format (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 document) or for defining 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 of a Point to a Point object using ST_GeomFromText().

SELECT ST_X(Point(15, 20));

The result is as follows:

+---------------------+
| ST_X(Point(15, 20)) |
+---------------------+
|                  15 |
+---------------------+
1 row in set (0.001 sec)

SELECT ST_X(ST_GeomFromText('POINT(15 20)'));

The result is as follows:

+---------------------------------------+
| ST_X(ST_GeomFromText('POINT(15 20)')) |
+---------------------------------------+
|                                    15 |
+---------------------------------------+
1 row in set (0.001 sec)

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 ST_MPointFromText() and ST_GeoFromText() functions, 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 collections.

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) and their corresponding format definitions.

Point

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

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

The format definition is shown in the following table, taking LINESTRING(1 -1, -1 1) as an example. 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	-	-	-

info

• Only GeometryCollection can be empty, indicating that it stores 0 elements. Other types cannot be empty.

• When LENGTH() is applied to a GIS object, it returns the length of the stored binary data.

SET @g = ST_GeomFromText('POINT(1 -1)');

The result is as follows:

Query OK, 0 rows affected (0.000 sec)

SELECT LENGTH(@g);

The result is as follows:

+------------+
| LENGTH(@g) |
+------------+
|         25 |
+------------+
1 row in set (0.001 sec)

SELECT HEX(@g);

The result is as follows:

+----------------------------------------------------+
| HEX(@g)                                            |
+----------------------------------------------------+
| 000000000101000000000000000000F03F000000000000F0BF |
+----------------------------------------------------+
1 row in set (0.001 sec)

Syntax and geometric validity

Syntax validity

Syntax validity must meet 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 in a ring (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 meet the following conditions:

• A Polygon cannot intersect itself.
• The outer ring of a Polygon must be outside the inner ring.
• Multipolygons cannot contain overlapping polygons.

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

GIS examples

Insert examples

//Converts the WKT to a geometry value and inserts it into the geom table.
INSERT INTO geom VALUES (ST_GeomFromText('POINT(1 1)'));

//Converts the WKT to a geometry value and inserts it into the geom table.
SET @g = 'POINT(1 1)';
INSERT INTO geom VALUES (ST_GeomFromText(@g));

//Converts the WKT to a geometry value and inserts it into the geom table.
SET @g = ST_GeomFromText('POINT(1 1)');
INSERT INTO geom VALUES (@g);

//Converts the WKT to a geometry value and inserts it into the geom table.
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));

//Converts the WKT to a geometry value and inserts it into the geom table.
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 insert a geometry value based on the WKB.
INSERT INTO geom VALUES(ST_GeomFromWKB(X'0101000000000000000000F03F000000000000F03F'));

Query examples

//Queries the geometry values and converts them to WKT.
SELECT ST_AsText(g) FROM geom;

//Queries the geometry values and converts them to WKB.
SELECT ST_AsBinary(g) FROM geom;