Well Known Text Or Binary
I am going to use the GEOMETRY data type over POINT, LINESTRING, or POLYGON as it can store any of those three while the other three can only contain data matching their name (so POINT can holds only point data, etc.). The values are stored in an internal geometry format but it takes wither WKT or WKB formatted data.
Those are Well-Known Text (WKT) or Well-Known Binary (WKB) formats repectively. I am hoping most of your are better with text than binary so the following examples demonstrate how to insert geometry values into a table by converting WKT values to internal geometry format.
So let us start with a simple table.
mysql> create table geom (type text, g geometry);
Query OK, 0 rows affected (0.04 sec)
We can use the ST_GeomFromText function to take some strings and convert into the internal format.
mysql> insert into geom values
('point', st_geomfromtext('point(1 1)'));
Query OK, 1 row affected (0.01 sec)
mysql> insert into geom values
('linestring', st_geomfromtext('linestring(0 0,1 1, 2 2)'));
Query OK, 1 row affected (0.01 sec)
There are type specific functions for POINT, LINESTRING, and POLYGON that we can also take advantage of for this work.
mysql> SET @g =
'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO geom
VALUES ('polygon',ST_PolygonFromText(@g));
Query OK, 1 row affected (0.00 sec)
If you do a SELECT * FROM geom; you will get the g column output in binary. Thankfully we can use ST_AsText() to provide us with something more readable.
mysql> select type, st_astext(g) from geom;
+------------+----------------------------------------------------------+
| type | st_astext(g) |
+------------+----------------------------------------------------------+
| point | POINT(1 1) |
| linestring | LINESTRING(0 0,1 1,2 2) |
| polygon | POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7,5 5)) |
+------------+----------------------------------------------------------+
3 rows in set (0.00 sec)
mysql>
Put the 'fun' in Function
There are functions that can be use with each of these type to provide information., For instance there are the x & Y coordinates for a point.
mysql> select type, st_x(g), st_y(g) from geom where type='point';
+-------+---------+---------+
| type | st_x(g) | st_y(g) |
+-------+---------+---------+
| point | 1 | 1 |
+-------+---------+---------+
1 row in set (0.00 sec)
We can even use our linestring data to get the minimal bounding rectangle for the given coordinates (basically if we had to put an envelope around the points the results are the coordinates points to enclose those x,y coordinates)
mysql> select st_astext(st_envelope(g))
from geom where type='linestring';
+--------------------------------+
| st_astext(st_envelope(g)) |
+--------------------------------+
| POLYGON((0 0,2 0,2 2,0 2,0 0)) |
+--------------------------------+
1 row in set (0.00 sec)
And we can get the area of a polygon too.
mysql> select type, st_area((g)) from geom where type='polygon';
+---------+--------------+
| type | st_area((g)) |
+---------+--------------+
| polygon | 96 |
+---------+--------------+
1 row in set (0.00 sec)
And find the mathematical center of that polygon.
mysql> select type,
st_astext(st_centroid(g))
from geom where type='polygon';
+---------+--------------------------------------------+
| type | st_astext(st_centroid(g)) |
+---------+--------------------------------------------+
| polygon | POINT(4.958333333333333 4.958333333333333) |
+---------+--------------------------------------------+
1 row in set (0.00 sec)
Plus we can get the linestring data if we wanted to draw out polygon.
mysql> select type,
st_astext(st_exteriorring(g))
from geom where type='polygon';
+---------+-------------------------------------+
| type | st_astext(st_exteriorring(g)) |
+---------+-------------------------------------+
| polygon | LINESTRING(0 0,10 0,10 10,0 10,0 0) |
+---------+-------------------------------------+
1 row in set (0.00 sec)
