Question 267648 in Mysql Question posted in
The official MySQL documentation can be found here.

how to set GEOMETRY in MySQL using JSON

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

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


  2. 0

    Your question is not clear. 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');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search