1

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?

O. Jones
  • 103,626
  • 17
  • 118
  • 172
timlwsk
  • 121
  • 3
  • 14
  • Is there a specific reason you aren't simply storing those two separate values in the database so the raw data can be supplied in whichever form you desire when the application needs it? – El_Vanja Mar 30 '21 at 14:02
  • No, there isn't - I thought Point is intended for location data? – timlwsk Mar 30 '21 at 14:03
  • I always prefer storing data independently of how it's being used. Gives you flexibility for the future. But it really depends on your use case. What kind of operations are you looking to perform on this data? – El_Vanja Mar 30 '21 at 14:07
  • I will later use this data to show datrabase entries on a leaflet.js map – timlwsk Mar 30 '21 at 14:10
  • I haven't used that library, but as far as I can see from the demo, coordinates are supplied separately. So I would advocate storing them separately. Makes little sense to receive separate coordinates, join them into a point, only to extract individual coordinates from the point. – El_Vanja Mar 30 '21 at 14:13
  • Okay, then this question is answered. Thanks – timlwsk Mar 30 '21 at 14:14
  • Hmmm, somehow when I try to add `$latitude = $jsonData[0]['lat']);` and `$longitude = $jsonData[0]['lon']);` the PHP breaks - any ideas? – timlwsk Mar 30 '21 at 14:22
  • The trailing parenthesis? – El_Vanja Mar 30 '21 at 14:22
  • please imagine my facepalm right now. – timlwsk Mar 30 '21 at 14:24

1 Answers1

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172