Invalid GIS data provided when importing .gdb file into MySQL using ogr2ogr

Question

I'm using ogr2ogr to import a GDB file into MySQL, and I get this error :

ERROR 1: MySQL error message:Invalid GIS data provided to function st_geomfromtext. Description: INSERT INTO `test_table` (`Shape` , `OBJECTID` , `prop_id`, `geo_id`, `shape_length`, `shape_area`) VALUES (ST_GeomFromText('MULTIPOLYGON EMPTY',4326, 'axis-order=long-lat') , 7080, ' ', ' ', 0, 0)
ERROR 1: Unable to write feature 7080 from layer land_layer.
ERROR 1: Terminating translation prematurely after failed
translation of layer land_layer (use -skipfailures to skip errors)

My command is :

ogr2ogr -f MySQL -t_srs EPSG:4326 MySQL:test,user=user,password=password /parcels.gdb -overwrite

Obviously the error is ST_GeomFromText('MULTIPOLYGON EMPTY') because MySQL only allows GeometryCollections to be empty, not MultiPolygons.

Is it possible to overwrite this via an option on the import command that I'm missing? Or how can I dump the insert commands to a mysqldump file and then do a sed replace on the file? Or would it be better to export to another type like JSON and then import that?

Or should I just switch my entire database setup to PostgreSQL?

• user2856: Do you want to skip the records with empty geometry or force them to be included?
• Vince: If MySQL is non-conformant on something basic like Nil geometries, I can't help but wonder what else will bite you next, so I don't think switching to PostgreSQL is all that extreme a response.
• Marshall C: I was able to solve my issue by adding -nlt GeometryCollection to the ogr2ogr command. I had previously tried -lco GEOMETRY_AS_COLLECTION=YES but that didn't work as I assumed.

Answer 1

I don't know if this would work, I don't have MySQL set up so can't test, but you could try casting to a geometry collection with some sql in ogr2ogr, e.g. something like:

ogr2ogr -dialect sqlite -sql "SELECT CastToGeometryCollection(geom) as geometry, REST, OF, FIELDS from parcels" MySQL:test,user=user,password=password parcels.gdb

Answer 2

In case anyone stumbles upon this in the future, I was able to solve my issue by adding -nlt GEOMETRYCOLLECTION to the ogr2ogr command.

ogr2ogr -f MySQL -t_srs EPSG:4326 MySQL:test,user=user,password=password /parcels.gdb -overwrite -nlt GEOMETRYCOLLECTION