Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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: http://stackoverflow.com/questions/1282962/error-creating-spatial-index-on-mysql-blob-column

share|improve this answer

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

share|improve this answer

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

    
Welcome to GIS SE! Something to be aware of is that your one line answer is attracting flags from other users, and risks your answer being deleted. I recommend "about a paragraph" being considered as minimum length for both questions and answers here. Would you be able to use the edit button beneath your answer to expand upon it slightly, please? –  PolyGeo Nov 27 at 22:44
1  
Welcome to GIS@SE. I suggest you give an example command line to expand your answer otherwise you risk having your answer voted for deletion. –  Mark C Nov 28 at 0:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.