Retrieving the coordinates of the MySQL point type

Question

I'm storing lat/long pairs in MySQL as a point using something like:

GeomFromText('POINT(32 -122)')

Given the point, how do i retrieve the individual X/Y coordinates?

Answer 1

Let's say you store GeomFromText('POINT(32 -122)') as a column called MY_POINT in a table called MY_TABLE.

Getting the X coordinate (will return 32 in this example):

SELECT ST_X(MY_POINT) as longitude FROM MY_TABLE;

Getting the Y coordinate (will return -122 in this example):

SELECT ST_Y(MY_POINT) as latitude FROM MY_TABLE;

Important: Prior to version 5.6, use X() and Y() instead of ST_X() and ST_Y().

Answer 2

Let's say you store GeomFromText('POINT(32 -122)') as a column called MY_POINT in a table called MY_TABLE. There are several ways to get the point data.

• First Check MySQL version

  SELECT VERSION()

• For MySQL 5.6 or older version using

  SELECT X(MY_POINT) AS Latitude, Y(MY_POINT) AS Longitude FROM MY_TABLE

• For MySQL 5.7 version using, although previous command also recognized.

  SELECT ST_X(MY_POINT) AS Latitude, ST_Y(MY_POINT) AS Longitude FROM MY_TABLE

• For MySQL 8.0 version using, although previous command also recognized. The main difference is this command for Point objects that have a geographic spatial reference system (SRS).

  SELECT ST_Latitude(MY_POINT) AS Latitude, ST_Longitude(MY_POINT) AS Longitude FROM MY_TABLE

Hope this would helps.