Insert and query MySQL point value returns ASCII

user_1738522 0
Viewed 168

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:

SQLFiddle

geophp geospatial coordinates mysql php
2 Answers

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

user_176450990

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.

user_2056080
Related