0

I have a table in MySQL with geometry data in one of the columns. The datatype is text and I need to save it as Polygon.

I have tried a few solutions, but keep running into the error

Invalid GIS data provided to function st_polygonfromtext.

Sample data to reproduce the error: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=78ac63e16ccb5b1e4012c21809cba5ff

Someone suggested using wrapping the statements around TRY and CATCH. https://stackoverflow.com/questions/35616177/detecting-faulty-geometry-wkt-and-returning-the-faulty-record

I am not too familiar with using them in MySQL or stored procedures either. Can anyone suggest a way to get around invalid geometries?

I need a spatial index on the table to be able to use spatial functions and filter queries by location.

Improve this question
4
  • 1
    Please include a data sample that exhibits the problem in the Question, since links aren't generally followed. When in doubt, it's probably safe to assume that the constructor function knows its requirements better than you do.
    – Vince
    Commented Jun 1, 2022 at 3:32
  • @Vince - it's a wide and long dataset, and can tricky with wide polygon geometries. With csv, one can quickly glance at the data and get a sense for it.
    – kms
    Commented Jun 1, 2022 at 3:53
  • 2
    I am pretty sure that you could make a small and simple dataset that shows the same problem. I guess that POINT (-100, 60) is large enough (requires naturally ST_PointFromText).
    – user30184
    Commented Jun 1, 2022 at 8:26
  • @user30184 I added a one row of data and had to truncate the polygon because it exceeded the length of body text allowed.
    – kms
    Commented Jun 1, 2022 at 22:35

1 Answer 1

Reset to default
1

Your WKT begins POLYGON ((-106.667521 35.081591, -106.667395 35.081503, -106.66677... so it has coordinates in longitude-latitude order. That's how many, if not most GIS databases and programs (including PostGIS) understand WKT. However, MySQL and some others have decided to make WKT aware of coordinate system and WKT that presents EPSG:4326 data should have coordinates in latitude-longitude order. With that interpretation value -106.667521 is invalid because latitude cannot be that low.

Read for example https://dev.mysql.com/blog-archive/axis-order-in-spatial-reference-systems/ and use the axis-order option “long-lat”.

Improve this answer
1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – PolyGeo ♦
    Commented Jun 3, 2022 at 0:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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