MySQL 5.1 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.

As of MySQL 5.1.35, 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.

Before MySQL 5.1.35, these functions produce BLOB values containing WKB representations of geometry values with no SRID from WKB arguments. The WKB value returned from these functions can be converted to geometry arguments by using them as the first argument to functions in the GeomFromWKB() function family.

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

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

Prior to MySQL 5.1.35, convert the WKB return value to a Point before inserting it:

INSERT INTO t1 (pt_col) VALUES(GeomFromWKB(Point(1,2)));
  • GeometryCollection(g1,g2,...)

    Constructs a GeometryCollection.

    If the argument contains a nonsupported geometry, the return value is NULL.

  • LineString(pt1,pt2,...)

    Constructs a LineString value from a number of Point or WKB Point arguments. If the number of arguments is less than two, the return value is NULL.

  • MultiLineString(ls1,ls2,...)

    Constructs a MultiLineString value using LineString or WKB LineString arguments.

  • MultiPoint(pt1,pt2,...)

    Constructs a MultiPoint value using Point or WKB Point arguments.

  • MultiPolygon(poly1,poly2,...)

    Constructs a MultiPolygon value from a set of Polygon or WKB Polygon arguments.

  • Point(x,y)

    Constructs a Point using its coordinates.

  • Polygon(ls1,ls2,...)

    Constructs a Polygon value from a number of LineString or WKB LineString arguments. If any argument does not represent a LinearRing (that is, not a closed and simple LineString), the return value is NULL.

Download this Manual
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.