phpmyadmin, MariaDB 10 and Point columns-CodePudding

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:

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

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

CodePudding user response：

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

CodePudding user response：

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