Skip navigation links

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