Spatial Data does not render correctly in MySQL 8 with geographic SRIDs

[btacordex]

Describe the bug

After upgrading to MySQL 8, using data with a geographic SRID (specifically, 4326) causes phpMyAdmin to behave incorrectly when visualizing GIS data.

To Reproduce

Steps to reproduce the behavior:

1. Have a table with properly formatted spatial data in a geographic SRS.
2. Select some data from the table.
3. Click the "Visualize GIS Data" button below.
4. The map may temporarily draw something that appears to be a shape, but then blanks out.

Expected behavior

One would expect the shape to be rendered correctly.

Server configuration

• Operating system: CentOS 8
• Web server: Apache 2.4
• Database version: 8.0.18
• PHP version: 7.3.13
• phpMyAdmin version: 5.0.1

Client configuration

• Browser: Safari 13.0.5
• Operating system: Mac OS X 10.15.3

Additional context

This happens specifically when using a geographic SRS. It's worth noting that MySQL 8 uses and analyzes the SRID much more so than 5.7 did. In MySQL 5.7, the SRID was essentially ignored, so that if one wanted to store the location of the Topeka Zoo in Topeka, KS, for example, you would store it as ST_GeomFromText('POINT(-95.726760 39.055857)'), with the X coordinate (longitude) first, followed by the Y coordinate (latitude). It treated all coordinate systems as Cartesian coordinates with SRID 0. MySQL 5.7 at least checked that all SRIDs involved in an operation were the same, but did not otherwise use it. MySQL 5.6 and earlier versions didn't even do that.

MySQL 8 has a better understanding of SRSes, and when using an SRS system that represents geographic coordinates, rather than Cartesian coordinates, it expects points in the regular format: latitude, then longitude. So the Topeka Zoo would be created as ST_GeomFromText('POINT(39.055857 -95.726760)', 4326). (If you were to try to store geometric data using the 5.7 method of X, then Y, MySQL 8 complains that it's not valid GIS data for the spatial reference system specified, because there's no such thing as 95°S.) But however phpMyAdmin is retrieving the data and sending it to the map, I don't think it understands this distinction. It's appears to be treating MySQL 8 geographic data as Cartesian data, which is probably why it starts drawing a shape, gets to a point that's more than 90°W, then craps out because that doesn't make sense, all while pointing the map at Antarctica. It's getting the latitude and longitude flipped.

[williamdes]

Thank you for report and details
I really appreciated
Could you send a test table with test data?

[btacordex]

Here's a dump file that should recreate it. Looking at the data in WKT format, it looks like it doesn't actually hit 90°W, but it does come pretty close.

-- phpMyAdmin SQL Dump
-- version 5.0.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Mar 04, 2020 at 01:53 PM
-- Server version: 8.0.18
-- PHP Version: 7.3.13

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `imaging`
--

-- --------------------------------------------------------

--
-- Table structure for table `mdzip2`
--

