Can't export a polygon shapefile to MySQL using ogr2ogr?

Question

I am having problems with ogr2ogr and keep getting different versions of the error:

ERROR 1: MySQL error message: The used table type doesn't support SPATIAL indexes. Description: ALTER TABLE 'test_poly' ADD SPATIAL INDEX('SHAPE')

my command (entering it in FWTools Shell) is:

ogr2ogr -f "MySQL" MYSQL:"test,host=27.0.0.1,user=root,port=3306" -nln test_poly -a_srs "EPSG:3857" -update -overwrite

...adding '-lco GEOMETRY_NAME ' doesn't do anything... ...also moving the ti tge ebdm behind -lco engine=MYSIAM does nothing...

All I see is the tables 'geometry_columns' and spatial_ref_sys' and 'test_poly' being created...but no data *(i.e. when I use "Select Rows - Limit 1000").

What am I missing?

I am a bit new to MySQL...but I read as much documentation as I could find...I thought I could set the spatial index by going to Engine: MYSIAM....am I wrong?

Answer 1

reference:

Location GEOMETRY NOT NULL,

spatial indexes should be created on GEOMETRY types.

CREATE TABLE `test_poly` (
        `Id` int(11) NOT NULL, 
        `Name` varchar(255) NOT NULL, 
        `Location` GEOMETRY NOT NULL,
        PRIMARY KEY (`Id`),
        SPATIAL KEY (`Location`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

source: https://stackoverflow.com/questions/1282962/error-creating-spatial-index-on-mysql-blob-column

Answer 2

To solve this issue you must add -lco ENGINE=MyISAM to your ogr2ogr command.

-lco NAME=VALUE: Layer creation option (format specific)

GDAL ogr2ogr docs

In this case this parameter sets storage engine for MySQL table(s). In MySQL 5.6 spatial indexes supports only on this storage engine.

For indexing spatial columns, MyISAM supports both SPATIAL and non-SPATIAL indexes. The other storage engines support non-SPATIAL indexes, as described in Section 13.1.13, “CREATE INDEX Syntax”.

Mysql Extensions for Spatial Data docs