MySQL Bulk Insert of Geometry fields

I have a mysql database that I'm trying to populate from a text file. The contents of my file look like (as just some examples. there are thousands of rows)

1:GeomFromText('Polygon(0 0, 1 1, 2 2, 0 0)')
2:GeomFromText('Polygon(0 0, 1 2, 2 2, 0 0)')

In my schema, the first field is an integer and the second is GEOMETRY

I try to load the data

开发者_开发知识库LOAD DATA LOCAL INFILE 'myfile.txt' INTO TABLE `testDb`.`testTable` FIELDS TERMINATED BY ':' LINES TERMINATED BY '\n'

And I get the error

Error Code 1416 Cannot get geometry object from data you send to the GEOMETRY field

If I try to do an individual insert like:

INSERT INTO TABLE testTable(id,region) VALUES (1,GeomFromText('Polygon(0 0, 1 1, 2 2, 0 0)'))

It works with no problems. This is very inefficient for a large number of inserts though. Does anyone know why the bulk load is throwing that error?

thanks, Jeff

---

Bulk insert does not evaluate sql statements, it just places the values into the db. That means that GeomFromText('Polygon(0 0, 1 1, 2 2, 0 0)') is interpreted as just a string value, which is not a Geometry object, and hence cannot be inserted into a Geometry column.

You need to set a function to be called on each row insert like below where emphasized text obj_id and obj_geo are the column names:

LOAD DATA INFILE 'c:\myFile.txt'
INTO TABLE `tbl_myTable`
(@var1, @var2)
SET
obj_id = @var1,
obj_geo = GeomFromText(@var2);