Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 43.3Mb
PDF (A4) - 43.4Mb
Man Pages (TGZ) - 297.2Kb
Man Pages (Zip) - 402.4Kb
Info (Gzip) - 4.3Mb
Info (Zip) - 4.3Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  / ...  / Data Types  / Spatial Data Types  /  Populating Spatial Columns

13.4.7 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 to internal geometry format:

  • Perform the conversion directly in theINSERT statement:

    INSERT INTO geom VALUES (ST_GeomFromText('POINT(1 1)'));SET @g = 'POINT(1 1)';INSERT INTO geom VALUES (ST_GeomFromText(@g));

  • Perform the conversion prior to theINSERT:

    SET @g = ST_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 (ST_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 (ST_GeomFromText(@g));SET @g ='GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))';INSERT INTO geom VALUES (ST_GeomFromText(@g));

The preceding examples useST_GeomFromText() to create geometry values. You can also use type-specific functions:

SET @g = 'POINT(1 1)';INSERT INTO geom VALUES (ST_PointFromText(@g));SET @g = 'LINESTRING(0 0,1 1,2 2)';INSERT INTO geom VALUES (ST_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 (ST_PolygonFromText(@g));SET @g ='GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))';INSERT INTO geom VALUES (ST_GeomCollFromText(@g));

A client application program that wants to use WKB representations of geometry values is responsible for sending correctly formed WKB in queries to the server. There are several ways to satisfy this requirement. For example:

  • Inserting aPOINT(1 1) value with hex literal syntax:

    INSERT INTO geom VALUES(ST_GeomFromWKB(X'0101000000000000000000F03F000000000000F03F'));

  • An ODBC application can send a WKB representation, binding it to a placeholder using an argument ofBLOB type:

    INSERT INTO geom VALUES (ST_GeomFromWKB(?))

    Other programming interfaces may support a similar placeholder mechanism.

  • In a C program, you can escape a binary value usingmysql_real_escape_string_quote() and include the result in a query string that is sent to the server. Seemysql_real_escape_string_quote().