AnalyticDB for MySQL supports the following geometry functions: ST_Point or Point, ST_AsText, ST_GeometryFromText or ST_GeomFromText, ST_Distance, and ST_Distance_Sphere. This topic describes how to use geometry functions.

ST_Point or Point

ST_Point(x, y)
  • Description: This function uses the result of x/y as a coordinate to construct a point value. x and y are two numerical values of the DOUBLE type.
  • Return value type: GEOMETRY. If you execute the SELECT statement to query results of this function, the returned results will be nonsensical.
  • Example:
    SELECT ST_Point(1,1);
    +-----------------------+
    | ST_Point(1,1)         |
    +-----------------------+
    |        �?      �?   |                    

ST_AsText

ST_AsText(g)
  • Description: This function returns g in the Well-Known Text (WKT) format.
  • Example:
    SELECT ST_AsText(ST_Point(1,1));
    +--------------------------+
    | ST_AsText(ST_Point(1,1)) |
    +--------------------------+
    | POINT (1 1)              |                   

ST_GeometryFromText or ST_GeomFromText

ST_GeometryFromText(wkt)
  • Description: This function uses a WKT-formatted string to construct a value of the GEOMETRY type.
  • Return value type: GEOMETRY. If you execute the SELECT statement to query results of this function, the returned results will be nonsensical.
  • Example:
    SELECT ST_GeometryFromText('Point(1 1)');
    +-----------------------------------+
    | ST_GeometryFromText('Point(1 1)') |
    +-----------------------------------+
    |        �?      �?                 |              
    SELECT ST_AsText(ST_GeometryFromText('Point(1 1)'));
    +----------------------------------------------+
    | ST_AsText(ST_GeometryFromText('Point(1 1)')) |
    +----------------------------------------------+
    | POINT (1 1)                                  |          

ST_Distance

ST_Distance(g1, g2)
  • Description: This function returns the linear distance between g1 and g2.
  • Return value type: DOUBLE.
  • Example:
    SELECT ST_Distance(ST_Point(1,1), ST_Point(2,2));
    +-------------------------------------------+
    | ST_Distance(ST_Point(1,1), ST_Point(2,2)) |
    +-------------------------------------------+
    |                        1.4142135623730951 |           

ST_Distance_Sphere

ST_Distance_Sphere(g1, g2 [, radius])
  • Description: This function returns the spherical distance between g1 and g2. You can specify the sphere radius. The default radius is 6,370,986 meters.
  • Return value type: DOUBLE.
  • Example:
    SELECT ST_Distance_Sphere(point(1,1), point(2,2));
    +--------------------------------------------+
    | ST_Distance_Sphere(point(1,1), point(2,2)) |
    +--------------------------------------------+
    |                         157225.08654191086 |       
    SELECT ST_Distance_Sphere(point(1,1), point(2,2), 6370986);
    +-----------------------------------------------------+
    | ST_Distance_Sphere(point(1,1), point(2,2), 6370986) |
    +-----------------------------------------------------+
    |                                  157225.08654191086 |