3

I am trying to import CSV data into MySQL using the LOAD DATA LOCAL INFILE syntax. This is normally a fairly simple task, but in this case the data includes a geometry field that is tripping me up.

When I try to run the import, I'm getting errors like this:

SQLSTATE[HY000]: General error: 4079 Illegal parameter data type longblob for operation 'st_geometryfromwkb'

The records in my CSV file look like this:

'Somewhere', -0.574823, 51.150771, '0x0101000000000000000000F03F000000000000F0BF'

So I have a location name, lat/long coords and a geometry field in binary WKB format. (the example above is a simple geometry that translates to POINT(1,1); the real data has complex polygons, but the content isn't relevant; the issue is the same with this simple example).

My table looks like this:

CREATE TABLE IF NOT EXISTS `mapping` (
  `id` int AUTO_INCREMENT PRIMARY KEY,
  `location` varchar(80) DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `geom` geometry NOT NULL,
  INDEX mapping_by_location (location),
  SPATIAL KEY `mapping_by_geom` (`geom`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

And my import query looks like this:

LOAD DATA LOCAL INFILE '{$file}'
REPLACE INTO TABLE `mapping`
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
(@col1, @col2, @col3, @col4)
SET
`location` = @col1,
`latitude` = @col2,
`longitude` = @col3,
`geom` = GeomFromWKB(@col4);

As stated, with this import query, I am getting the Illegal parameter data type error shown at the top of this question.

However, the query works if I replace the final line with a hard-coded geometry string, like this:

`geom` = GeomFromWKB(0x0101000000000000000000F03F000000000000F0BF);

Obviously this isn't any good, as I need the field to load from the CVS not a hard-coded value in the query, but it does work, whereas loading the same value from the CSV in @col4 does not.

I have tried a bunch of variations on this query - with and without the call to GeomFromWKB(), with both X'...' and 0x... notations for the hex value; nothing seems to work.

Can anyone give me some help please?

Spudley
  • 166,037
  • 39
  • 233
  • 307
  • have you tried importing col4 as a string an a temp table and after insert select with cast to geomWKB?? or use WKT on text geom? – ScaisEdge Dec 19 '18 at 09:04
  • @scaisEdge I have not tried that. I'll give it a go (although the temp table idea sounds excessively complex; I'll be disappointed if I have to jump through that many hoops just to pull in this data) – Spudley Dec 19 '18 at 09:42
  • You could also Try exporting for a test table the equivalent info as csv a data sample and check for difference between the dato in you file and the data managed by mysql – ScaisEdge Dec 19 '18 at 09:56
  • not sure but it can be the ' ' around the wkb data – Daniel E. Dec 19 '18 at 10:33
  • @DanielE. thanks for the comment. I have tried it without the quotes; no difference. – Spudley Dec 19 '18 at 10:45
  • Other suggestion : Are you sure you don't need to put the SRID, like this : GeomFromWKB(@col4, SRID) ? Because in the documentation, i found this line : "Constructs a Point value using its WKB representation and SRID. The result is NULL if the WKB or SRID argument is NULL." – Daniel E. Dec 19 '18 at 12:55

0 Answers0