Does MySQL store point datatypes as LAT LNG or LNG LAT?

Constructor Syntax

Looking up the spatial function reference, you'll see

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.

This is touched on in the spec OpenGIS® Implementation Standard for Geographic information - Simple feature access - Part 2: SQL option,

  • For geodetic CRSs having an ellipsoidal 2-D coordinate system, the two-dimensional ellipsoidal coordinate system axes are geodetic latitude, positive northwards, and geodetic longitude, positive eastwards. Axis direction shall be ‘north’ and ‘east’ respectively.
  • For geodetic CRSs having a three-dimensional ellipsoidal coordinate system, the name and abbreviation of the horizontal axes in a WKT string shall follow the requirements in (ii). The vertical axis name shall be ‘ellipsoidal height’; the vertical axis abbreviation shall be ‘h’ and should be included when abbreviations for the horizontal axes are included.

Note the above words are found verbatim in Geographic information - Well-known text representation of coordinate reference systems

Even as far back as the 1.1 spec,

A spatial reference system, also referred to as a coordinate system, is a geographic (latitude-longitude), a projected (X,Y), or a geocentric (X,Y,Z) coordinate system.

That said, it seems PostGIS and Oracle and many third party libraries maintain (x,y,[z]) for all types of points. This is in violation of the spec for WKT, but is a pretty common convention. For example GeoJSON also does this,

Point coordinates are in x, y order (easting, northing for projected coordinates, longitude, and latitude for geographic coordinates)

And, (lat,long) ordering is explicitly defined by EPSG for SRSID 4326,.

A 2D or 3D coordinate system in which position is specified by geodetic latitude, geodetic longitude and (in the three-dimensional case) ellipsoidal height, used in Geographic CRSs.

You may also consider checking out Paul Ramsey's (Captain PostGIS) blog on this subject entitled, "Let's Call the Whole Thing Off"

Nicities

As a special note, MySQL brings two new functions to the mix,

  • ST_Longitude()
  • ST_Latitude()

These functions stand aside from ST_X(), and ST_Y() and require a geographic SRS or they throw an exception and error.

Storage

From the docs

Internally, MySQL stores geometry values in a format that is not identical to either WKT or WKB format. (Internal format is like WKB but with an initial 4 bytes to indicate the SRID.)

Storing is defined here.

Table 11.1 WKB Components Example

Component       Size    Value
Byte order      1 byte  01
WKB type        4 bytes 01000000
X coordinate    8 bytes 000000000000F03F
Y coordinate    8 bytes 000000000000F0BF

Tags:

Mysql

Spatial

Recent Posts