Insert and query MYSql point value returns ascii-CodePudding

Bit of a strange one. I’m trying to insert MySQL point coordinates and return in PHP, but I get ascii put instead of coordinates.

This is my SQL

CREATE TABLE `Grid` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `cntr_point` POINT DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

INSERT INTO `Grid`(cntr_point) 
VALUES(POINTFROMTEXT('POINT(49.227239 17.564932)'));

In PHP I have code such as this (which works for other fields)

$row['cntr_point']

But I get random ascii character output such as this:

*H@v0b1@

When I would instead be expecting to see the coordinates I inserted. I seem to get the same even when I skip PHP and use something like SQLfiddle:

SQLFiddle

Any help would be appreciated

CodePudding user response：

You can use ST_AsText:

SELECT id, ST_AsText(cntr_point) FROM Grid

Output: POINT(49.227239 17.564932)

Or you can use ST_X and ST_Y to get the coordinates:

SELECT id, ST_X(cntr_point), ST_Y(cntr_point) FROM Grid;

Fiddle

CodePudding user response：

The POINT data type contains binary data. Try ST_AsWkt() to get it displayed as text.

SELECT id, ST_AsWkt(cntr_point) FROM Grid

In MySQL's OpenGIS extension, geometric columns like your POINT column are stored in the so-called Well-Known Binary (WKB) format. ST_AsWkt() converts WKB to Well-Known Text (WKT).

The constant you use in your INSERT, 'POINT(49.227239 17.564932)', is an example of WKT. The POINTFROMTEXT() function you use to load your table converts the other way.

So, yes, if you want just the long/lat data, you'll have to extract it from the WKT in your php program. Or, you could use GeoPHP to handle WKB directly if that makes sense in your app.