how to set GEOMETRY in MySQL from coordinates of JSON_ARRAY

Question

I want to update my dataset and use the GEOMETRY datatype instead of using latitude and longitude. The problem is I have set a geo column in my table with the datatype yet I can't put my data in it. how to set its value?

I searched through other questions and all of them use text and I wanted to transform from JSON.

Answer 1

To update the field, we need to follow this format first we create a JSON that has type and coordinates and then transform it to GEO. Also, note that coordinates are in the format of JSON array.

here is an example for type Point and coorinates of (5, 1)

ST_GeomFromGeoJSON(JSON_OBJECT('type', 'Point', 'coordinates', JSON_ARRAY(5, 10)))