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);