First I have a problem creating spatial index in MySQL 8.0. Here the description:
- I have a collection of several thousand species range maps (polygon & multi-polygon) which is provided by IUCN as a single shapefile.
- I opened this shapefile in QGIS 3.6 and performed geometry check (Vector/Geometry Tools/Check validity).
- All valid range maps have been exported into a new shapefile.
- I imported this new shapefile into MySQL 8.0 via command line tool ogr2ogr.
- For the ogr2ogr import I had to use the option "-skipfailures". Reason: ogr2ogr considered some of the range maps as invalid.
- 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.
- 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.