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 |