Skip navigation links
16.4.3 Creating Spatial Columns »
Section Navigation      [Toggle]

16.4.2.3. Creating Geometry Values Using MySQL-Specific Functions

MySQL provides a set of useful nonstandard functions for creating geometry WKB representations. The functions described in this section are MySQL extensions to the OpenGIS specification. The results of these functions are BLOB values containing WKB representations of geometry values with no SRID. The results of these functions can be substituted as the first argument for any function in the GeomFromWKB() function family.

  • GeometryCollection(g1,g2,...)

    Constructs a WKB GeometryCollection. If any argument is not a well-formed WKB representation of a geometry, the return value is NULL.

  • LineString(pt1,pt2,...)

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

  • MultiLineString(ls1,ls2,...)

    Constructs a WKB MultiLineString value using WKB LineString arguments. If any argument is not a WKB LineString, the return value is NULL.

  • MultiPoint(pt1,pt2,...)

    Constructs a WKB MultiPoint value using WKB Point arguments. If any argument is not a WKB Point, the return value is NULL.

  • MultiPolygon(poly1,poly2,...)

    Constructs a WKB MultiPolygon value from a set of WKB Polygon arguments. If any argument is not a WKB Polygon, the return value is NULL.

  • Point(x,y)

    Constructs a WKB Point using its coordinates.

  • Polygon(ls1,ls2,...)

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


User Comments

Posted by [name withheld] on November 12 2006 10:42pm[Delete] [Edit]

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 11:05pm[Delete] [Edit]

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);
or
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)) ) );




Add your own comment.