MySQL Bugs: #98731: st_asgeojson does not work as expected

Bug #98731	st_asgeojson does not work as expected
Submitted:	25 Feb 2020 8:47	Modified:	28 Feb 2020 8:16
Reporter:	Vincent GUTH	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: GIS	Severity:	S3 (Non-critical)
Version:	8.0.19	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
Hi,

This SQL query:
SELECT ST_AsGeoJSON(ST_GeomFromText('Polygon((0 1, 1 2, 2 1, 0 1))', 4326))

Shows:
{"type": "Polygon", "coordinates": [[[1.0, 0.0], [2.0, 1.0], [1.0, 2.0], [1.0, 0.0]]]}

According to GeoJSON RFC (https://tools.ietf.org/html/rfc7946):
"Point coordinates are in x, y order (easting, northing for projected coordinates, longitude, and latitude for geographic coordinates)"

So expected output is:
{"type": "Polygon", "coordinates": [[[0.0, 1.0], [1.0, 2.0], [2.0, 1.0], [0.0, 1.0]]]}

Longitude and latitude should be reversed.

Thank you

How to repeat:
Run the SQL query:
SELECT ST_AsGeoJSON(ST_GeomFromText('Polygon((0 1, 1 2, 2 1, 0 1))', 4326))

Hello Vincent,

Thank you for the report and test case.
Verified as described with 8.0.19 build.

regards,
Umesh

- 8.0.19 

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT ST_AsGeoJSON(ST_GeomFromText('Polygon((0 1, 1 2, 2 1, 0 1))', 4326))
    -> ;
+----------------------------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('Polygon((0 1, 1 2, 2 1, 0 1))', 4326))                   |
+----------------------------------------------------------------------------------------+
| {"type": "Polygon", "coordinates": [[[1.0, 0.0], [2.0, 1.0], [1.0, 2.0], [1.0, 0.0]]]} |
+----------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

- 5.7.29 (seems regression in 8.0.19)

bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT ST_AsGeoJSON(ST_GeomFromText('Polygon((0 1, 1 2, 2 1, 0 1))', 4326));
+------------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('Polygon((0 1, 1 2, 2 1, 0 1))', 4326))   |
+------------------------------------------------------------------------+
| {"type": "Polygon", "coordinates": [[[0, 1], [1, 2], [2, 1], [0, 1]]]} |
+------------------------------------------------------------------------+
1 row in set (0.07 sec)

Posted by developer:
 
Hi Vincent,

ST_GeomFromText correctly reports longitude as longitude and latitude as latitude. The polygon is created from WKT with points (lat 0 long 1), (lat 1 long 2), (lat 2 long 1), (lat 0 long 1). MySQL follows the OGC Axis Order Policy Guidance (OGC 08-38r5), so unless the format defines an explicit axis order (which WKT doesn't, but GeoJSON does), the spatial reference system axis order is used. This means that your ST_GeomFromText statement takes lat-long order, while ST_AsGeoJSON produces long-lat order.

If you use the axis-order parameter to change the interpretation of the WKT string, you can get the result you want:

Default:

mysql> SELECT ST_AsGeoJSON(ST_GeomFromText('Polygon((0 1, 1 2, 2 1, 0 1))', 4326));
+----------------------------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('Polygon((0 1, 1 2, 2 1, 0 1))', 4326))                   |
+----------------------------------------------------------------------------------------+
| {"type": "Polygon", "coordinates": [[[1.0, 0.0], [2.0, 1.0], [1.0, 2.0], [1.0, 0.0]]]} |
+----------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

axis-order=srid-defined is the default:

mysql> SELECT ST_AsGeoJSON(ST_GeomFromText('Polygon((0 1, 1 2, 2 1, 0 1))', 4326, 'axis-order=srid-defined'));
+-------------------------------------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('Polygon((0 1, 1 2, 2 1, 0 1))', 4326, 'axis-order=srid-defined')) |
+-------------------------------------------------------------------------------------------------+
| {"type": "Polygon", "coordinates": [[[1.0, 0.0], [2.0, 1.0], [1.0, 2.0], [1.0, 0.0]]]}          |
+-------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

SRID 4326 comes from EPSG, and all EPSG SRSs are lat-long order, so the SRID default is the same as lat-long:

mysql> SELECT ST_AsGeoJSON(ST_GeomFromText('Polygon((0 1, 1 2, 2 1, 0 1))', 4326, 'axis-order=lat-long'));
+---------------------------------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('Polygon((0 1, 1 2, 2 1, 0 1))', 4326, 'axis-order=lat-long')) |
+---------------------------------------------------------------------------------------------+
| {"type": "Polygon", "coordinates": [[[1.0, 0.0], [2.0, 1.0], [1.0, 2.0], [1.0, 0.0]]]}      |
+---------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

You can override the default and set axis-order=long-lat:

mysql> SELECT ST_AsGeoJSON(ST_GeomFromText('Polygon((0 1, 1 2, 2 1, 0 1))', 4326, 'axis-order=long-lat'));
+---------------------------------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('Polygon((0 1, 1 2, 2 1, 0 1))', 4326, 'axis-order=long-lat')) |
+---------------------------------------------------------------------------------------------+
| {"type": "Polygon", "coordinates": [[[0.0, 1.0], [1.0, 2.0], [2.0, 1.0], [0.0, 1.0]]]}      |
+---------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

I hope that explains what you're seeing.

Closing as not a bug.

Posted by developer:
 
Sorry, that should be "ST_AsGeoJSON correctly reports longitude as longitude and latitude as
latitude."

(ST_GeomFromText also works correctly, but that wasn't the point here.)

Thank you very much for your answer, and sorry for having taking your time!