Hfrhyu

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.

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);