MySQL 8.0 Error Code: 3617. Latitude is out of range in function st_geomfromtext Planned maintenance scheduled April 23, 2019 at 00:00UTC (8:00pm US/Eastern) Announcing the arrival of Valued Associate #679: Cesar Manara Unicorn Meta Zoo #1: Why another podcast?Ogr2Ogr exported this boundary 90 degrees incorrectlyAggregate/dissolve MySQL geometryogr2ogr latitute longitude conversion formatsChange the actual coordinates in a shapefile (QGIS)Why would FME imported data into SQL Server not being found by QGIS?howto set ogr2ogr imported SRID to 0? Currently defaulting to SRID 1Run SQL queries against shapefile in QGIS?Importing Shapefile into PostGIS and EPSG of created table is different from Shapefile?Optimizing MySQL query to select all points with in a large polygon using spatial indexesFind all points near a point within a radius MySql 8.0 (SRID 4326)
Sum letters are not two different
What is "gratricide"?
As a beginner, should I get a Squier Strat with a SSS config or a HSS?
How to compare two different files line by line in unix?
Is there a kind of relay only consumes power when switching?
Did Krishna say in Bhagavad Gita "I am in every living being"
Why is it faster to reheat something than it is to cook it?
Maximum summed subsequences with non-adjacent items
Time to Settle Down!
Why do we need to use the builder design pattern when we can do the same thing with setters?
Is there hard evidence that the grant peer review system performs significantly better than random?
AppleTVs create a chatty alternate WiFi network
Morning, Afternoon, Night Kanji
Illegal assignment from sObject to Id
What is a fractional matching?
Do I really need to have a message in a novel to appeal to readers?
Hangman Game with C++
Is grep documentation about ignoring case wrong, since it doesn't ignore case in filenames?
Project Euler #1 in C++
How to write the following sign?
Find 108 by using 3,4,6
Selecting user stories during sprint planning
How to play a character with a disability or mental disorder without being offensive?
Is there any word for a place full of confusion?
MySQL 8.0 Error Code: 3617. Latitude is out of range in function st_geomfromtext
Planned maintenance scheduled April 23, 2019 at 00:00UTC (8:00pm US/Eastern)
Announcing the arrival of Valued Associate #679: Cesar Manara
Unicorn Meta Zoo #1: Why another podcast?Ogr2Ogr exported this boundary 90 degrees incorrectlyAggregate/dissolve MySQL geometryogr2ogr latitute longitude conversion formatsChange the actual coordinates in a shapefile (QGIS)Why would FME imported data into SQL Server not being found by QGIS?howto set ogr2ogr imported SRID to 0? Currently defaulting to SRID 1Run SQL queries against shapefile in QGIS?Importing Shapefile into PostGIS and EPSG of created table is different from Shapefile?Optimizing MySQL query to select all points with in a large polygon using spatial indexesFind all points near a point within a radius MySql 8.0 (SRID 4326)
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
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.
qgis shapefile ogr2ogr mysql-spatial
edited Apr 11 at 3:42
Vince
14.8k32850
asked Apr 11 at 0:30
stssts
12
add a comment |
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.
qgis shapefile ogr2ogr mysql-spatial
edited Apr 11 at 3:42
Vince
14.8k32850
asked Apr 11 at 0:30
stssts
12
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
add a comment |
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.
qgis shapefile ogr2ogr mysql-spatial
edited Apr 11 at 3:42
Vince
14.8k32850
asked Apr 11 at 0:30
stssts
12
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.
qgis shapefile ogr2ogr mysql-spatial
qgis shapefile ogr2ogr mysql-spatial
edited Apr 11 at 3:42
Vince
14.8k32850
asked Apr 11 at 0:30
stssts
12
edited Apr 11 at 3:42
Vince
14.8k32850
asked Apr 11 at 0:30
stssts
12
edited Apr 11 at 3:42
Vince
14.8k32850
edited Apr 11 at 3:42
Vince
14.8k32850
edited Apr 11 at 3:42
Vince
14.8k32850
14.8k32850
asked Apr 11 at 0:30
stssts
12
asked Apr 11 at 0:30
stssts
12
asked Apr 11 at 0:30
stssts
12
12
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
add a comment |
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
MySQL 8.0 changed the way it does indexing. See mysqlserverteam.com/geographic-indexes-in-innodb
– Mapperz♦
Apr 11 at 1:33
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
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
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
add a comment |
1 Answer
1
active
oldest
votes
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);
answered Apr 11 at 23:55
stssts
12
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "79"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f318438%2fmysql-8-0-error-code-3617-latitude-is-out-of-range-in-function-st-geomfromtext%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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);
answered Apr 11 at 23:55
stssts
12
add a comment |
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);
answered Apr 11 at 23:55
stssts
12
add a comment |
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);
answered Apr 11 at 23:55
stssts
12
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);
answered Apr 11 at 23:55
stssts
12
edited Apr 12 at 0:02
answered Apr 11 at 23:55
stssts
12
answered Apr 11 at 23:55
stssts
12
answered Apr 11 at 23:55
stssts
12
12
add a comment |
add a comment |
Thanks for contributing an answer to Geographic Information Systems Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f318438%2fmysql-8-0-error-code-3617-latitude-is-out-of-range-in-function-st-geomfromtext%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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