LOAD DATA INFILE and LINESTRING

Question

I want to import large CSV file with following columns

SegmentId;Lengthmm;RoadClass;OptimalSpeedKph;SpeedLimitKph;WKT 1;7329;1;120;70;LINESTRING (28.97845 41.067332,28.978537 41.067332) ...

I am using LOAD DATA INFILE. Problem is that WKT is LINESTRING. Actually if I add one entry with following query:

INSERT INTO FCD VALUES (3,1,1,1,1,LineStringFromText( 'LINESTRING (28.947971 41.050192,28.94754 41.049887)'));

It is inserted into table.

However I didn't know to access the WKT parameter in LOAD DATA INFILE command and transform it.

LOAD DATA LOCAL INFILE '/home/username/userfile.csv' INTO TABLE `FCD` FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES

It gives me the following error:

ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field

With regards

axiac · Accepted Answer · 2015-03-11 14:53:23Z

You can use a variable to load the value from the WKT column and use the SET clause of the LOAD DATA INFILE statement to use the value of the variable in an expression and assign it to a column:

LOAD DATA LOCAL INFILE '/home/username/userfile.csv' INTO TABLE `FCD` FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (SegmentId, Lengthmm, RoadClass, OptimalSpeedKph, SpeedLimitKph, @WKT) SET WKT = LineStringFromText(@WKT)

I assumed the first line of the CSV file contains the names of the table columns. Remark the last field in the list is @WKT which is a variable then the SET clause that uses the value of variable @WKT into an expression then assign it to column WKT.

I will also try your solution. Now I am trying the earlier answer, but my file contains 12M entries. I will answer as long it is finished

MattyB · Accepted Answer · 2015-03-11 14:42:59Z

Create a staging table where WKT is a string. Then run

INSERT INTO FCD SELECT SegmentId, Lengthmm, RoadClass, OptimalSpeedKph, SpeedLimitKph, LineStringFromText (WKT) FROM FCD_staging;

Collectives™ on Stack Overflow

LOAD DATA INFILE and LINESTRING

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related