Bug #94382 MySQL workbench 8.0 shows inverted geospatial coordinates
Submitted: 18 Feb 2019 15:21 Modified: 19 Feb 2019 6:58
Reporter: Jan Waldner Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:8.0, 8.0.15 OS:Windows
Assigned to: CPU Architecture:x86

[18 Feb 2019 15:21] Jan Waldner
Description:
I have a table `areas` defined as follows:

CREATE TABLE `areas` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `boundary` polygon NOT NULL,
  `description` varchar(45) DEFAULT NULL,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

I perform the following statement:

INSERT INTO areas (name, description, boundary) VALUES ('234', '234', ST_GeomFromText('POLYGON ((0 0,0 1,1 1,1 0,2 0,0 0))', 4326));

Looking at the resulting column with the data editor in WKT, it shows

POLYGON ((0 0,1 0,1 1,0 1,0 2,0 0))

all coordinates are inverted.

Using

SELECT ST_astext(boundary) FROM areas WHERE id = 1;

correctly returns

POLYGON((0 0,0 1,1 1,1 0,2 0,0 0))

Not sure how to interpret these results.

How to repeat:
CREATE TABLE `areas` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `boundary` polygon NOT NULL,
  `description` varchar(45) DEFAULT NULL,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO areas (name, description, boundary) VALUES ('234', '234', ST_GeomFromText('POLYGON ((0 0,0 1,1 1,1 0,2 0,0 0))', 4326));

Look at the resulting column with the data editor in WKT, it shows inverted coordinates for each point.
[19 Feb 2019 6:58] MySQL Verification Team
Hello Jan Waldner,

Thank you for the report.

regards,
Umesh
[22 Feb 2021 10:06] Mx Dog
I have a very similar issue since this one is so old,

a column is set as geometry 4326 data in the column is in latitude,longitude order  @ 1 point per row EXAMPLE...  ST_AsText(GeoLoc)
POINT (41.718692846098243 -86.892960902249271)
when running a query on that column what is returned is randomly? flip-flopped

EXAMPLE... ST_AsText(GeoLoc)
POINT(-86.89296090224927 41.71869284609824)

the randomness is shown here ...

EX.
-86.89296090224927, 41.71869284609824, 41.71869284609824, -86.89296090224927, 9231.55

where one row is inverted and the other was correct. 

the query for that last was . 

Select  @Destination := ST_GeomFromText( ST_AsText(GeoLoc), 4326,'axis-order=lat-long') from bleah bleah... with GeoLoc holding the geometry Blob.

the problem seems to be the function not handling the array correctly and it seems to be the ST_AsText returning the bogus values. OR there is a translation problem from what the server is storing internally and what the function is reporting back ? 

AND it is not workbench as I get bogus results from the command line also 

EXAMPLE: Select  @Destination := ST_AsText(GeoLoc)  from....

+----------------------------------------------+
| @Destination := ST_AsText(GeoLoc)            |
+----------------------------------------------+
| POINT(-86.16231096249841 41.945720369297725) |
+----------------------------------------------+
again backwards...