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: | |
Category: | MySQL Server: GIS | Severity: | S3 (Non-critical) |
Version: | 8.0.19 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | regression |
[25 Feb 2020 8:47]
Vincent GUTH
[25 Feb 2020 14:15]
MySQL Verification Team
Hello Vincent, Thank you for the report and test case. Verified as described with 8.0.19 build. regards, Umesh
[25 Feb 2020 14:16]
MySQL Verification Team
- 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)
[25 Feb 2020 14:16]
MySQL Verification Team
- 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)
[26 Feb 2020 9:55]
Norvald Ryeng
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.
[26 Feb 2020 9:59]
Norvald Ryeng
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.)
[28 Feb 2020 8:16]
Vincent GUTH
Thank you very much for your answer, and sorry for having taking your time!