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

Answer 2

If your table currently has a JSON column containing the latitude and longitude you could use something like this:

tbl

id	json_point	new_point
1	{"latitude": "51.5072", "longitude": "0.1276"}

UPDATE tbl SET new_point = POINT(json_point->>'$.longitude', json_point->>'$.latitude');

If json_point contains a JSON array:

id	json_point	new_point
1	[0.1276, 51.5072]

you could update with:

UPDATE tbl SET new_point = POINT(json_point->'$[0]', json_point->'$[1]');