When using geospatial column types such as POINT with MySQL and MariaDB, there is a lot of confusion about the order in which one should specify latitude and longitude (1,2,3), and different applications use different conventions. However, when using some functions - such as ST_DISTANCE_SPHERE - the order of lat and long matters.

In what order to specify Latitude and Longitude when creating a POINT in MySQL and MariaDB?

I slighted adapted an example from the MariaDB docs and tested this on MariaDB 10.6.7 and MySQL 8.0.28. Both databases return numerically identical results:

set @zenica   = ST_GeomFromText('POINT(17.907743 44.203438)');
set @sarajevo = ST_GeomFromText('POINT(18.413076 43.856258)');


set @zenica2   = ST_GeomFromText('POINT(44.203438 17.907743)');
set @sarajevo2 = ST_GeomFromText('POINT(43.856258 18.413076)');
SELECT ST_Distance_Sphere(@zenica, @sarajevo), ST_Distance_Sphere(@zenica2, @sarajevo2);
-- Result: 55878.59337591705    67103.51207765185

So in both MySQL and MariaDB, we should put long-lat format when creating POINTS, i.e. longitude goes first

In what order do MySQL and MariaDB return GIS coordinates?

When SELECTing a point, MySQL and MariaDB return a binary format of the point. However, I get a different response depending on which database I am using

CodePudding user response：

MySQL and MariaDB use incompatible binary formats for representing POINTs

Unfortunately, MySQL uses lon-lat in its binary format for POINT while MariaDB has the reverse. Here is some example code in PHP:

$testCase = DB::select("SELECT ST_PointFromText('POINT(24.00 12.00)', 4326) AS samplePoint")[0];
$testUnpacked = unpack("lSRID/CByteOrder/lTypeInfo/dFirst/dSecond", $testCase->samplePoint);
var_dump($testUnpacked['First'])
# double(12) -- MySQL output
# double(24) -- MariaDB output

When encountering this problem in my application, I needed to forgo explicitly checking for the database vendor using version strings or similar, and used "feature detection" instead - seeing what response I get for the testCase and adjusting the unpack format accordingly.

CodePudding user response：

In MySQL the interpretation depends on SRID you use with the data, and follows the SRID-defined order, thus latitude-longitude for SRID 4326. Without SRID (same as SRID=0) it uses longitude-latitude order. It is easy to check by e.g. passing location at the pole, 90:100 - with lat:lon order it is North pole, with lon:lat order it is incorrect data since latitude should be between -90 and 90.

In MySQL you can also use regular ST_Distance to compute distance (in meters) between points in SRID=4326, as it properly implements this geographic SRID:

-- MySQL 8.0
select 
-- ST_Distance_Sphere without SRID => lon lat order.
st_distance_sphere(
  st_geomfromtext('point(1 90)'),
  st_geomfromtext('point(100 90)')) AS dist_sphere_0,
-- ST_Distance with SRID => lat lon order.  
st_distance(
  st_geomfromtext('point(90 1)', 4326),
  st_geomfromtext('point(90 100)', 4326)) AS dist,
-- ST_Distance_Sphere with SRID => lat lon order.  
st_distance(
  st_geomfromtext('point(90 1)', 4326),
  st_geomfromtext('point(90 100)', 4326)) AS dist_sphere_4326;

All these return 0 or value very close to 0, since all these points have latitude 90 and describe North Pole.

MariaDB does not implement geographic SRID AFAIK, thus it allows any x/y values, ST_Distance still returns incorrect (planar) distance even with SRID=4326, and the order for ST_Distance_Sphere always seem to be longitude-latitude.