Skip navigation links
11.13.4.5 Fetching Spatial Data »
Section Navigation      [Toggle]

11.13.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 20.9.3.53, “mysql_real_escape_string()”.


User Comments

Posted by Clive Page on July 29 2003 1:50am[Delete] [Edit]

At present the only way (that I can see) to populate a column of geometry type is to use a sequence of INSERT (or I suppose UPDATE) statements, which are rather verbose and slow. It would be nice to have a defined format for an external file so that one could do a bulk load, using LOAD DATA INFILE.

Posted by Gijsbert Noordam on September 16 2003 11:01pm[Delete] [Edit]

The key to a successful adoption of the SPATIAL EXTENSIONS in MySQL is the ability to be able to bulk load spatial data. From my experience, other database platforms with support for spatial data (notably: Oracle) have been held back for years because of the difficulty of loading legacy GIS data. In that light, being able to use LOAD DATA INFILE for bulk loading spatial data would -- indeed -- be a very important first step.

Currently, this -- sort of -- works because LOAD DATA INFILE is capable of loading (a representation of) the internal geometry format. It appears to me a SELECT ... INTO OUTFILE created file containing spatial data can be uploaded again. Naturally, it would be better if LOAD DATA INFILE was capable of handling WKT of WKB representations of spatial data.

A possible -- albeit lousy -- work-around is to load e.g. the WKT representation as a text column using LOAD DATA INFILE and then transfer the data into the geometry column using an UPDATE. For limited amounts of not too complex data this seems to work.

Posted by Ian Scrivener on February 18 2005 3:33pm[Delete] [Edit]

I took me some time to figure out how to populate a Spatial Point column from aspatial Decimal Lat/Long values.

UPDATE myTable
SET Coord = PointFromText(CONCAT('POINT(',myTable.DLong,' ',myTable.DLat,')'));

... is there a better way?

Posted by Stein Haugan on February 27 2007 9:22am[Delete] [Edit]

Clive Page and Gijsbert Noordam describe a problem that I have, too: Can't use LOAD DATA INFILE for geometry columns, b/c the input is dependent on a function (i.e. input is not literal data).

However, I think I've found a way to do it: Create "phantom" tables for insertions (one for each type of geometry to insert). Create insert triggers for each phantom table, and make the trigger call a stored procedure that converts the input coordinates (one phantom table column for each coordinate) into a geometry object, and inserts it into the actual geometry column.

I haven't implemented the solution myself, since the geometry inserts are only a small fraction of the time it takes to ingest a full record, but I can't see why it shouldn't work. It should also be fairly efficient. The phantom tables could be black holes, of course.

Posted by Richard Bronosky on December 26 2007 4:10pm[Delete] [Edit]

For those wanting to use LOAD DATA INFILE for spatial data, the solution may actually be hidden on the manual page http://dev.mysql.com/doc/refman/5.0/en/load-data.html which demonstrates:

LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, @var1)
SET column2 = @var1/100;

The important thing to take away from this is:
1. Each column of source data can be routed to custom targets.
2. The targets can be destination table columns or user space variables.
3. The optional SET clause is evaluated after the custom targets are populated.
4. The optional SET clause allows you to use the full suite of MySQL functions to preprocess values loaded into user space variables.

These points should give you the power to systematically solve any data loading need. Does it not?

Posted by Colin Guthrie on July 4 2008 2:56pm[Delete] [Edit]

Re: Ian Scrivener's comment about using CONCAT to populate a Point column in a table that already has lat/lng fields:

I first took this approach too, but when reading the manual some more I noticed there were some functions MySQL provides that return WKB format.

I found that in a table of 23k odd entries, using the CONCAT method verses using:
SET point = PointFromWKB(Point(latitude, longitude))
(where Point() is a MySQL function that return WKB)

caused differences in three rows. I'm sure these differences are related to rounding and precision and wont really matter too much in all practical use.

Hope this helps.

Posted by Jerry Fowler on November 17 2008 2:33am[Delete] [Edit]

Re: Posted by Richard Bronosky on December 26 2007 4:10pm

Thanks for this. One is still compelled to use the CONCAT('POINT('... hack above in the LOAD ... SET statement.
The idea of using PointFromText(Point(a,b)) is nice, but doesn't work (5.0).

This, however, does:
CREATE TABLE `table_with_a_point` (
`id` bigint(20) not null,
`location` point not NULL,
`latitude` float default NULL,
`longitude` float default NULL,
`value` int(11) not null,
PRIMARY KEY (`id`)
);
create spatial index table_with_a_point_index on table_with_a_point(location);

LOAD DATA LOCAL INFILE 'somedata.txt'
INTO TABLE table_with_a_point
COLUMNS TERMINATED BY ' ' LINES TERMINATED BY '\r\n'
(id, latitude, longitude, value)
set location = PointFromText(CONCAT('POINT(',latitude,' ',longitude,')'));


Add your own comment.