1

I tried to collaborate on this question from the Stack Overflow site.

The ST_Latitude and ST_Longitude functions (equal ST_X and ST_Y) of MySQL (version 8.0.17) returns a non-exact value for certain coordinates (e.g.: -31, -61 or -62). At first I thought it could be a bug in this MySQL functions and I reported it in bugs.mysql.com, however, from MySQL they indicate that it is not a bug and that I must read in depth the GIS standard.

DROP TABLE IF EXISTS `tbl_table`;

CREATE TABLE IF NOT EXISTS `tbl_table` (
  `random_point` GEOMETRY
);

SET @`SRID` := 4326;

INSERT INTO `tbl_table`
VALUES
  (ST_GeomFromText('POINT(-30 -31)', @`SRID`)),
  (ST_GeomFromText('POINT(-32 -33)', @`SRID`)),
  (ST_GeomFromText('POINT(-60 -61)', @`SRID`)),
  (ST_GeomFromText('POINT(-62 -63)', @`SRID`));

SELECT
  ST_AsGeoJSON(`random_point`) `geojson`,
  ST_Latitude(`random_point`) `latitude`,
  ST_Longitude(`random_point`) `longitude`,
  ST_X(`random_point`) `x`,
  ST_Y(`random_point`) `y`
FROM `tbl_table`;

geojson                                          |           latitude |           longitude |                  x |                   y
:----------------------------------------------- | -----------------: | ------------------: | -----------------: | ------------------:
{"type": "Point", "coordinates": [-31.0, -30.0]} |                -30 | -30.999999999999996 |                -30 | -30.999999999999996
{"type": "Point", "coordinates": [-33.0, -32.0]} |                -32 |                 -33 |                -32 |                 -33
{"type": "Point", "coordinates": [-61.0, -60.0]} |                -60 |  -61.00000000000001 |                -60 |  -61.00000000000001
{"type": "Point", "coordinates": [-63.0, -62.0]} | -61.99999999999999 |                 -63 | -61.99999999999999 |                 -63

See dbfiddle.

I understand that MySQL, MariaDB and PostgreSQL use the Open Geospatial Consortium (OGC) specification and after reading the specification anyway I cannot determine the reason why some coordinates do not return the exact value in MySQL.

Both MariaDB and PostgreSQL return the exact value of the same coordinates as demonstrated in the following examples: MariaDB (version 10.4.8) and PostgreSQL (version 11.5).

Has anyone faced the same problem before?.

  • 1
    Looks like a simple floating point rounding issue, I don't think the simple feature spec says anything about that. – Ian Turton Oct 13 '19 at 10:33
  • 3
    Coordinates are inherently floating-point values. While the most common error is applying to many digits of reprenstation, zero place coordinate representation is of little value ( obligatory xkcd reference ). IEEE floating-point representation can only store so many discrete values, so expecting perfect translation past 5 places in unformatted values is a mistake (note that this is a generic IT issue, and has nothing to do with MySQL) – Vince Oct 13 '19 at 12:15
  • 1
    @Vince: So will it depend on how each database engine (MySQL, MariaDB, PostgreSQL, others) handles the floating point values?. Thanks for the comment. – wchiquito Oct 13 '19 at 12:38
  • 1
    No, it depends on how end-users such as yourself utilize the raw values correctly returned by the database. If you explicitly format the value to six or seven decimal places, the -30.999999999999996 is going to be represented as -31.000000, but because 31 is a prime number, IEEE-754 representation cannot represent it as a whole number. – Vince Oct 13 '19 at 19:00
  • 1
    If you use the floating point converter h-schmidt.net/FloatConverter/IEEE754.html and test with number -31 then if feels that MySQL is doing something odd. Value -31 can be expressed accurately as 11000001111110000000000000000000 and one bit less 11000001111101111111111111111111 is -30.9999980926513671875 as a decimal number. Anyway, the difference is not meaningful and floating point rounding errors belong to computing. Issue has nothing to do with the OGC standards. – user30184 Oct 14 '19 at 9:56