Geographical latitude and longitude axis swapped on MySQL

[holtkamp]

The following links describe how MySQL interprets the coordinates of a POINT when a spatial reference identifier (SRID) is involved, making the type a "Geography" instead of a "Geometry":

• https://dba.stackexchange.com/a/229689/199259, linking to:
  • https://dba.stackexchange.com/a/182626/199259

MySQL follows the specification properly, but this is not the "de facto" standard (as used by this library), in summary:

All GIS implementations must do Point(x,y) for projected coordinates which is (long,lat). But, on geodetic coordinate systems there is some disagreement about what to do. MySQL (and SQL Server) do (lat,long) but PostGIS maintains (long,lat) everywhere.

This is the observed behavior:

• no SRID isset / geometric types:
  • \CrEOF\Spatial\PHP\Types\Geometry\Point.x is populated with x: correct
  • \CrEOF\Spatial\PHP\Types\Geometry\Point.y is populated with y: correct
• SRID is set / geographical types:
  • \CrEOF\Spatial\PHP\Types\Geography\Point.x is populated with latitude: incorrect
  • \CrEOF\Spatial\PHP\Types\Geography\Point.y is populated with longitude: incorrect

This behavior also propagates to LineStrings, Polygons, MultiPolygons, etc.

A simple way to check whether database table columns are configured/populated properly, is to load the table using MySQL Workbench and use the Spatial Viewer to browse the content of a table.

Possible solution

A possible solution might be to force the axis-order to long-lat which this library expects when loading and saving the data in CrEOF\Spatial\DBAL\Platform\Mysql:

use CrEOF\Spatial\DBAL\Types\GeographyType;
class MySql extends AbstractPlatform
{
    /**
     * For Geographic types MySQL follows the WKT specifications and returns (latitude,longitude) while (x,y) / (longitude,latitude) is expected.
     *
     * @var string
     */
    private const AXIS_ORDER_OPTION = 'axis-order=long-lat';

    public function convertToPHPValueSQL(AbstractSpatialType $type, $sqlExpr)
    {
        return $type instanceof GeographyType
            ? sprintf('ST_AsBinary(%s, "%s")', $sqlExpr, self::AXIS_ORDER_OPTION)
            : sprintf('ST_AsBinary(%s)', $sqlExpr);
    }

    public function convertToDatabaseValueSQL(AbstractSpatialType $type, $sqlExpr)
    {
        return $type instanceof GeographyType
            ? sprintf('ST_GeomFromText(%s, %d, "%s")', $sqlExpr, $type->getSrid(), self::AXIS_ORDER_OPTION)
            : sprintf('ST_GeomFromText(%s)', $sqlExpr);
    }
}