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.
Constructs a WKB GeometryCollection
. If
any argument is not a well-formed WKB representation of a
geometry, the return value is NULL
.
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
.
Constructs a WKB MultiLineString
value
using WKB LineString
arguments. If any
argument is not a WKB LineString
, the
return value is NULL
.
Constructs a WKB MultiPoint
value using
WKB Point
arguments. If any argument is
not a WKB Point
, the return value is
NULL
.
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
.
Constructs a WKB Point
using its
coordinates.
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
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));
...
... set @poly to a different polygon ...
select @ctrP2 := Centroid(polygonfromtext( @poly));
...
--------------
select @line2 := GeomFromWKB( LineString( asWKB( @ctrP1), asWKB( @ctrP2)));
--------------
--------------
select astext(@line2);
--------------
1 row in set (0.00 sec)
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.