phpmyadmin, MariaDB 10 and Point columns

Question

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

I removed the phpmyadmin tag. It's just the client program. Using it is like using a Firefox tag on a question about Stack Overflow because you viewed it with that browser. — O. Jones, Oct 12 '21 at 22:59

m33ts4k0z · Accepted Answer · 2021-10-19T05:15:54.073

1

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

edited Oct 19 '21 at 05:15

answered Oct 12 '21 at 23:29

m33ts4k0z

645
7
26

O. Jones · Answer 2 · 2021-10-12T23:04:31.293

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

Thanks but as I said, I also have a way to do that using a query. I wonder why the latest version of PHPMyAdmin doesn't work through user interface for doing the same. — m33ts4k0z, Oct 12 '21 at 23:00

phpmyadmin, MariaDB 10 and Point columns

2 Answers2