1

First I have a problem creating spatial index in MySQL 8.0. Here the description:

  1. I have a collection of several thousand species range maps (polygon & multi-polygon) which is provided by IUCN as a single shapefile.
  2. I opened this shapefile in QGIS 3.6 and performed geometry check (Vector/Geometry Tools/Check validity).
  3. All valid range maps have been exported into a new shapefile.
  4. I imported this new shapefile into MySQL 8.0 via command line tool ogr2ogr.
  5. For the ogr2ogr import I had to use the option "-skipfailures". Reason: ogr2ogr considered some of the range maps as invalid.
  6. The remaining maps were imported into a MySQL table. And it seems all ok, at least you can run queries successfully. But the queries are slow, because MySQL always reads the whole table.
  7. So I decided to create a spatial index and for that you'll need to set a SRID. And here the problem starts:

“03:10:32 UPDATE gis.all_reptiles_innodb SET SHAPE = ST_GeomFromText(ST_AsText(shape), 4326) Error Code: 3617. Latitude 124.241900 is out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000]. 0.000 sec”

I can't set a SRID because of problems with the latitude range.

A latitude of "124.241900" is obviously impossible, because latitude can only range from -90 and +90 degrees. It seems to happen quite often, that people mix up longitude (allowed range: -180 and +180 degrees) with latitude.

Additional information: QIS shows these properties for the shapefile:

“-181.5899672849999718,-54.0594466929999840 : 181.1581243180000911,71.1881355930000836”

Does somebody have an idea what went wrong and how I could prevent this error?

Something in the interaction between shapefile, QGIS, ogr2ogr and MySQL obviously caused problems. The map display in QGIS, however, seems to work fine.

I would like to import only valid maps into MySQL because I need the spatial data index. Without an index a single query takes >8 seconds and that is too slow.

  • MySQL 8.0 changed the way it does indexing. See mysqlserverteam.com/geographic-indexes-in-innodb – Mapperz Apr 11 at 1:33
  • The indexing changes are a good point. The problem is: When I use ogr2ogr with "-update" it will assign "SRID 0". And I can't change it after to SRID 4326 as needed due to the problems described. If I use ogr2ogr with "-append" it will not insert a single row into the table because it considers all data records as invalid. Anybody an idea how I can tell ogr2ogr that my source material has SRID 4326 and that I want to insert it the same way in the MySQL table? – sts Apr 11 at 4:40
  • you need "With MySQL 8.0 or later, the ST_SPATIAL_REFERENCE_SYSTEMS table provided by the database is used instead of spatial_ref_sys. " as described gdal.org/drv_mysql.html – Mapperz Apr 11 at 13:33
1

Thanks for the hint! I played around with ogr2ogr and this seems to be the solution for the import including the correct SRID:

ogr2ogr -skipfailures -f "MySQL" MYSQL:"gis,host=localhost,user=root,password=#123456789" -nln “my_tablename” -update -overwrite -t_srs EPSG:4326 -lco engine=INNODB my_shapefile.shp

But this sets only the SRID for each row. You have then to set an SRID for the column to create the spatial index:

ALTER TABLE my_table MODIFY SHAPE geometry NOT NULL SRID 4326;

Then you can create the spatial index:

ALTER TABLE my_table ADD SPATIAL INDEX(SHAPE);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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