Spatial Types

Spatial Types

Spatial Types, SRIDs, and Format Conversion

A geometry carries an SRID naming its spatial reference system: SRID 0 is a flat, unitless plane, and SRID 4326 is WGS 84, the GPS system, in degrees on an ellipsoid (MySQL 524 knows 5,238 systems). A column declared SRID 4326 rejects other SRIDs (error 3643 for an SRID-0 point), and only such a column lets the optimizer use its SPATIAL (R-tree) index, which also needs NOT NULL.

A stores table in WGS 84, and the axis-order trapSQL

CREATE TABLE stores (id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  city VARCHAR(40) NOT NULL, loc POINT NOT NULL SRID 4326, SPATIAL INDEX sp_loc (loc));
INSERT INTO stores (city, loc) VALUES
  ('Kota Kinabalu', ST_GeomFromText('POINT(5.9804 116.0735)', 4326)),
  ('Kuala Lumpur',  ST_GeomFromText('POINT(3.1390 101.6869)', 4326)),
  ('Singapore',     ST_GeomFromText('POINT(1.3521 103.8198)', 4326)),
  ('Tokyo',         ST_SRID(POINT(139.6503, 35.6762), 4326)),
  ('Sao Paulo',     ST_GeomFromText('POINT(-46.6333 -23.5505)', 4326, 'axis-order=long-lat'));
INSERT INTO stores (city, loc) VALUES ('Oops', ST_GeomFromText('POINT(116.07 5.98)', 4326));
SELECT city, ST_AsText(loc) AS wkt, ST_Longitude(loc) AS lon FROM stores;

Output

ERROR 3617 (22S03): Latitude 116.070000 is out of range in function st_geomfromtext. It must
  be within [-90.000000, 90.000000].
+---------------+--------------------------+----------+
| city          | wkt                      | lon      |
+---------------+--------------------------+----------+
| Kota Kinabalu | POINT(5.9804 116.0735)   | 116.0735 |
| Kuala Lumpur  | POINT(3.139 101.6869)    | 101.6869 |
| Singapore     | POINT(1.3521 103.8198)   | 103.8198 |
| Tokyo         | POINT(35.6762 139.6503)  | 139.6503 |
| Sao Paulo     | POINT(-23.5505 -46.6333) | -46.6333 |
+---------------+--------------------------+----------+

EPSG defines 4326 as latitude first, so MySQL's WKT is POINT(lat lon), while GeoJSON and many map libraries put longitude first. Three inputs agree above: lat-long WKT, WKT with axis-order=long-lat, and ST_SRID(POINT(x, y), 4326), where POINT() takes longitude as x. A swap fails loudly only when the would-be latitude exceeds 90 (the Oops row); Sao Paulo's text without the option was accepted as a point in the South Atlantic. HEX(loc) shows the storage format, a 4-byte SRID (E6100000) and then WKB; ST_AsWKB(), ST_SwapXY() and ST_Transform(loc, 3857) (Web Mercator) convert it.

MENU