[SOLVED] MySQL: how to get lat/lgn values from GEOMETRY column?

MySQL: how to get lat/lgn values from GEOMETRY column?

If a series of latitude and longitude values are stored in a GEOMETRY column in MySQL, is there a way to get back those values once it's been stored as a geometry data type? Sample data:

CREATE TABLE poi_milan_fp
( idno INTEGER,
  name CHARACTER(80),
  ptyp CHARACTER(50),
  area FLOAT,
  gn GEOMETRY);

INSERT INTO poi_milan_fp (idno,name,ptyp,area,gn)
VALUES (11, 'Giardini pubblici', '02 - garden',0, 
PolygonFromText('POLYGON(( 
9.197915773 45.476819539,
9.195513514 45.472823358,
9.196395088 45.471905458,
9.197969097 45.470451939,
9.200557298 45.470952889,
9.201648922 45.472091272,
9.202453092 45.471989421,
9.204439767 45.474246289,
9.198739377 45.476916834,
9.197915773 45.476819539))')
);

http://sqlfiddle.com/#!9/023746/1

Solution

According to the manual, you can use the AsText() function. In 5.6 and later (I believe) you should use the ST_AsText() function though. The older name is deprecated in the latest versions.

SELECT ST_AsText(gn) FROM poi_milan_fp;