'Save lat & lng values into point field in mysql

I have a table with fields latitude and longitude stored as float values, and I want to start saving them into points in a new column to use spatial features.

Currently, I have the values as following

  • Example latitude: 41.7298
  • Example longitude: -87.5995

If I want to start saving them as points, how should I do this? Do I need to convert them to radians? Degrees? Or what format? Also, when fetching them, how could I be doing so? Assume I have a column called "location" as point datatype. I have an InnoDB

mysqlgeospatialinnodb


Solution 1:[1]

Something like this:

ALTER TABLE t ADD COLUMN ll_point POINT;
UPDATE t SET ll_point = POINT(lng, lat);   -- Note: LNG first

After you have written your code to use ll_point instead of lat and lng, cleanup:

ALTER TABLE t DROP COLUMN lat,
              DROP COLUMN lng;

Lat/lng are in degrees, just as you have now.

Side note: there is no comma in

GeomFromText('POINT(6.905235 79.862687)')

See https://dev.mysql.com/doc/refman/8.0/en/gis-mysql-specific-functions.html

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Rick James

Related Questions