Working with coordinates in CodeIgniter 4 (or PHP)

Question

I am programming in CodeIgniter 4, and I don't know how to deal with the following problem using either CodeIgniter 4 or directly with PHP.

I have a database table places with one column coord with the position (coordinates) of each place, a MySQL column of type point. Thanks to CodeIgniter 4 I get all the columns, but in order to use the latitude and longitude separately I need to do:

select (*, st_x(coord) as lat, st_y(coord) as lon)
from ...

That way, I can use lat and lon. If I just use coord I get something without meaning.

Is there a way to do one of the following:

1. Work with that coord variable to get the latitude and longitude in PHP?
2. Add a function in the MySQL table to make that select(*) return also the lat and lon columns (calculated from st_x(coord) and st_y(coord))?

Answer 1

You could add virtual generated columns for lat and lon -

ALTER TABLE places
    ADD COLUMN lat DOUBLE GENERATED ALWAYS AS (ST_X(coord)) VIRTUAL,
    ADD COLUMN lon DOUBLE GENERATED ALWAYS AS (ST_Y(coord)) VIRTUAL;