1

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?

Improve this question
3
  • Do you want to skip the records with empty geometry or force them to be included? Commented May 5, 2024 at 0:04
  • 2
    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. Commented May 5, 2024 at 4:03
  • 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. Commented May 9, 2024 at 16:24

2 Answers 2

Reset to default
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
Improve this answer
0

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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.