Consider the following query:
SELECT ST_Simplify(SHAPE, 1.0) FROM nhdflowline WHERE OGR_FID = 12701;
Running that gives me the following error:
Error Code: 3618. st_simplify(LINESTRING, ...) has not been implemented for geographic spatial reference systems.
However, if I do this, it works just fine:
SELECT ST_Simplify(ST_GeomFromText(ST_AsText(SHAPE)), 1.0) FROM nhdflowline WHERE OGR_FID = 12701;
This doesn’t make any sense to me. I can’t run ST_Simplify directly on a geometry – I have to convert it to text and then back to a geometry to run ST_Simplify?
Am I missing something? I’m running MySQL 8.0.22.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
The key is in the last part of the error message: “has not been implemented for geographic spatial reference systems”.
There are two kinds of reference systems (SRID): geographic, with computations on sphere, and projected, with computations on plane map.
When you do ST_AsText, and then back to geometry, you remove SRID, so the geometry which used to be associated with geographic (i.e. spherical) geometry, now has planar semantics and SRID=0. It’s semantics changed for MySQL. What used to be lat:lng on sphere now are just two coordinates on plane.
Your version of MySQL implements simplify for planar (projected) geometry, but not for geographic SRID. You can use simplify on plane, but units of distance are different (usually degrees vs meters), as well as semantics (one degree along meridian is bigger in meters than one degree along parallel if you are far from equator).
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0