| 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: | |
| Category: | MySQL Workbench: SQL Editor | Severity: | S3 (Non-critical) |
| Version: | 8.0, 8.0.15 | OS: | Windows |
| Assigned to: | CPU Architecture: | x86 | |
[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...
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.