Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 32.3Mb
PDF (A4) - 32.3Mb
PDF (RPM) - 30.4Mb
HTML Download (TGZ) - 7.8Mb
HTML Download (Zip) - 7.8Mb
HTML Download (RPM) - 6.7Mb
Man Pages (TGZ) - 142.5Kb
Man Pages (Zip) - 201.7Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb


MySQL 8.0 Reference Manual  /  ...  /  Geometry Format Conversion Functions

Pre-General Availability Draft: 2017-05-26

13.15.6 Geometry Format Conversion Functions

MySQL supports the functions listed in this section for converting geometry values from internal geometry format to WKT or WKB format, or for swapping the order of X and Y coordinates.

There are also functions to convert a string from WKT or WKB format to internal geometry format. See Section 13.15.3, “Functions That Create Geometry Values from WKT Values”, and Section 13.15.4, “Functions That Create Geometry Values from WKB Values”.

Functions such as ST_GeomFromText() that accept WKT geometry collection arguments understand both OpenGIS 'GEOMETRYCOLLECTION EMPTY' standard syntax and MySQL 'GEOMETRYCOLLECTION()' nonstandard syntax. Functions such as ST_AsWKT() that produce WKT values produce 'GEOMETRYCOLLECTION EMPTY' standard syntax:

mysql> SET @s1 = ST_GeomFromText('GEOMETRYCOLLECTION()');
mysql> SET @s2 = ST_GeomFromText('GEOMETRYCOLLECTION EMPTY');
mysql> SELECT ST_AsWKT(@s1), ST_AsWKT(@s2);
+--------------------------+--------------------------+
| ST_AsWKT(@s1)            | ST_AsWKT(@s2)            |
+--------------------------+--------------------------+
| GEOMETRYCOLLECTION EMPTY | GEOMETRYCOLLECTION EMPTY |
+--------------------------+--------------------------+
  • ST_AsBinary(g [, options]), ST_AsWKB(g [, options])

    Converts a value in internal geometry format to its WKB representation and returns the binary result.

    The result is NULL if the geometry argument is NULL. If the argument is not a syntactically well-formed geometry, an ER_GIS_INVALID_DATA error occurs.

    The function return value has geographic coordinates (latitude, longitude) in the order specified by the spatial reference system that applies to the geometry argument. An optional options argument may be given to override the default axis order.

    The result is NULL if the options argument is NULL. If the options argument is invalid, an error occurs to indicate why.

    mysql> SET @g = ST_LineFromText('LINESTRING(0 5,5 10,10 15)', 4326);
    mysql> SELECT ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g)));
    +-----------------------------------------+
    | ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g))) |
    +-----------------------------------------+
    | LINESTRING(5 0,10 5,15 10)              |
    +-----------------------------------------+
    mysql> SELECT ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g, 'axis-order=long-lat')));
    +----------------------------------------------------------------+
    | ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g, 'axis-order=long-lat'))) |
    +----------------------------------------------------------------+
    | LINESTRING(0 5,5 10,10 15)                                     |
    +----------------------------------------------------------------+
    mysql> SELECT ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g, 'axis-order=lat-long')));
    +----------------------------------------------------------------+
    | ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g, 'axis-order=lat-long'))) |
    +----------------------------------------------------------------+
    | LINESTRING(5 0,10 5,15 10)                                     |
    +----------------------------------------------------------------+
    
  • ST_AsText(g [, options]), ST_AsWKT(g [, options])

    Converts a value in internal geometry format to its WKT representation and returns the string result.

    The result is NULL if the geometry argument is NULL. If the argument is not a syntactically well-formed geometry, an ER_GIS_INVALID_DATA error occurs.

    The function return value has geographic coordinates (latitude, longitude) in the order specified by the spatial reference system that applies to the geometry argument. An optional options argument may be given to override the default axis order.

    The result is NULL if the options argument is NULL. If the options argument is invalid, an error occurs to indicate why.

    mysql> SET @g = 'LineString(1 1,2 2,3 3)';
    mysql> SELECT ST_AsText(ST_GeomFromText(@g));
    +--------------------------------+
    | ST_AsText(ST_GeomFromText(@g)) |
    +--------------------------------+
    | LINESTRING(1 1,2 2,3 3)        |
    +--------------------------------+
    

    Output for MultiPoint values includes parentheses around each point. For example:

    
    mysql> SELECT ST_AsText(ST_GeomFromText(@mp));
    +---------------------------------+
    | ST_AsText(ST_GeomFromText(@mp)) |
    +---------------------------------+
    | MULTIPOINT((1 1),(2 2),(3 3))   |
    +---------------------------------+
    
  • ST_SwapXY(g)

    Accepts an argument in internal geometry format, swaps the X and Y values of each coordinate pair within the geometry, and returns the result.

    The result is NULL if the argument is NULL. If the argument is not a syntactically well-formed geometry, an ER_GIS_INVALID_DATA error occurs.

    mysql> SET @g = ST_LineFromText('LINESTRING(0 5,5 10,10 15)');
    mysql> SELECT ST_AsText(@g);
    +----------------------------+
    | ST_AsText(@g)              |
    +----------------------------+
    | LINESTRING(0 5,5 10,10 15) |
    +----------------------------+
    mysql> SELECT ST_AsText(ST_SwapXY(@g));
    +----------------------------+
    | ST_AsText(ST_SwapXY(@g))   |
    +----------------------------+
    | LINESTRING(5 0,10 5,15 10) |
    +----------------------------+
    

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