MySQL 5.6 リファレンスマニュアル  /  ...  /  空間カラムへのデータ移入 空間カラムへのデータ移入


値は内部幾何形式で格納する必要がありますが、WKT (Well-Known Text)、WKB (Well-Known Binary) のいずれの形式からでも、その形式に値を変換できます。次の例は、WKT 値を内部幾何形式に変換することによって、幾何値をテーブルに挿入する方法を示しています。

  • 次のように INSERT ステートメント内で直接変換を実行します。

    INSERT INTO geom VALUES (GeomFromText('POINT(1 1)'));
    SET @g = 'POINT(1 1)';
    INSERT INTO geom VALUES (GeomFromText(@g));
  • 次のように INSERT の前に変換を実行します。

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


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 =
INSERT INTO geom VALUES (GeomFromText(@g));

前述の例では、GeomFromText() を使用して幾何値を作成しています。次のように型に固有の関数を使用することもできます。

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 =
INSERT INTO geom VALUES (GeomCollFromText(@g));

幾何値の WKB 表現を使用するクライアントアプリケーションプログラムが、クエリーで正しく作成された WKB のサーバーへの送信を担います。この要件を満たす方法は複数あります。例:

  • 次のように、16 進リテラル構文を使用して、POINT(1 1) 値を挿入します。

    mysql> INSERT INTO geom VALUES
        -> (GeomFromWKB(0x0101000000000000000000F03F000000000000F03F));
  • ODBC アプリケーションは、BLOB 型の引数を使用して WKB 表現をプレースホルダにバインドし、WKB 表現を送信できます。

    INSERT INTO geom VALUES (GeomFromWKB(?))


  • C プログラムでは、mysql_real_escape_string() を使用してバイナリ値をエスケープし、その結果をクエリー文字列に含めてサーバーに送信できます。セクション23.8.7.54「mysql_real_escape_string()」を参照してください。

User Comments
  Posted by Clive Page on July 29, 2003
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
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
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
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
For those wanting to use LOAD DATA INFILE for spatial data, the solution may actually be hidden on the manual page which demonstrates:

(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
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
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,
create spatial index table_with_a_point_index on table_with_a_point(location);

INTO TABLE table_with_a_point
(id, latitude, longitude, value)
set location = PointFromText(CONCAT('POINT(',latitude,' ',longitude,')'));

Sign Up Login You must be logged in to post a comment.