18.4.4. Populating Spatial Columns
After you have created spatial columns, you can populate them
with spatial data.
Values should be stored in internal geometry format, but you can
convert them to that format from either Well-Known Text (WKT) or
Well-Known Binary (WKB) format. The following examples
demonstrate how to insert geometry values into a table by
converting WKT values into internal geometry format:
-
Perform the conversion directly in the
INSERT statement:
INSERT INTO geom VALUES (GeomFromText('POINT(1 1)'));
SET @g = 'POINT(1 1)';
INSERT INTO geom VALUES (GeomFromText(@g));
-
Perform the conversion prior to the
INSERT:
SET @g = GeomFromText('POINT(1 1)');
INSERT INTO geom VALUES (@g);
The following examples insert more complex geometries into the
table:
SET @g = 'LINESTRING(0 0,1 1,2 2)';
INSERT INTO geom VALUES (GeomFromText(@g));
SET @g = 'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';
INSERT INTO geom VALUES (GeomFromText(@g));
SET @g =
'GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))';
INSERT INTO geom VALUES (GeomFromText(@g));
The preceding examples all use GeomFromText()
to create geometry values. You can also use type-specific
functions:
SET @g = 'POINT(1 1)';
INSERT INTO geom VALUES (PointFromText(@g));
SET @g = 'LINESTRING(0 0,1 1,2 2)';
INSERT INTO geom VALUES (LineStringFromText(@g));
SET @g = 'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';
INSERT INTO geom VALUES (PolygonFromText(@g));
SET @g =
'GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))';
INSERT INTO geom VALUES (GeomCollFromText(@g));
Note that if a client application program wants to use WKB
representations of geometry values, it is responsible for
sending correctly formed WKB in queries to the server. However,
there are several ways of satisfying this requirement. For
example:
-
Inserting a POINT(1 1) value with hex
literal syntax:
mysql> INSERT INTO geom VALUES
-> (GeomFromWKB(0x0101000000000000000000F03F000000000000F03F));
-
An ODBC application can send a WKB representation, binding
it to a placeholder using an argument of
BLOB type:
INSERT INTO geom VALUES (GeomFromWKB(?))
Other programming interfaces may support a similar
placeholder mechanism.
In a C program, you can escape a binary value using
mysql_real_escape_string() and include
the result in a query string that is sent to the server. See
Section 25.2.3.52, “mysql_real_escape_string()”.
