phpmysqlgeospatialopengis

How can I store two API values in MYSQL Point

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?

Solution

  • 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.