Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.9Mb
PDF (A4) - 37.9Mb
PDF (RPM) - 32.8Mb
HTML Download (TGZ) - 8.0Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 6.9Mb
Man Pages (TGZ) - 132.7Kb
Man Pages (Zip) - 189.0Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  MySQL-Specific Functions That Create Geometry Values

12.15.5 MySQL-Specific Functions That Create Geometry Values

MySQL provides a set of useful nonstandard functions for creating geometry values. The functions described in this section are MySQL extensions to the OpenGIS specification.

These functions produce geometry objects from either WKB values or geometry objects as arguments. If any argument is not a proper WKB or geometry representation of the proper object type, the return value is NULL.

For example, you can insert the geometry return value from Point() directly into a POINT column:

INSERT INTO t1 (pt_col) VALUES(Point(1,2));

User Comments
  Posted by on November 12, 2006
you can use these special mySQL functions to create new geometries from geometric values returned from performing validations or so on existing geometries, like so:

1. get 2 separate polygons' centroids (return type POINT)
2. create a line connecting the two centroids using the mySQL function

... create a polygon
select @ctrP1 := Centroid(polygonfromtext( @poly));
| astext( @ctrP1) |
| POINT(474265.80876111 5525475.9707162) |

... set @poly to a different polygon ...

select @ctrP2 := Centroid(polygonfromtext( @poly));
| astext( @ctrP2) |
| POINT(475065.80876111 5524675.9707161) |

select @line2 := GeomFromWKB( LineString( asWKB( @ctrP1), asWKB( @ctrP2)));

select astext(@line2);

| astext(@line2) |
| LINESTRING(474265.80876111 5525475.9707162,475065.80876111 5524675.9707161) |
1 row in set (0.00 sec)

  Posted by J Jorgenson on April 11, 2007
Using a raw Geometry column is not very clear, everyone seems to be creating session variables that are TEXT strings used for the GeomFromText( 'slow text string' ) func.

To use actual geometry columns instead of re-constructing text strings for everything (which is hopefully more efficient internally) you need to utilize the function
AsBinary( geom_col );

To create a LineString from two GEOMETRY points:
SET @myLineWKB = LineString( AsBinary(geom_col), AsBinary(geom_col) );

However, the resulting LineString is not actually a Geometry value that could be stuffed into a Geometry data column, but a WKB description of the LineString. To be able to use that LineString, say for Geometry fuctions, you'll need to convert it using LineFromWKB() or GeomFromWKB().
SET @myLine = LineFromWKB(@myLineWKB);
SET @myLine = GeomFromWKB(@myLineWKB);

Performance Thought: One would think that using specific LineFromWKB() is faster than the generic GeomFromWKB(), but it is just a guess.

Now you can obtain the Distance/Length between the two original points using a Geometry function:
SELECT GLength( @myLine );
or more a complex version as a single SQL statement:
SELECT GLength( LineFromWKB( LineString(AsBinary(geom_col), AsBinary(geom_col)) ) );

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