MySQL Spatial - Convert Point from EPSG 4326 to 25831

Question

I'm trying to learn how geospatial fields work in MySQL (5.7.20).

I have a table like:

CREATE TABLE `geom` (
  `g` geometry NOT NULL,
  SPATIAL KEY `g` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I had inserted this info:

INSERT INTO geom VALUES (ST_PointFromText('POINT(2.427475 41.534244)', 4326));
INSERT INTO geom VALUES (ST_PointFromText('POINT(2.428602 41.533272)', 4326));
INSERT INTO geom VALUES (ST_PointFromText('POINT(2.430147 41.534075)', 4326));
INSERT INTO geom VALUES (ST_PointFromText('POINT(2.429321 41.535191)', 4326));

When I run:

SELECT * FROM geom;

All I get is four rows with a BLOB field.

When I run:

SELECT ST_AsText(g) FROM geom;

I get the same as I had inserted:

POINT(2.429321 41.535191)
POINT(2.430147 41.534075)
POINT(2.428602 41.533272)
POINT(2.427475 41.534244)

And now I'm trying to convert the output coordenates from EPSG:4326 to EPSG:25831.

I found "MySQL Spatial - Convert from SRID 31287 to 4326" and tried it in a select instead of an insert but changing the SRID:

SELECT ST_AsText(g), ST_SRID(g), ST_AsText(ST_GeomFromText(ST_AsText(g), 25831)), ST_SRID(ST_GeomFromText(ST_AsText(g), 25831)) FROM geom;

And I get:

ST_AsText(g)                ||  ST_SRID(g)  ||  ST_AsText(ST_GeomFromText(ST_AsText(g), 25831)) ||  ST_SRID(ST_GeomFromText(ST_AsText(g), 25831))
POINT(2.429321 41.535191)   ||  4326        ||  POINT(2.429321 41.535191)                       ||  25831
POINT(2.430147 41.534075)   ||  4326        ||  POINT(2.430147 41.534075)                       ||  25831
POINT(2.428602 41.533272)   ||  4326        ||  POINT(2.428602 41.533272)                       ||  25831
POINT(2.427475 41.534244)   ||  4326        ||  POINT(2.427475 41.534244)                       ||  25831

So the result from a ST_AsText(ST_GeomFromText(ST_AsText(g), 25831)) gets me the same coordinate as the inserted value.

What I am trying to get is the conversion from EPSG:4326 to EPSG:25831. Something like (or at least the coordinates):

POINT(452240.56 4598224.20)
POINT(452333.86 4598115.66)
POINT(452463.33 4598203.96)
POINT(452395.25 4598328.31)

What I'm doing wrong?

Answer 1

MySQL doesn't have any SRID awareness. So this is simply impossible. To the extent that it supports anything SRID, it'll just reject calculations if the features have a different SRID.

What you want is PostGIS, which does this just fine with ST_Transform. As a bonus, you get a better database.

SELECT ST_Transform(pt,31287)
FROM ST_SetSRID(ST_MakePoint(2.430147,41.534075), 4326) AS pt;

Answer 2

As @EvanCarroll answered, MySQL doesn't have SRID awareness (at the moment). But if you need it and are using PHP I've found a class to convert coordinates between SRID's proj4php (disclaimer: I'm no related with this project) and works perfect.