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
INSERTstatement: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
BLOBtype: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 17.2.3.51, “mysql_real_escape_string()”.
User Comments
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.
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.
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?
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.
Add your own comment.