How can I store two API values in MYSQL Point

Question

I receive lan and lot data from an API in PHP and extract them from the response with ($jsonData[0]['lat']); and $jsonData[0]['lon']);. How can I add them two my MySQL column location as a POINT value?

Answer 1

MySQL's geometry stuff offers the ST_GeomFromText() function. You can use it like this.

ST_GeomFromText('POINT(40.7488 -73.9854)')

Notice that 'POINT(40.7488 -73.9854)' is a text string, and that there's no comma between the two numbers.

If you do something like this to make one of those text strings

$pointText = "POINT(" . $jsonData[0]['lat'] . " " .  $jsonData[0]['lon'] . ")";

then you can do an insert like this:

INSERT INTO tbl (geocolumn) VALUES (ST_GeomFromText(?));

with $pointText as the parameter.

With MySQL versions prior to 8, I agreed with @El_Vanja's suggestion of using separate FLOAT columns for lat / long. But with version 8, MySQL has the ability to handle spherical as well as cartesian coordinate systems, so the spatial index can be very useful.