CREATE TABLE `mdzip2` (
  `id` int(11) NOT NULL,
  `SHAPE` geometry NOT NULL,
  `zip` varchar(7) NOT NULL,
  `centerLat` float(9,7) NOT NULL,
  `centerLon` float(10,7) NOT NULL,
  `city` varchar(51) NOT NULL COMMENT 'copied from mdZip',
  `ST` varchar(2) NOT NULL COMMENT 'copied from mdZip',
  `tzName` varchar(80) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `mdzip2`
--

INSERT INTO `mdzip2` (`id`, `SHAPE`, `zip`, `centerLat`, `centerLon`, `city`, `ST`, `tzName`) VALUES
(1, 0xe6100000010600000002000000010300000001000000af0000006118b0e42a1056c0b4c876be9f764040e0be0e9c331056c0593332c85d76404012f6ed24221056c0e4bd6a65c2754040ad6ef59cf40f56c048fab48afe744040df37bef6cc0f56c09aec9fa701754040cac51858c70f56c057d0b4c4ca74404049dbf813950f56c0eb3713d38574404017b83cd68c0f56c081cd397826744040596dfe5f750f56c02942ea76f6734040695721e5270f56c079b0c56e9f7340400fd253e4100f56c01afa27b858734040af21382ee30e56c0c9586dfe5f734040c9772975c90e56c008ca6dfb1e73404002a08a1bb70e56c0e97e4e417e724040849ecdaacf0e56c0f19d98f562724040439259bdc30e56c0651c23d9237240408dd13aaa9a0e56c01b0fb6d8ed714040c6bfcfb8700e56c0ea9788b7ce7140409010e50b5a0e56c029eca2e8817140407f6b274a420e56c04dd9e90775714040abcb2901310e56c07de9edcf45714040cd936b0a640e56c01a19e42ec27040405fed28ce510e56c0bef73768af704040f29a5775560e56c04c512e8d5f704040657094bc3a0e56c0a375543541704040d865f84f370e56c05d8940f50f70404043705cc64d0e56c0ebc5504eb46f40408ae42b81940e56c0f8e28bf6786f4040b9fab1497e0e56c04208c897506f4040ce1b2785790e56c0c1a90f24ef6e4040c119fcfd620e56c0a05225cade6e4040e9d32afa430e56c058569a94826e4040580394861a0e56c0938fdd054a6e4040c66cc9aa080e56c0b4e73235096e4040b83f170d190e56c0e4839ecdaa6d404062c092ab580e56c020990e9d9e6d404074d0251c7a0e56c02849d74cbe6d4040a7203f1bb90e56c07c629d2adf6d40401d5723bbd20e56c0ec6cc83f336e4040d027f224e90e56c0cc608c48146e4040ae2d3c2f150f56c005fd851e316e404001bf4692200f56c0ebfeb1101d6e4040e6eac726f90e56c090a4a487a16d40402e71e481c80e56c0849f38807e6d4040addd76a1b90e56c07fbf982d596d40405ebbb4e1b00e56c0f0332e1c086d40403a4030478f0e56c0dc2c5e2c0c6d404061e0b9f7700e56c0137f1475e66c4040d49b51f3550e56c0a5f9635a9b6c4040e42d573f360e56c039d55a98856c40409816f549ee0d56c02829b000a66c40408d96033dd40d56c0aeba0ed5946c40401a33897ac10d56c08b8d791d716c404039d1ae42ca0d56c05682c5e1cc6b404058ad4cf8a50d56c00ec00644886b4040a260c614ac0d56c09f7422c1546b4040a5db12b9e00d56c0cbbbea01f36a40407903cc7c070e56c09757aeb7cd6a4040637ac2120f0e56c0971de21fb66a404052d2c3d0ea0d56c0661536035c6a4040cf68ab92c80d56c07bdcb75a276a4040a20914b1880d56c00a86730d336a4040018a9125730d56c01ff5d72b2c6a404059130b7c450d56c0eb39e97de36940406a882afc190d56c022516859f76940408eb0a888d30c56c07670b037316a4040e38dcc237f0c56c0054f2157ea694040e606431d560c56c09947fe60e06940408690f3fe3f0c56c01cec4d0cc9694040217711a6280c56c0863b17467a694040c845b588280c56c0df701fb9356940409a7d1ea33c0c56c055bfd2f9f0684040d8f0f44a590c56c032923d42cd684040ae80423d7d0c56c0ec3026fdbd68404040170d198f0c56c0013274eca06840405e4a5d328e0c56c08eaed2dd756840405f44db31750c56c0dbdd03745f684040228b34f10e0c56c0bbeead484c684040ea25c632fd0b56c003eca35357684040ce177b2fbe0b56c0605793a7ac6840404c50c3b7b00b56c06284f068e3684040c3802557b10b56c06bd619df176940403f8ba548be0b56c0713b342c46694040245f09a4c40b56c04628b682a669404098512cb7b40b56c06f63b323d56940401d1ec2f8690b56c0b24b546f0d6a4040dc82a5ba800b56c01381ea1f446a4040e2e47e87a20b56c0e4a3c519c36a40401de38a8ba30b56c0b8cb7edde96a40409dbaf2599e0b56c06fbda607056b40409a42e735760b56c027a25f5b3f6b40401afd6838650b56c00551f701486b40408dd31055f80a56c074d190f1286b40400470b378b10a56c0de3cd521376b4040cf842689250a56c0c617edf1426c4040d5b0df13eb0956c0eacda8f92a6d4040304ad05fe80956c0a1a17f828b6d4040e161da37f70956c0c5e40d30f36d4040be2d58aa0b0a56c0c0cfb870206e4040f20c1afa270a56c01e8d43fd2e6e4040e9edcf45430a56c049d92269376e4040e1455f419a0a56c043ff04172b6e40404f0647c9ab0a56c08dd2a57f496e40405039268bfb0a56c048179b560a6f4040f4bf5c8b160b56c04b5b5ce3336f404015191d90840b56c0d3dee00b936f4040ccf09f6ea00b56c0c576f700dd6f4040a85489b2b70b56c0917efb3a7070404014252191b60b56c0f56915fda1714040fe7e315bb20b56c0f73e55850672404012bd8c62b90b56c0a0c211a4527240404012f6ed240c56c0083a5ad592744040231631ec300c56c07cd45fafb0744040e868554b3a0c56c06a865451bc744040e25817b7d10c56c083328d2617754040dac70a7e1b0d56c0f96706f1817540409c6a2dcc420d56c07bbfd18e1b76404029417fa1470d56c09ab4a9ba47764040e547fc8a350d56c02619390b7b764040809a5ab6d60c56c014d044d8f0764040cc7edde9ce0c56c0dffc86890677404088855ad3bc0c56c089d00836ae7740405051f52b9d0c56c07c28d192c7774040f6eb4e779e0c56c02ba4fca4da7740401c28f04e3e0c56c08c15359886774040d9e89c9fe20b56c0728c648f507740405beecc04c30b56c0e86b96cb46774040c7bc8e38640b56c097adf54542774040cf2c0950530b56c0f86ef3c6497740409c33a2b4370b56c09755d80c70774040f94ecc7a310b56c04e98309a9577404069006f81040b56c0a56950340f7840409a0645f3000b56c0cb82893f8a7840408335cea6230b56c0cee33098bf784040b821c66b5e0b56c03883bf5fcc784040e02bbaf59a0b56c0e6e786a6ec784040af5fb01bb60b56c0438ead6708794040ae0d15e3fc0b56c0c6a4bf97c27940404985b185200c56c083fa96395d7a4040f94b8bfa240c56c096067e54c37a404070287cb60e0c56c07cf0daa50d7b404087fe092e560c56c0c173efe1927b4040e256410c740c56c077d9af3bdd7b40409b012ec8960c56c0c6c1a563ce7b4040ac58fca6b00c56c062f54718067c4040cf31207bbd0c56c0187959130b7c4040e8d84125ae0c56c0f452b131af7b4040574277499c0c56c030815b77f37a4040f16261889c0c56c0a2d288997d7a40404c1938a0a50c56c0310917f2087a40405e8429caa50c56c021787c7bd77840409aed0a7db00c56c0b5c2f4bd86784040c746205ed70c56c0a3ea573a1f78404020b24813ef0c56c0e126a3ca30784040fe43faedeb0c56c0c66cc9aa0879404055f99e91080d56c0ba83d899427940401327f73b140d56c0342db1321a794040293e3e213b0d56c017f19d98f5784040b4e386df4d0d56c0c993a46b26794040312592e8650d56c0b55208e412794040e1eb6b5d6a0d56c0d06053e751794040462575029a0d56c01841632651794040c9570229b10d56c01c2785798f7940406add06b5df0d56c071581af851794040836dc493dd0d56c0eacda8f92a794040d1cc936b0a0e56c021956247e3784040eb39e97de30d56c0c5aeeded96784040247b849a210e56c0f20a444fca784040280d350a490e56c0471e882cd2784040be4c1421750f56c0fbcbeec9c378404094f947dfa40f56c0184339d1ae784040b7f1272a1b1056c03d484f914378404048a98427f40f56c0761a69a9bc774040af415f7afb0f56c07311df89597740406118b0e42a1056c0b4c876be9f76404001030000000100000055010000c3a04ca3c91556c0f969dc9bdf7e4040d07f0f5ebb1556c073bb97fbe47e4040df6a9db81c1556c094c151f2ea7e4040cc988235ce1256c088bb7a15197f4040d3a414747b1256c0dc4944f8177f404024f0879fff0a56c081b22957787f404054abafae0a0b56c08df0f620048040403f1f65c4050b56c0cac342ad698040404d2f3196e90a56c04fb2d5e594804040b2f1608bdd0a56c0dfc2baf1ee8040404bb0389cf90a56c043e55fcb2b814040b3b45373b90a56c0a6d3ba0d6a814040dec9a7c7b60a56c087fa5dd89a814040ddd3d51d8b0a56c096438b6ce7814040876c205d6c0a56c0ab09a2ee03824040581f0f7d770a56c09016670c73824040c119fcfd620a56c001dbc1887d824040fad346753a0a56c01d1ec2f86982404088f2052d240a56c030dae38574824040b3075a81210a56c08ca37213b5824040f9a067b3ea0956c0b2a19bfd8182404081cea44dd50956c0c8ec2c7aa7824040390ce6af900956c030f65e7cd1824040bb0b9414580956c06f48a302278340400f7d772b4b0956c06f48a30227834040927538ba4a0956c0d13e56f0db8240406f4a79ad840956c0eeea556474824040567f8461c00956c0af9811de1e824040bd3ac780ec0956c0aad55757058240403db7d095080a56c0356090f469814040a86dc328080a56c0c3f352b1318140404a5f0839ef0956c09529e620e8804040c9e53fa4df0956c0de3eabcc94804040a0a70183a40956c0ed2b0fd253804040fd2d01f8a70956c0346953758f804040d2730b5d890956c0d93f4f0306814040292158552f0956c01631ec3026814040d3dd7536e40856c02db1321af980404072bf4351a00856c0397d3d5fb3804040a20dc006440856c0dee7f86871804040894336902e0856c07e54c37e4f804040c7d79e59120856c052d50451f77f4040465b9544f60756c0a6ef3504c77f40404e4354e1cf0756c0406b7efca57f40400ce9f010c60756c0e3a7716f7e7f40402f8b89cdc70756c0f35a09dd257f40402ec55565df0756c06a17d34cf77e40404b051555bf0756c097917a4fe57e4040944a7842af0756c07e8d2441b87e40401633c2db830756c04d840d4faf7e40404aef1b5f7b0756c04df564fed17f40409faeee586c0756c06ff4311f108040409148dbf8130756c09cfd8172db804040d4b66114040756c01ac1c6f5ef804040643bdf4f8d0656c0c91ea166488140404ce0d6dd3c0656c02a711de38a814040897b2c7de80556c0de57e542e58140404704e3e0d20556c0467efd101b82404095ef1989d00556c041f4a44c6a824040406b7efca50556c0d47e6b274a82404022dfa5d4250556c0b64ab0389c814040b4041901150556c06b8313d1af814040bd344580d30456c0f165a208a9814040e9d2bf24950456c040c23060c98140404bac8c463e0456c0c6a4bf97c28140407d7555a0160456c0ad6bb41ce8814040a88aa9f4130456c0fc6f253b36824040b4b0a71dfe0356c062f4dc4257824040b83b6bb75d0356c073f7393e5a82404004c6fa06260356c0a1f65b3b51824040fd1186014b0356c0edbc8dcd8e824040b403ae2b660356c025ea059fe68240400a2e56d4600356c0f60d4c6e1483404067d13b15700356c09cfbabc77d8340408f8eab915d0356c0871744a4a683404039b4c876be0256c0c9cc052e8f83404096cd1c925a0256c0d48041d2a78340409ce1067c7e0256c0f5f411f8c3834040da006c40840256c0bbb54c86e3834040a08d5c37a50256c02578431a158440403f00a94d9c0256c0349e08e23c844040bb44f5d6c00256c0349e08e23c844040e659492bbe0256c0caa65ce15d8440407c9c69c2f60256c0be892139998440402672c119fc0256c00a2debfeb184404087c267ebe00256c0eace13cfd9844040a24621c9ac0256c067d311c0cd84404027f56569a70256c066c1c41f45854040613255302a0256c0f3c81f0c3c854040e52a16bf290256c0c4ec65db698540400a12dbdd030256c0452fa3586e85404082e15cc30c0256c059c0046edd854040035b25581c0256c07ba2ebc20f864040cb660e492d0256c0331af9bce2854040aaf1d24d620256c079e8bb5b598640404b5af10d850256c0e275fd82dd864040b2d5e594800256c03a24b550328740403196e997880256c0992b836a838740402733de567a0256c00bf0dde68d8740408db800344a0256c0cb11329067874040c0779b374e0256c06823d74d298740401f0f7d772b0256c0a725564623874040b0e600c11c0256c070253b3602874040b6d782de1b0256c005dec9a7c7864040ad342905dd0156c053060e68e9864040cee33098bf0156c0c72b103d298740407eaa0a0dc40156c00e2f88484d874040dcbb067de90156c041f163cc5d874040bb09be69fa0156c02733de567a874040999eb0c4030256c050e27327d8874040a04fe449d20156c0cf9f36aad38740409c1727beda0156c079e926310888404030bc92e4b90156c0e8fa3e1c2488404083a7902bf50156c0077b134372884040ecdb4944f80356c05587dc0c378a40406f641ef9830356c0122f4fe78a8a404060c9552c7e0356c0f818ac38d58a4040efe714e4670356c0037cb779e38a404023a46e675f0356c05f454607248b4040fa0ad28c450356c0dae731ca338b40401d739eb12f0356c044520b25938b404016855d143d0356c0de7536e49f8b4040460bd0b69a0356c03659a31ea28b4040bf99982ec40356c067b96c74ce8b4040fdbe7ff3e20356c04703780b248c40409c31cc09da0356c02e724f57778c40400726378aac0356c09e7c7a6ccb8c4040193a7650890356c04a27124c358d40400c5872158b0356c02106baf6058e4040118dee20760356c09700fc53aa8e4040e9edcf45430356c0e6046d72f88e4040ae81ad122c0356c0c808a870048f4040baa46abb090356c0dd990986738f4040a41820d1040356c064cc5d4bc88f4040bfd6a546e80256c071e82d1ede8f404061376c5b940256c086048c2e6f904040103fff3d780256c001a777f17e904040f583ba48a10256c057d0b4c4ca90404098689082a70256c0325706d5069140405cae7e6c920256c06f9f55664a91404031ea5a7b9f0256c09fcbd42478914040b1a4dc7d8e0256c0378c82e0f19140408eaed2dd750256c0581d39d219924040c6a70018cf0256c08bdea9807b924040e90e62670a0356c0b7d100de02934040fd8348861c0356c029b2d6506a93404078431a15380356c0906ad8ef89934040705e9cf86a0356c0de74cb0ef1934040b08d78b29b0356c07bdb4c8578944040e6e8f17b9b0356c0adfa5c6dc59440407b6b60ab040456c099107349d5944040c11e1329cd0356c0b8e7f9d346954040080264e8d80356c0890b40a374954040b9e2e2a8dc0356c0755ab741ed954040f295404aec0356c0fe45d0984996404044882b67ef0356c0d1967329ae964040bde5eac7260456c0d90a9a965897404006f69848690456c0ecbe6378ec9740406b7d91d0960456c09d2d20b41e984040a1d79fc4e70456c0ef92382ba2984040c6353e93fd0456c0cc7c073f71984040d9226937fa0456c07b2e5393e09740400492b06f270556c00ea2b5a2cd97404077f86bb2460556c0bcae5fb01b9840408b87f71c580556c0e04a766c04984040a8c30ab77c0556c02e73ba2c26984040c53d963e740556c0dcd781734698404005a4fd0fb00556c04f2157ea5998404029cb10c7ba0556c0fd851e317a9840408e93c2bcc70556c0486fb88fdc9840403815a930b60556c0e44ba8e0f0984040f9a067b3ea0556c053af5b04c69840404f78094e7d0656c03b53e8bcc6984040c58ec6a17e0656c0bc79aa436e984040bbed42739d0656c0b00111e2ca974040e36f7b82c40656c0899b53c9009840401f85eb51b80656c0cd02ed0e29984040e8154f3dd20656c0ef586c938a984040668522ddcf0656c036cafacdc4984040dd0c37e0f30656c0e7e104a6d39840409b70afcc5b0756c0d829560dc2984040723788d68a0756c0adc090d5ad9840408b8c0e48c20756c0d388997d1e994040dcf126bf450856c05c74b2d47a9940403813d385580856c0029d499baa994040dba2cc06990856c0daab8f87be9940403b191c25af0856c0605b3ffd6799404080289831050956c0fc8ba03193984040e4d6a4db120956c0f3cb608c4898404073124a5f080956c0c40776fc17984040e275fd82dd0856c0e2e7bf07af9740400bcf4bc5c60856c0dd2571564497404091b41b7dcc0856c04ddbbfb2d29640403c2f151bf30856c0d49b51f355964040e675c4211b0956c03cbd5296219640406b0da5f6220956c0d47fd6fcf8954040522cb7b41a0956c042075dc2a1954040bea25bafe90856c087c1fc1532954040ff40b96ddf0856c09acfb9dbf5944040a8c5e061da0856c0274d83a279944040dcf126bf450956c0f3052d2460944040e0490b97550956c0bff2203d4594404022a7afe76b0956c03752b648da934040179e978a8d0956c04b77d7d9909340409a9658198d0956c01cd0d2156c934040bf7ff3e2c40956c0d90a9a965893404065a54929e80956c0f243a51133934040cc26c0b0fc0956c0815ce2c803934040f2cea10c550a56c0addee17668924040016a6ad95a0a56c07bf99d2633924040fc1a4982700a56c07fdaa84e07924040933a014d840a56c056815a0c1e924040adf71bedb80a56c090847d3b899240407a6ea12b110b56c0fd4ffeee1d934040d7187442e80a56c05e4c33ddeb92404084640113b80a56c01bd7bfeb3393404017f2086ea40a56c0740af2b3919340402f34d769a40a56c09c340d8ae6934040c8b260e28f0a56c05eda70581a9440400b0a83328d0a56c095826e2f6994404079b130444e0a56c08b170b43e4944040ead0e979370a56c046b41d5377954040a1478c9e5b0a56c0689604a8a99540402dea93dc610a56c00abb287ae09540405305a3923a0a56c0dd0bcc0a45964040f530b43a390a56c0a036aad3819640407c9e3f6d540a56c0f08ae07f2b974040e316f373430a56c018b5fb55809740402d5c5661330b56c066db696b449840401c0a9fad830b56c0859675ff589840400394861a850b56c08bdf14562a984040363ffed2a20b56c07d957cec2e9840405247c7d5c80b56c05ebc1fb75f984040d49d279eb30b56c09d499baa7b98404028637c98bd0b56c041b96ddfa39840406aa0f99cbb0b56c0d36bb3b112994040151bf33ae20b56c0ddb243fcc39840400d350a49660c56c01b81785dbf9a404088484dbb980c56c05e9ece15a59a4040598638d6c50c56c0fddcd0949d9a4040eb1c03b2d70c56c068244223d89a40401041d5e8d50c56c0e90e62670a9b40404241295ab90c56c068976f7d589b404016a1d80a9a0c56c04e4700378b9b40407cb94f8e020d56c0de02098a1f9d40408a22a46e670d56c0959f54fb749e4040b45a608f890d56c024d40ca9a29e40404be82e89b30d56c0817a336abe9e4040d4d7f335cb0d56c0f296ab1f9b9e40409f211cb3ec0d56c083dc4598a29e40409f211cb3ec0d56c089d4b48b699e40407ffacf9a1f0e56c00917f2086e9e4040494a7a185a0e56c0afd2dd75369e404030116f9d7f0e56c0de567a6d369e404076a911fa990e56c0f0366f9c149e40402f17f19d980e56c0e010aad4ec9d40409c89e942ac0e56c085effd0dda9d4040c9570229b10e56c0af08feb7929d40405f9b8d95980e56c0001fbc76699d4040c9cc052e8f0e56c037548cf3379d404050c24cdbbf0e56c04ed4d2dc0a9d4040655419c6dd0e56c01d740987de9c40405d6f9ba9100f56c041102043c79c404022c50089260f56c0bff1b567969c4040942efd4b520f56c0b7989f1b9a9c4040632827da550f56c0c0ce4d9b719c40407f677bf4860f56c08690f3fe3f9c404044c362d4b50f56c0093543aa289c404048c0e8f2e60f56c01fd8f15f209c40406fa0c03bf90f56c0e068c70dbf9b4040bc57ad4cf80f56c0ec17ec866d9b40402fa704c4241056c0802daf5c6f9b40408104c58f311056c0c100c287129b4040d236fe44651056c0f437a110019b4040630e828e561056c07e8d2441b89a40404c88b9a46a1056c05a492bbea19a40406c3d4338661056c0321f10e84c9a4040471ff301811056c0753c66a0329a4040bb7f2c44871056c0d655815a0c9a40403c873254c51056c01407d0effb9940405c01857afa1056c08f368e588b9940400ccee0ef171156c0ef7211df899940407f315bb22a1156c0f8a8bf5e619940406f9f55664a1156c04f07b29e5a994040bde13e726b1156c06c5b94d920994040e8f9d346751156c0ec4d0cc9c9984040130ce71a661156c0fca886fd9e984040d0b7054b751156c0b42094f77198404018416326511156c06362f3716d984040e481c8224d1156c0ff3f4e9830984040bde2a9471a1156c0a91611c5e4974040ac8e1ce90c1156c00b0dc4b299974040a3e716ba121156c0a9a3e36a64974040b779e3a4301156c03dd7f7e1209740404225ae635c1156c0840f255af2964040c6dff604891156c0938d075bec9640409b92acc3d11156c08a0629780a9740401f477364e51156c0a987687407974040e0675c38101256c00bb8e7f9d3964040dbc4c9fd0e1256c031242713b7964040d3122ba3911156c07d04fef0f39540403315e291781156c04966f50eb79540403546eba86a1156c0b56e83da6f954040581ccefc6a1156c0f3c81f0c3c954040b2d826158d1156c0ed478ac8b094404041b96ddfa31156c041800c1d3b944040577c43e1b31156c06552431b80934040c7b8e2e2a81156c0f969dc9bdf924040c093162eab1156c05f0ce544bb92404031444e5fcf1156c0ae122c0e679240405871aab5301256c02098a3c7ef914040b0e8d66b7a1256c0789961a3ac9140402ee6e786a61256c0e6779acc78914040e61f7d93a61256c0967840d994914040fdf7e0b54b1256c0753dd175e1914040fa5fae450b1256c018b325ab22924040ff5a5eb9de1156c0c51d6ff25b924040e773ee76bd1156c0c63368e89f9240404d840d4faf1156c01c97715303934040a794d74ae81156c082c8224dbc9340401d9430d3f61156c0357ba0151894404028806264c91156c05e4d9eb29a9440406adac534d31156c03e97a949f094404065a54929e81156c0b85cfdd824954040996725adf81156c00aa0185932954040b75f3e59311256c022fc8ba031954040284701a2601256c01764cbf27595404088d7f50b761256c089d00836ae9540406c257497c41256c0537765170c964040a794d74ae81256c0c9569753029640407b884677101356c03e096ccec1954040b6b9313d611356c0b3b3e89d0a96404067800bb2651356c001dc2c5e2c96404052b648da8d1356c0b6494563ed954040b14f00c5c81356c01763601dc7954040f52a323a201456c04b02d4d4b29540408f71c5c5511456c0fa9b508880954040aa08cbe86f1456c0059dc9ab4e95404009bffabe731456c0fda589f2109540409bc8cc052e1556c08fc2f5285c894040c3a04ca3c91556c0f969dc9bdf7e4040, '35442', 33.0737038, -88.1935883, 'Aliceville', 'AL', 'America/Chicago');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `mdzip2`
--
ALTER TABLE `mdzip2`
  ADD PRIMARY KEY (`id`) USING BTREE,
  ADD UNIQUE KEY `zcta5` (`zip`) USING BTREE,
  ADD SPATIAL KEY `SHAPE` (`SHAPE`),
  ADD KEY `city` (`city`,`ST`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `mdzip2`
--
ALTER TABLE `mdzip2`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=36067;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

[williamdes]

Thank you, I will try to reproduce and fix this issue ASAP

[btacordex]

Actually, sorry, I just realized that phpMyAdmin didn't quite capture all the properties of the original table when I created the one-row copy. (Or it didn't copy them on export?) Please use this version:

-- MySQL dump 10.13  Distrib 8.0.18, for Linux (x86_64)
--
-- Host: localhost    Database: imaging
-- ------------------------------------------------------
-- Server version	8.0.18

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `mdzip2`
--

DROP TABLE IF EXISTS `mdzip2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `mdzip2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shape` geometry NOT NULL /*!80003 SRID 4326 */,
  `zip` varchar(7) NOT NULL,
  `centerLat` float(9,7) NOT NULL,
  `centerLon` float(10,7) NOT NULL,
  `city` varchar(51) NOT NULL COMMENT 'copied from mdZip',
  `ST` varchar(2) NOT NULL COMMENT 'copied from mdZip',
  `tzName` varchar(80) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `zcta5` (`zip`) USING BTREE,
  KEY `city` (`city`,`ST`),
  SPATIAL KEY `shape` (`shape`)
) ENGINE=InnoDB AUTO_INCREMENT=36067 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `mdzip2`
--

LOCK TABLES `mdzip2` WRITE;
/*!40000 ALTER TABLE `mdzip2` DISABLE KEYS */;
INSERT INTO `mdzip2` VALUES (1,0xE6100000010600000002000000010300000001000000AF0000006118B0E42A1056C0B4C876BE9F764040E0BE0E9C331056C0593332C85D76404012F6ED24221056C0E4BD6A65C2754040AD6EF59CF40F56C048FAB48AFE744040DF37BEF6CC0F56C09AEC9FA701754040CAC51858C70F56C057D0B4C4CA74404049DBF813950F56C0EB3713D38574404017B83CD68C0F56C081CD397826744040596DFE5F750F56C02942EA76F6734040695721E5270F56C079B0C56E9F7340400FD253E4100F56C01AFA27B858734040AF21382EE30E56C0C9586DFE5F734040C9772975C90E56C008CA6DFB1E73404002A08A1BB70E56C0E97E4E417E724040849ECDAACF0E56C0F19D98F562724040439259BDC30E56C0651C23D9237240408DD13AAA9A0E56C01B0FB6D8ED714040C6BFCFB8700E56C0EA9788B7CE7140409010E50B5A0E56C029ECA2E8817140407F6B274A420E56C04DD9E90775714040ABCB2901310E56C07DE9EDCF45714040CD936B0A640E56C01A19E42EC27040405FED28CE510E56C0BEF73768AF704040F29A5775560E56C04C512E8D5F704040657094BC3A0E56C0A375543541704040D865F84F370E56C05D8940F50F70404043705CC64D0E56C0EBC5504EB46F40408AE42B81940E56C0F8E28BF6786F4040B9FAB1497E0E56C04208C897506F4040CE1B2785790E56C0C1A90F24EF6E4040C119FCFD620E56C0A05225CADE6E4040E9D32AFA430E56C058569A94826E4040580394861A0E56C0938FDD054A6E4040C66CC9AA080E56C0B4E73235096E4040B83F170D190E56C0E4839ECDAA6D404062C092AB580E56C020990E9D9E6D404074D0251C7A0E56C02849D74CBE6D4040A7203F1BB90E56C07C629D2ADF6D40401D5723BBD20E56C0EC6CC83F336E4040D027F224E90E56C0CC608C48146E4040AE2D3C2F150F56C005FD851E316E404001BF4692200F56C0EBFEB1101D6E4040E6EAC726F90E56C090A4A487A16D40402E71E481C80E56C0849F38807E6D4040ADDD76A1B90E56C07FBF982D596D40405EBBB4E1B00E56C0F0332E1C086D40403A4030478F0E56C0DC2C5E2C0C6D404061E0B9F7700E56C0137F1475E66C4040D49B51F3550E56C0A5F9635A9B6C4040E42D573F360E56C039D55A98856C40409816F549EE0D56C02829B000A66C40408D96033DD40D56C0AEBA0ED5946C40401A33897AC10D56C08B8D791D716C404039D1AE42CA0D56C05682C5E1CC6B404058AD4CF8A50D56C00EC00644886B4040A260C614AC0D56C09F7422C1546B4040A5DB12B9E00D56C0CBBBEA01F36A40407903CC7C070E56C09757AEB7CD6A4040637AC2120F0E56C0971DE21FB66A404052D2C3D0EA0D56C0661536035C6A4040CF68AB92C80D56C07BDCB75A276A4040A20914B1880D56C00A86730D336A4040018A9125730D56C01FF5D72B2C6A404059130B7C450D56C0EB39E97DE36940406A882AFC190D56C022516859F76940408EB0A888D30C56C07670B037316A4040E38DCC237F0C56C0054F2157EA694040E606431D560C56C09947FE60E06940408690F3FE3F0C56C01CEC4D0CC9694040217711A6280C56C0863B17467A694040C845B588280C56C0DF701FB9356940409A7D1EA33C0C56C055BFD2F9F0684040D8F0F44A590C56C032923D42CD684040AE80423D7D0C56C0EC3026FDBD68404040170D198F0C56C0013274ECA06840405E4A5D328E0C56C08EAED2DD756840405F44DB31750C56C0DBDD03745F684040228B34F10E0C56C0BBEEAD484C684040EA25C632FD0B56C003ECA35357684040CE177B2FBE0B56C0605793A7AC6840404C50C3B7B00B56C06284F068E3684040C3802557B10B56C06BD619DF176940403F8BA548BE0B56C0713B342C46694040245F09A4C40B56C04628B682A669404098512CB7B40B56C06F63B323D56940401D1EC2F8690B56C0B24B546F0D6A4040DC82A5BA800B56C01381EA1F446A4040E2E47E87A20B56C0E4A3C519C36A40401DE38A8BA30B56C0B8CB7EDDE96A40409DBAF2599E0B56C06FBDA607056B40409A42E735760B56C027A25F5B3F6B40401AFD6838650B56C00551F701486B40408DD31055F80A56C074D190F1286B40400470B378B10A56C0DE3CD521376B4040CF842689250A56C0C617EDF1426C4040D5B0DF13EB0956C0EACDA8F92A6D4040304AD05FE80956C0A1A17F828B6D4040E161DA37F70956C0C5E40D30F36D4040BE2D58AA0B0A56C0C0CFB870206E4040F20C1AFA270A56C01E8D43FD2E6E4040E9EDCF45430A56C049D92269376E4040E1455F419A0A56C043FF04172B6E40404F0647C9AB0A56C08DD2A57F496E40405039268BFB0A56C048179B560A6F4040F4BF5C8B160B56C04B5B5CE3336F404015191D90840B56C0D3DEE00B936F4040CCF09F6EA00B56C0C576F700DD6F4040A85489B2B70B56C0917EFB3A7070404014252191B60B56C0F56915FDA1714040FE7E315BB20B56C0F73E55850672404012BD8C62B90B56C0A0C211A4527240404012F6ED240C56C0083A5AD592744040231631EC300C56C07CD45FAFB0744040E868554B3A0C56C06A865451BC744040E25817B7D10C56C083328D2617754040DAC70A7E1B0D56C0F96706F1817540409C6A2DCC420D56C07BBFD18E1B76404029417FA1470D56C09AB4A9BA47764040E547FC8A350D56C02619390B7B764040809A5AB6D60C56C014D044D8F0764040CC7EDDE9CE0C56C0DFFC86890677404088855AD3BC0C56C089D00836AE7740405051F52B9D0C56C07C28D192C7774040F6EB4E779E0C56C02BA4FCA4DA7740401C28F04E3E0C56C08C15359886774040D9E89C9FE20B56C0728C648F507740405BEECC04C30B56C0E86B96CB46774040C7BC8E38640B56C097ADF54542774040CF2C0950530B56C0F86EF3C6497740409C33A2B4370B56C09755D80C70774040F94ECC7A310B56C04E98309A9577404069006F81040B56C0A56950340F7840409A0645F3000B56C0CB82893F8A7840408335CEA6230B56C0CEE33098BF784040B821C66B5E0B56C03883BF5FCC784040E02BBAF59A0B56C0E6E786A6EC784040AF5FB01BB60B56C0438EAD6708794040AE0D15E3FC0B56C0C6A4BF97C27940404985B185200C56C083FA96395D7A4040F94B8BFA240C56C096067E54C37A404070287CB60E0C56C07CF0DAA50D7B404087FE092E560C56C0C173EFE1927B4040E256410C740C56C077D9AF3BDD7B40409B012EC8960C56C0C6C1A563CE7B4040AC58FCA6B00C56C062F54718067C4040CF31207BBD0C56C0187959130B7C4040E8D84125AE0C56C0F452B131AF7B4040574277499C0C56C030815B77F37A4040F16261889C0C56C0A2D288997D7A40404C1938A0A50C56C0310917F2087A40405E8429CAA50C56C021787C7BD77840409AED0A7DB00C56C0B5C2F4BD86784040C746205ED70C56C0A3EA573A1F78404020B24813EF0C56C0E126A3CA30784040FE43FAEDEB0C56C0C66CC9AA0879404055F99E91080D56C0BA83D899427940401327F73B140D56C0342DB1321A794040293E3E213B0D56C017F19D98F5784040B4E386DF4D0D56C0C993A46B26794040312592E8650D56C0B55208E412794040E1EB6B5D6A0D56C0D06053E751794040462575029A0D56C01841632651794040C9570229B10D56C01C2785798F7940406ADD06B5DF0D56C071581AF851794040836DC493DD0D56C0EACDA8F92A794040D1CC936B0A0E56C021956247E3784040EB39E97DE30D56C0C5AEEDED96784040247B849A210E56C0F20A444FCA784040280D350A490E56C0471E882CD2784040BE4C1421750F56C0FBCBEEC9C378404094F947DFA40F56C0184339D1AE784040B7F1272A1B1056C03D484F914378404048A98427F40F56C0761A69A9BC774040AF415F7AFB0F56C07311DF89597740406118B0E42A1056C0B4C876BE9F76404001030000000100000055010000C3A04CA3C91556C0F969DC9BDF7E4040D07F0F5EBB1556C073BB97FBE47E4040DF6A9DB81C1556C094C151F2EA7E4040CC988235CE1256C088BB7A15197F4040D3A414747B1256C0DC4944F8177F404024F0879FFF0A56C081B22957787F404054ABAFAE0A0B56C08DF0F620048040403F1F65C4050B56C0CAC342AD698040404D2F3196E90A56C04FB2D5E594804040B2F1608BDD0A56C0DFC2BAF1EE8040404BB0389CF90A56C043E55FCB2B814040B3B45373B90A56C0A6D3BA0D6A814040DEC9A7C7B60A56C087FA5DD89A814040DDD3D51D8B0A56C096438B6CE7814040876C205D6C0A56C0AB09A2EE03824040581F0F7D770A56C09016670C73824040C119FCFD620A56C001DBC1887D824040FAD346753A0A56C01D1EC2F86982404088F2052D240A56C030DAE38574824040B3075A81210A56C08CA37213B5824040F9A067B3EA0956C0B2A19BFD8182404081CEA44DD50956C0C8EC2C7AA7824040390CE6AF900956C030F65E7CD1824040BB0B9414580956C06F48A302278340400F7D772B4B0956C06F48A30227834040927538BA4A0956C0D13E56F0DB8240406F4A79AD840956C0EEEA556474824040567F8461C00956C0AF9811DE1E824040BD3AC780EC0956C0AAD55757058240403DB7D095080A56C0356090F469814040A86DC328080A56C0C3F352B1318140404A5F0839EF0956C09529E620E8804040C9E53FA4DF0956C0DE3EABCC94804040A0A70183A40956C0ED2B0FD253804040FD2D01F8A70956C0346953758F804040D2730B5D890956C0D93F4F0306814040292158552F0956C01631EC3026814040D3DD7536E40856C02DB1321AF980404072BF4351A00856C0397D3D5FB3804040A20DC006440856C0DEE7F86871804040894336902E0856C07E54C37E4F804040C7D79E59120856C052D50451F77F4040465B9544F60756C0A6EF3504C77F40404E4354E1CF0756C0406B7EFCA57F40400CE9F010C60756C0E3A7716F7E7F40402F8B89CDC70756C0F35A09DD257F40402EC55565DF0756C06A17D34CF77E40404B051555BF0756C097917A4FE57E4040944A7842AF0756C07E8D2441B87E40401633C2DB830756C04D840D4FAF7E40404AEF1B5F7B0756C04DF564FED17F40409FAEEE586C0756C06FF4311F108040409148DBF8130756C09CFD8172DB804040D4B66114040756C01AC1C6F5EF804040643BDF4F8D0656C0C91EA166488140404CE0D6DD3C0656C02A711DE38A814040897B2C7DE80556C0DE57E542E58140404704E3E0D20556C0467EFD101B82404095EF1989D00556C041F4A44C6A824040406B7EFCA50556C0D47E6B274A82404022DFA5D4250556C0B64AB0389C814040B4041901150556C06B8313D1AF814040BD344580D30456C0F165A208A9814040E9D2BF24950456C040C23060C98140404BAC8C463E0456C0C6A4BF97C28140407D7555A0160456C0AD6BB41CE8814040A88AA9F4130456C0FC6F253B36824040B4B0A71DFE0356C062F4DC4257824040B83B6BB75D0356C073F7393E5A82404004C6FA06260356C0A1F65B3B51824040FD1186014B0356C0EDBC8DCD8E824040B403AE2B660356C025EA059FE68240400A2E56D4600356C0F60D4C6E1483404067D13B15700356C09CFBABC77D8340408F8EAB915D0356C0871744A4A683404039B4C876BE0256C0C9CC052E8F83404096CD1C925A0256C0D48041D2A78340409CE1067C7E0256C0F5F411F8C3834040DA006C40840256C0BBB54C86E3834040A08D5C37A50256C02578431A158440403F00A94D9C0256C0349E08E23C844040BB44F5D6C00256C0349E08E23C844040E659492BBE0256C0CAA65CE15D8440407C9C69C2F60256C0BE892139998440402672C119FC0256C00A2DEBFEB184404087C267EBE00256C0EACE13CFD9844040A24621C9AC0256C067D311C0CD84404027F56569A70256C066C1C41F45854040613255302A0256C0F3C81F0C3C854040E52A16BF290256C0C4EC65DB698540400A12DBDD030256C0452FA3586E85404082E15CC30C0256C059C0046EDD854040035B25581C0256C07BA2EBC20F864040CB660E492D0256C0331AF9BCE2854040AAF1D24D620256C079E8BB5B598640404B5AF10D850256C0E275FD82DD864040B2D5E594800256C03A24B550328740403196E997880256C0992B836A838740402733DE567A0256C00BF0DDE68D8740408DB800344A0256C0CB11329067874040C0779B374E0256C06823D74D298740401F0F7D772B0256C0A725564623874040B0E600C11C0256C070253B3602874040B6D782DE1B0256C005DEC9A7C7864040AD342905DD0156C053060E68E9864040CEE33098BF0156C0C72B103D298740407EAA0A0DC40156C00E2F88484D874040DCBB067DE90156C041F163CC5D874040BB09BE69FA0156C02733DE567A874040999EB0C4030256C050E27327D8874040A04FE449D20156C0CF9F36AAD38740409C1727BEDA0156C079E926310888404030BC92E4B90156C0E8FA3E1C2488404083A7902BF50156C0077B134372884040ECDB4944F80356C05587DC0C378A40406F641EF9830356C0122F4FE78A8A404060C9552C7E0356C0F818AC38D58A4040EFE714E4670356C0037CB779E38A404023A46E675F0356C05F454607248B4040FA0AD28C450356C0DAE731CA338B40401D739EB12F0356C044520B25938B404016855D143D0356C0DE7536E49F8B4040460BD0B69A0356C03659A31EA28B4040BF99982EC40356C067B96C74CE8B4040FDBE7FF3E20356C04703780B248C40409C31CC09DA0356C02E724F57778C40400726378AAC0356C09E7C7A6CCB8C4040193A7650890356C04A27124C358D40400C5872158B0356C02106BAF6058E4040118DEE20760356C09700FC53AA8E4040E9EDCF45430356C0E6046D72F88E4040AE81AD122C0356C0C808A870048F4040BAA46ABB090356C0DD990986738F4040A41820D1040356C064CC5D4BC88F4040BFD6A546E80256C071E82D1EDE8F404061376C5B940256C086048C2E6F904040103FFF3D780256C001A777F17E904040F583BA48A10256C057D0B4C4CA90404098689082A70256C0325706D5069140405CAE7E6C920256C06F9F55664A91404031EA5A7B9F0256C09FCBD42478914040B1A4DC7D8E0256C0378C82E0F19140408EAED2DD750256C0581D39D219924040C6A70018CF0256C08BDEA9807B924040E90E62670A0356C0B7D100DE02934040FD8348861C0356C029B2D6506A93404078431A15380356C0906AD8EF89934040705E9CF86A0356C0DE74CB0EF1934040B08D78B29B0356C07BDB4C8578944040E6E8F17B9B0356C0ADFA5C6DC59440407B6B60AB040456C099107349D5944040C11E1329CD0356C0B8E7F9D346954040080264E8D80356C0890B40A374954040B9E2E2A8DC0356C0755AB741ED954040F295404AEC0356C0FE45D0984996404044882B67EF0356C0D1967329AE964040BDE5EAC7260456C0D90A9A965897404006F69848690456C0ECBE6378EC9740406B7D91D0960456C09D2D20B41E984040A1D79FC4E70456C0EF92382BA2984040C6353E93FD0456C0CC7C073F71984040D9226937FA0456C07B2E5393E09740400492B06F270556C00EA2B5A2CD97404077F86BB2460556C0BCAE5FB01B9840408B87F71C580556C0E04A766C04984040A8C30AB77C0556C02E73BA2C26984040C53D963E740556C0DCD781734698404005A4FD0FB00556C04F2157EA5998404029CB10C7BA0556C0FD851E317A9840408E93C2BCC70556C0486FB88FDC9840403815A930B60556C0E44BA8E0F0984040F9A067B3EA0556C053AF5B04C69840404F78094E7D0656C03B53E8BCC6984040C58EC6A17E0656C0BC79AA436E984040BBED42739D0656C0B00111E2CA974040E36F7B82C40656C0899B53C9009840401F85EB51B80656C0CD02ED0E29984040E8154F3DD20656C0EF586C938A984040668522DDCF0656C036CAFACDC4984040DD0C37E0F30656C0E7E104A6D39840409B70AFCC5B0756C0D829560DC2984040723788D68A0756C0ADC090D5AD9840408B8C0E48C20756C0D388997D1E994040DCF126BF450856C05C74B2D47A9940403813D385580856C0029D499BAA994040DBA2CC06990856C0DAAB8F87BE9940403B191C25AF0856C0605B3FFD6799404080289831050956C0FC8BA03193984040E4D6A4DB120956C0F3CB608C4898404073124A5F080956C0C40776FC17984040E275FD82DD0856C0E2E7BF07AF9740400BCF4BC5C60856C0DD2571564497404091B41B7DCC0856C04DDBBFB2D29640403C2F151BF30856C0D49B51F355964040E675C4211B0956C03CBD5296219640406B0DA5F6220956C0D47FD6FCF8954040522CB7B41A0956C042075DC2A1954040BEA25BAFE90856C087C1FC1532954040FF40B96DDF0856C09ACFB9DBF5944040A8C5E061DA0856C0274D83A279944040DCF126BF450956C0F3052D2460944040E0490B97550956C0BFF2203D4594404022A7AFE76B0956C03752B648DA934040179E978A8D0956C04B77D7D9909340409A9658198D0956C01CD0D2156C934040BF7FF3E2C40956C0D90A9A965893404065A54929E80956C0F243A51133934040CC26C0B0FC0956C0815CE2C803934040F2CEA10C550A56C0ADDEE17668924040016A6AD95A0A56C07BF99D2633924040FC1A4982700A56C07FDAA84E07924040933A014D840A56C056815A0C1E924040ADF71BEDB80A56C090847D3B899240407A6EA12B110B56C0FD4FFEEE1D934040D7187442E80A56C05E4C33DDEB92404084640113B80A56C01BD7BFEB3393404017F2086EA40A56C0740AF2B3919340402F34D769A40A56C09C340D8AE6934040C8B260E28F0A56C05EDA70581A9440400B0A83328D0A56C095826E2F6994404079B130444E0A56C08B170B43E4944040EAD0E979370A56C046B41D5377954040A1478C9E5B0A56C0689604A8A99540402DEA93DC610A56C00ABB287AE09540405305A3923A0A56C0DD0BCC0A45964040F530B43A390A56C0A036AAD3819640407C9E3F6D540A56C0F08AE07F2B974040E316F373430A56C018B5FB55809740402D5C5661330B56C066DB696B449840401C0A9FAD830B56C0859675FF589840400394861A850B56C08BDF14562A984040363FFED2A20B56C07D957CEC2E9840405247C7D5C80B56C05EBC1FB75F984040D49D279EB30B56C09D499BAA7B98404028637C98BD0B56C041B96DDFA39840406AA0F99CBB0B56C0D36BB3B112994040151BF33AE20B56C0DDB243FCC39840400D350A49660C56C01B81785DBF9A404088484DBB980C56C05E9ECE15A59A4040598638D6C50C56C0FDDCD0949D9A4040EB1C03B2D70C56C068244223D89A40401041D5E8D50C56C0E90E62670A9B40404241295AB90C56C068976F7D589B404016A1D80A9A0C56C04E4700378B9B40407CB94F8E020D56C0DE02098A1F9D40408A22A46E670D56C0959F54FB749E4040B45A608F890D56C024D40CA9A29E40404BE82E89B30D56C0817A336ABE9E4040D4D7F335CB0D56C0F296AB1F9B9E40409F211CB3EC0D56C083DC4598A29E40409F211CB3EC0D56C089D4B48B699E40407FFACF9A1F0E56C00917F2086E9E4040494A7A185A0E56C0AFD2DD75369E404030116F9D7F0E56C0DE567A6D369E404076A911FA990E56C0F0366F9C149E40402F17F19D980E56C0E010AAD4EC9D40409C89E942AC0E56C085EFFD0DDA9D4040C9570229B10E56C0AF08FEB7929D40405F9B8D95980E56C0001FBC76699D4040C9CC052E8F0E56C037548CF3379D404050C24CDBBF0E56C04ED4D2DC0A9D4040655419C6DD0E56C01D740987DE9C40405D6F9BA9100F56C041102043C79C404022C50089260F56C0BFF1B567969C4040942EFD4B520F56C0B7989F1B9A9C4040632827DA550F56C0C0CE4D9B719C40407F677BF4860F56C08690F3FE3F9C404044C362D4B50F56C0093543AA289C404048C0E8F2E60F56C01FD8F15F209C40406FA0C03BF90F56C0E068C70DBF9B4040BC57AD4CF80F56C0EC17EC866D9B40402FA704C4241056C0802DAF5C6F9B40408104C58F311056C0C100C287129B4040D236FE44651056C0F437A110019B4040630E828E561056C07E8D2441B89A40404C88B9A46A1056C05A492BBEA19A40406C3D4338661056C0321F10E84C9A4040471FF301811056C0753C66A0329A4040BB7F2C44871056C0D655815A0C9A40403C873254C51056C01407D0EFFB9940405C01857AFA1056C08F368E588B9940400CCEE0EF171156C0EF7211DF899940407F315BB22A1156C0F8A8BF5E619940406F9F55664A1156C04F07B29E5A994040BDE13E726B1156C06C5B94D920994040E8F9D346751156C0EC4D0CC9C9984040130CE71A661156C0FCA886FD9E984040D0B7054B751156C0B42094F77198404018416326511156C06362F3716D984040E481C8224D1156C0FF3F4E9830984040BDE2A9471A1156C0A91611C5E4974040AC8E1CE90C1156C00B0DC4B299974040A3E716BA121156C0A9A3E36A64974040B779E3A4301156C03DD7F7E1209740404225AE635C1156C0840F255AF2964040C6DFF604891156C0938D075BEC9640409B92ACC3D11156C08A0629780A9740401F477364E51156C0A987687407974040E0675C38101256C00BB8E7F9D3964040DBC4C9FD0E1256C031242713B7964040D3122BA3911156C07D04FEF0F39540403315E291781156C04966F50EB79540403546EBA86A1156C0B56E83DA6F954040581CCEFC6A1156C0F3C81F0C3C954040B2D826158D1156C0ED478AC8B094404041B96DDFA31156C041800C1D3B944040577C43E1B31156C06552431B80934040C7B8E2E2A81156C0F969DC9BDF924040C093162EAB1156C05F0CE544BB92404031444E5FCF1156C0AE122C0E679240405871AAB5301256C02098A3C7EF914040B0E8D66B7A1256C0789961A3AC9140402EE6E786A61256C0E6779ACC78914040E61F7D93A61256C0967840D994914040FDF7E0B54B1256C0753DD175E1914040FA5FAE450B1256C018B325AB22924040FF5A5EB9DE1156C0C51D6FF25B924040E773EE76BD1156C0C63368E89F9240404D840D4FAF1156C01C97715303934040A794D74AE81156C082C8224DBC9340401D9430D3F61156C0357BA0151894404028806264C91156C05E4D9EB29A9440406ADAC534D31156C03E97A949F094404065A54929E81156C0B85CFDD824954040996725ADF81156C00AA0185932954040B75F3E59311256C022FC8BA031954040284701A2601256C01764CBF27595404088D7F50B761256C089D00836AE9540406C257497C41256C0537765170C964040A794D74AE81256C0C9569753029640407B884677101356C03E096CCEC1954040B6B9313D611356C0B3B3E89D0A96404067800BB2651356C001DC2C5E2C96404052B648DA8D1356C0B6494563ED954040B14F00C5C81356C01763601DC7954040F52A323A201456C04B02D4D4B29540408F71C5C5511456C0FA9B508880954040AA08CBE86F1456C0059DC9AB4E95404009BFFABE731456C0FDA589F2109540409BC8CC052E1556C08FC2F5285C894040C3A04CA3C91556C0F969DC9BDF7E4040,'35442',33.0737038,-88.1935883,'Aliceville','AL','America/Chicago');
/*!40000 ALTER TABLE `mdzip2` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2020-03-04 14:03:44

[MauricioFauth]

I was able to reproduce this on demo server. Works as expected with the MariaDB server.

[williamdes]

@Pankaj-Wadhwani could you have a look ?

[Pankaj-Wadhwani]

can you share screenshots?

[williamdes]

MariaDB

MySQL 8

[williamdes]

Disabling the OpenStreetMaps checkbox shows the data

[Pankaj-Wadhwani]

I'm unable to find a solution , I will try to solve other issues. @williamdes

[williamdes]

@asprazz Would you be interested to fix this issue ?

[anksh1997]

Hi @williamdes, I'll try for sure

[anksh1997]

@btacordex is right about different way of storing spatial data in MYSQL8 than MariaDB/MYSQL5

MariaDB

MYSQL8

Now the thing is,
i. is it possible to change (in phpmyadmin) the wayMYSQL8 stores geom data?
or
ii. we can write parser/swapper to swap long and lat when its MYSQL8 version
or
iii. we can only change long with lat and vice versa values whereever necessary
or
iv. there can be another way by modifying SQL query when fetching raw data for GIS vis which I don't know

@williamdes need guidance

[ibennetch]

Thanks for finding this. If this is changed in MySQL version 8 (a link to documentation would be helpful to cover all cases), then we have the capability to detect the user’s version and use the proper SQL commands. We already do this in many places, the most notable of which is commands for creating or modifying users (I believe there are currently three different options depending on the database version). It would be great if we could get this fixed.

[williamdes]

Hi @asprazz

i is not possible

ii is not the very best option

iii could be an option

iv seems to be the best solution at libraries/classes/Gis/GisVisualization.php at _modifySqlQuery

I do think MySQL ST_SwapXY function will be useful as a fix after finding why this bug did occur ;)

https://dev.mysql.com/doc/refman/8.0/en/gis-format-conversion-functions.html#function_st-swapxy

Thank you for the detection work

[anksh1997]

Thank you. I will be going for 4th one.

PS: @btacordex did reported everythin in depth :)

[ibennetch]

Ah I honk I misunderstood the question. Thanks William for answering this!

[williamdes]

"Incompatible Change: These functions now interpret latitude and longitude coordinates as in the order specified by the spatial reference system. The functions also accept an optional argument to override the default axis order."
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-1.html

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-1.html

"A new spatial function, ST_SwapXY(), accepts a geometry argument and swaps the X and Y values of each coordinate pair within the geometry. For more information, see Geometry Format Conversion Functions."

[anksh1997]

Yeah! It was introduced in MYSQL8.0.1, when they started using co-ordinates as latitude and longitude

This is the optional arugument that we can pass to ST_ASTEXT to override default axis order.
ST_TEXT(....., 'axis-order=long-lat')
Should we go with this rather than ST_SwapXY ?

[williamdes]

I think we could use this solution and also add a version restriction for this fix starting with 8.0.1

[drtrppr]

Don't know if this is the right place to comment, but I'm trying to get some clear on flipflopping lat-lon I experience (see stackoverflow (as suggested on your homepage) https://stackoverflow.com/questions/62272155/what-to-expect-when-using-mariadb-phpmyadmin-when-working-with-datatype-geometr ) Don't get sattisfying response there, so I searched the phpmyadmin-issues. This issue comes close, exept it deals with mysql 8, I'm experiencing the flipflopping with mariadb 10.4.1.

[williamdes]

Hi @drtrppr
Are you sure to reproduce the issue mentioned here with the data #16012 (comment) on MariaDB ?

[drtrppr]

Thanks for response, and I'm absolutely NOT sure that I can reproduce the issue with data of comment :) . Things are getting fast beyond my level of knowlegde to be sure about the technical aspects, that's why I posted the question on Stackoverflow. My guess is that phpmyadmin/mariadb is working correctly, but I misunderstand how things work.

Main question at the moment is do lat-lon correspond to x-y in phpmyadmin (with a given SRID I guess)? If not, so lat-lon correspond to y-x by design, then there is no problem. I just have to swap values when importing data in database, and swap them again when exporting. It's just that it doesn't feel right.

Just want to be sure before importing a bunch of data, and having to swap them at some time etc.

If that's not the case, so lat-lon do correspond to x-y, I'll be happy to sent you all the details you want to have, if you're willing to help when I get stuck in the more technical details.

[williamdes]

I think https://dba.stackexchange.com/a/182626/117444 is the answer.

Point(x, y)
Constructs a Point using its coordinates
This isn't entirely correct.
All GIS implementations must do (x,y) for projected coordinates which is (long,lat).
But, on geodidic cordinate systems there is some disagreement about what to do. MySQL (and SQL Server) do (lat,long) but PostGIS maintains (long,lat) everywhere.

It's just that it doesn't feel right.

I would say, do not adapt your data to phpMyAdmin this is a bad idea :)

Also I am considering adding a checkbox to invert the coordinates (very unsure)
This did the trick to view the GIS data

SELECT coordinates,POINT(ST_Y(coordinates), ST_X(coordinates)) as coordinatesReversed  FROM `addresses`

[drtrppr]

Ah, disagreement, ok, that makes difficult to code.

Reading what btacordex wrote to report the bug:

MySQL 8 has a better understanding of SRSes, and when using an SRS system that represents geographic coordinates, rather than Cartesian coordinates, it expects points in the regular format: latitude, then longitude.

As phpmyadmin is targeted towards Mysql/Mariadb (homepage phpmyadmin), the lat-lon format should be the way to go?

But, what's puzzling me now, this issue is closed, so I assume phpmyadmin in combo with mysql 8 uses lat-lon now? I'm using mariadb 10.4 so I would expect the lat-lon format? So why still the invertion? There's something I'm not getting I think.

I would say, do not adapt your data to phpMyAdmin this is a bad idea :)

I was pleasantly surprised that these features showed up when using the GEOMETRY-format and really like using it. Makes it very easy to check geo-data. Indeed will not adapt data to phpMyAdmin, I think I will decide to use lat-lon, because of what btacordex wrote and my gpx-files also use the lat-lon format. So, that invertion-checkbox would be nice for the time being, but using the given SELECT-statement is my rescue-kit! Thanks.

[williamdes]

But, what's puzzling me now, this issue is closed, so I assume phpmyadmin in combo with mysql 8 uses lat-lon now? I'm using mariadb 10.4 so I would expect the lat-lon format? So why still the invertion? There's something I'm not getting I think.

"Incompatible Change: These functions now interpret latitude and longitude coordinates as in the order specified by the spatial reference system. The functions also accept an optional argument to override the default axis order." (#16012 (comment))

This issue is closed because we added a compatibility setting for newer MySQL versions (969f43c)

I was pleasantly surprised that these features showed up when using the GEOMETRY-format and really like using it. Makes it very easy to check geo-data. Indeed will not adapt data to phpMyAdmin, I think I will decide to use lat-lon, because of what btacordex wrote and my gpx-files also use the lat-lon format. So, that invertion-checkbox would be nice for the time being, but using the given SELECT-statement is my rescue-kit! Thanks.

Yeah, I have a database that has no SRID and that maybe causes the inversion 🤔

I would need an expert to let us know if we are doing the lat long stuff right or not

As phpmyadmin is targeted towards Mysql/Mariadb (homepage phpmyadmin), the lat-lon format should be the way to go?

Go the database way, and then use phpMyAdmin. If the results are incoherent will will have something to fix ;)

[drtrppr]

I stumbled upon https://wiki.osgeo.org/wiki/Tile_Map_Service_Specification :

Firstly, the EPSG database has some specific definitions for commonly used geodetic coordinate systems, in particular EPSG:4326 -- geodetic coordinates relative to the WGS84 spheroid. The EPSG definition for 4326 says that the coordinate order is latitude, longitude and that the units are degrees, minutes, seconds. However, common usage of EPSG:4326 in web mapping says that the coordinate order is longitude, latitude and the units are decimal degrees.

Apparently order has a dependancy with units used. I'm using decimal degrees, so I need to use lon-lat order. When I use this order, phpadmin maps the data the way I expected.