[SOLVED] MySQL Spatial - Convert Point from EPSG 4326 to 25831

MySQL Spatial - Convert Point from EPSG 4326 to 25831

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?

Solution

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;