Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Supported Spatial Data Formats Supported Spatial Data Formats

Two standard spatial data formats are used to represent geometry objects in queries:

  • Well-Known Text (WKT) format

  • Well-Known Binary (WKB) format

Internally, MySQL stores geometry values in a format that is not identical to either WKT or WKB format.

There are functions available to convert between different data formats; see Section 12.15.6, “Geometry Format Conversion Functions”. Well-Known Text (WKT) Format

The Well-Known Text (WKT) representation of geometry values is designed for exchanging geometry data in ASCII form. The OpenGIS specification provides a Backus-Naur grammar that specifies the formal production rules for writing WKT values (see Section 11.5, “Extensions for Spatial Data”).

Examples of WKT representations of geometry objects:

  • A Point:

    POINT(15 20)

    The point coordinates are specified with no separating comma. This differs from the syntax for the SQL Point() function, which requires a comma between the coordinates. Take care to use the syntax appropriate to the context of a given spatial operation. For example, the following statements both extract the X-coordinate from a Point object. The first produces the object directly using the Point() function. The second uses a WKT representation converted to a Point with GeomFromText().

    mysql> SELECT ST_X(Point(15, 20));
    | ST_X(POINT(15, 20)) |
    |                  15 |
    mysql> SELECT ST_X(ST_GeomFromText('POINT(15 20)'));
    | ST_X(ST_GeomFromText('POINT(15 20)')) |
    |                                    15 |
  • A LineString with four points:

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

    The point coordinate pairs are separated by commas.

  • A Polygon with one exterior ring and one interior ring:

    POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))
  • A MultiPoint with three Point values:

    MULTIPOINT(0 0, 20 20, 60 60)

    As of MySQL 5.7.9, spatial functions such as ST_MPointFromText() and ST_GeomFromText() that accept WKT-format representations of MultiPoint values permit individual points within values to be surrounded by parentheses. For example, both of the following function calls are valid, whereas before MySQL 5.7.9 the second one produces an error:

    ST_MPointFromText('MULTIPOINT (1 1, 2 2, 3 3)')
    ST_MPointFromText('MULTIPOINT ((1 1), (2 2), (3 3))')

    As of MySQL 5.7.9, output for MultiPoint values includes parentheses around each point. For example:

    mysql> SET @mp = 'MULTIPOINT(1 1, 2 2, 3 3)';
    mysql> SELECT ST_AsText(ST_GeomFromText(@mp));
    | ST_AsText(ST_GeomFromText(@mp)) |
    | MULTIPOINT((1 1),(2 2),(3 3))   |

    Before MySQL 5.7.9, output for the same value does not include parentheses around each point:

    mysql> SET @mp = 'MULTIPOINT(1 1, 2 2, 3 3)';
    mysql> SELECT ST_AsText(ST_GeomFromText(@mp));
    | ST_AsText(ST_GeomFromText(@mp)) |
    | MULTIPOINT(1 1,2 2,3 3)         |
  • A MultiLineString with two LineString values:

    MULTILINESTRING((10 10, 20 20), (15 15, 30 15))
  • A MultiPolygon with two Polygon values:

    MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5)))
  • A GeometryCollection consisting of two Point values and one LineString:

    GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20)) Well-Known Binary (WKB) Format

The Well-Known Binary (WKB) representation of geometric values is used for exchanging geometry data as binary streams represented by BLOB values containing geometric WKB information. This format is defined by the OpenGIS specification (see Section 11.5, “Extensions for Spatial Data”). It is also defined in the ISO SQL/MM Part 3: Spatial standard.

WKB uses 1-byte unsigned integers, 4-byte unsigned integers, and 8-byte double-precision numbers (IEEE 754 format). A byte is eight bits.

For example, a WKB value that corresponds to POINT(1 1) consists of this sequence of 21 bytes, each represented by two hex digits:


The sequence consists of these components:

Byte order:   01
WKB type:     01000000
X coordinate: 000000000000F03F
Y coordinate: 000000000000F03F

Component representation is as follows:

  • The byte order is either 1 or 0 to indicate little-endian or big-endian storage. The little-endian and big-endian byte orders are also known as Network Data Representation (NDR) and External Data Representation (XDR), respectively.

  • The WKB type is a code that indicates the geometry type. Values from 1 through 7 indicate Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection.

  • A Point value has X and Y coordinates, each represented as a double-precision value.

WKB values for more complex geometry values have more complex data structures, as detailed in the OpenGIS specification.

Download this Manual
User Comments
Sign Up Login You must be logged in to post a comment.