skip to Main Content

I have a POINT column to store coordinates, and here is my insert query:

INSERT INTO _coordinates_test (coordinates) VALUES(POINT(59.356125477261266, -2.8759361517393898))

But here is what i get on the column (coordinates):

   …•­M@“Ö(ÐêÀ

I also tried reading coordinates like this:

SELECT POINT(59.356125477261266, -2.8759361517393898)

I also tried converting a regular coordinates string to a POINT, like this:

SELECT ST_PointFromText(CONCAT('POINT(', '59.356125477261266 -2.8759361517393898' , ')'))

Here is the CREATE TABLE query:

CREATE TABLE `_coordinates_test` (
    `coordinates` POINT NULL DEFAULT NULL
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

The result is always the same, those strange characters.

Is there some issue with the encoding (currently utf8mb4_unicode_ci) or does POINT not support coordinates ?

Could it be that my MySQL Client software (HeidiSQL) is not reading the data correctly ?

MY USE CASE: Im trying to store coordinates in that column so i can calculate the distance (in meters) between different physical objects.

2

Answers


  1. Chosen as BEST ANSWER

    For some reason, the strange characters did actually represent the POINT value, and so it was not corrupted (i thought it was) but can be shown as text using ST_TEXT(coordinates).

    So:

    SELECT ST_ASTEXT(POINT(59.356125477261266, -2.8759361517393898))
    

    will show the correct value

    I can then manipulate the values as i want, using ST_X or ST_Y, and i can perform the measuring i need using ST_DISTANCE_SPHERE


  2. As per the MySQL official documentation, use ST_X() for x-coordinate and ST_Y() for y-coordinate when reading a Point data type.

    mysql> SELECT ST_X(Point(15, 20));
    15
    

    Refer to below for more details
    https://dev.mysql.com/doc/refman/5.7/en/gis-point-property-functions.html#function_st-x

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search