skip to Main Content

Trying to edit a column of a table in MariaDB using PHPmyAdmin v5.1.1 gives me trouble.
It saves the column as binary and I need to edit the whole row in order to be able to edit it as text. Even doing so, when choosing Edit/Insert next to the appropriate field, I get this copied:

'POINT(0 0)',0

However this is not compatible with MariaDB 10. The only thing that works so far is using a raw query for updating the field like this:

UPDATE `locations` SET `point` = POINT(1, 2) WHERE `locations`.`id` = 169;

My question is, isn’t there a way of updating the field through the UI of PHPMyAdmin instead of running manual queries?

Trying to be more specific. This is how the column looks like in PHPMyAdmin:

This

And this is how it looks like when editing the row:

this

Note that because its WKB, it cant be edited directly. However when adding to the point field this:

POINT(1, 1)

PHPMyAdmin changes it to:

'POINT(1, 1)'

And it doesnt work.

Also the comma is needed otherwise MariaDB throws an error:

#1416 - Cannot get geometry object from data you send to the GEOMETRY field

2

Answers


  1. Chosen as BEST ANSWER

    Well this looks like a bug in the PHPMyAdmin for the newer versions of MySQL and MariaDB. It is described in this very recent article here

    Indeed a fix is already deployed and will be available in PHPMyAdmin 5.1.2


  2. If you use the ST_PointFromText() function you can take Well-Known Text WKT format text input you show in your example. and turn it into the Well-Known Binary WKB format suitable for stashing in a WKB format column in a table. Something like this.

    UPDATE locations 
       SET point = ST_PointFromText('POINT(1 2)') 
     WHERE whatever
    

    Modern versions of MySQL and MariaDB both have this.

    Notice that POINT(1 2) doesn’t have a comma separating the two numbers. Use commas between points, like this.

    LineString(1 1, 1 2, 2 2)
    

    If you want WKT (the text format) use ST_AsText(). That will make it possible for text-based client programs to display your geometric data.

    SELECT ST_AsText(point)
      FROM locations
     WHERE whatever
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search