I have a bit of a strange problem. 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:
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.