Skip to content
Tutorials Center
My learning

Topics

Lesson 39 of 75 ยท JSON and Modern Features

Spatial Data and Geometry

Store places and areas in MySQL with POINT and POLYGON columns, understand SRID 4326 and axis order, measure distances, use spatial indexes and exchange GeoJSON.

"Which stores are within 5 km of me?" "Is this delivery address inside our service area?" "Show me every listing in the visible part of the map." These are questions about where things are, and MySQL can answer them in SQL. It has geometry column types (POINT, LINESTRING, POLYGON and collections of them), a large set of ST_* functions, and an R-tree spatial index in InnoDB.

This lesson explains the types, the single biggest source of confusion (coordinate reference systems and the order of latitude and longitude), the standard "radius search" pattern, and GeoJSON for talking to maps. Geometry types and functions are MySQL server features, and the browser's SQLite has none of them, so the MySQL code is read-only. The live examples do the same jobs with plain latitude and longitude columns, which is also a perfectly good design for many applications.

The geometry family

POINT One location, like a shop.
LINESTRING A path, like a bus route.
POLYGON An area, like a delivery zone.
MULTI and collections Several shapes in one value.

One column holds one geometry value per row.

1 / 2

Geometry types and well-known text

MySQL follows the OGC standard. A geometry is created from well-known text (WKT), a readable format:

WKT Shape
POINT(10 20) One position.
LINESTRING(0 0, 5 5, 10 5) A connected path.
POLYGON((0 0, 10 0, 10 10, 0 10, 0 0)) An area. The first ring is the outer boundary and must close by repeating its first point. Further rings are holes.
MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION Several of the above in one value.

Internally the server stores a geometry as a 4-byte SRID followed by a binary form (WKB), so you read and write it with functions: ST_GeomFromText(wkt, srid) and ST_PointFromText parse text, and ST_AsText(g) gives text back. There are also functions to test and measure: ST_X, ST_Y, ST_Latitude, ST_Longitude, ST_Distance, ST_Contains, ST_Within, ST_Intersects, ST_Area, ST_Length and more.

SRID: what do the numbers mean?

A pair like (10, 20) means nothing until you say which coordinate system it uses. MySQL labels every geometry with an SRID, a spatial reference system id:

  • SRID 0 is the default: an abstract, unit-less flat plane. Distances are plain Euclidean distances in whatever units you used. Good for game maps and floor plans.
  • SRID 4326 is WGS 84, the system GPS and web maps use: positions on the Earth in degrees of latitude and longitude. Calculations take the curvature of the Earth into account, and distances come out in meters.

Geometries with different SRIDs cannot be compared or combined: mixing 0 and 4326 raises an error. Declare the SRID on the column and MySQL enforces it for every row:

SQL
CREATE TABLE stores (
  id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name     VARCHAR(100) NOT NULL,
  location POINT NOT NULL SRID 4326,
  SPATIAL INDEX idx_stores_location (location)
);

The axis-order trap

For SRID 4326, MySQL 8 follows the official definition of the system: the first coordinate is latitude, the second is longitude. Most people (and GeoJSON, and most map libraries) think "x then y", that is, longitude then latitude. Mixing the two up is the most common spatial bug: Madrid is at latitude 40.4, longitude -3.7, and with swapped axes it lands in the ocean, or fails outright, since a latitude above 90 is rejected with an "out of range" error.

Be explicit every time you create a point from longitude and latitude:

SQL
INSERT INTO stores (name, location) VALUES
  ('Madrid', ST_PointFromText('POINT(-3.7038 40.4168)', 4326, 'axis-order=long-lat')),
  ('Oslo',   ST_PointFromText('POINT(10.7522 59.9139)', 4326, 'axis-order=long-lat'));

SELECT name, ST_Latitude(location) AS lat, ST_Longitude(location) AS lon,
       ST_AsText(location) AS wkt_lat_first,
       ST_AsText(location, 'axis-order=long-lat') AS wkt_lon_first
FROM stores;

ST_Latitude and ST_Longitude always return what their names say, so use them to check your data. Plain ST_X and ST_Y return coordinates in the axis order of the reference system, which for 4326 is the opposite of what the letters suggest. Also note that building a point with POINT(lon, lat) and then labeling it with ST_SRID(..., 4326) makes the same mistake, because the first number becomes the latitude.

For two points on the Earth, ST_Distance_Sphere(p1, p2) returns the distance in meters along a sphere (default radius about 6,370,986 m). It is the simplest way to answer "how far apart are these?". On SRID 4326 values, recent 8.0 versions can also use ST_Distance, which uses the ellipsoid and is slightly more accurate. Check the documentation for your exact version when you combine either function with SRID 4326 columns, since argument handling changed between releases.

A "stores within 5 km" query looks natural:

SQL
SELECT name
FROM stores
WHERE ST_Distance_Sphere(location, @me) <= 5000;

It is correct, but the function is evaluated for every row, and it cannot use the spatial index by itself. The standard fix is a two-step filter. First a cheap bounding box that the R-tree index can answer, then the exact distance on the few rows that remain:

SQL
SET @me = ST_PointFromText('POINT(-3.7038 40.4168)', 4326, 'axis-order=long-lat');

SELECT name
FROM stores
WHERE ST_Contains(
        ST_GeomFromText('POLYGON((-3.76 40.37, -3.64 40.37, -3.64 40.46, -3.76 40.46, -3.76 40.37))',
                        4326, 'axis-order=long-lat'),
        location)                                -- uses the spatial index
  AND ST_Distance_Sphere(location, @me) <= 5000; -- exact check on the survivors

The box is computed in your application from the center and the radius: one degree of latitude is about 111.32 km everywhere, and one degree of longitude is about 111.32 km times the cosine of the latitude, so boxes get wider in degrees toward the poles. The same two-step idea works without any spatial feature. Here is a version with ordinary lat and lon columns. The haversine formula plays the part of ST_Distance_Sphere:

SQL Distance from Madrid with the haversine formula

Press Run (or Ctrl + Enter) to see the result here.

The result lists Lisbon at about 502 km, Paris 1,053 km, Berlin 1,869 km and Oslo 2,388 km. It uses the same sphere radius as MySQL's default, so the numbers agree with what ST_Distance_Sphere would report for these coordinates.

The two-step radius search, live

Now the full pattern: an ordinary B-tree index on (lat, lon) for the box, then the exact distance. A B-tree can narrow only the first column well (a spatial R-tree handles both at once), but even so it cuts the candidate set a lot:

SQL Bounding box first, exact distance second

Press Run (or Ctrl + Enter) to see the result here.

The plan shows SEARCH cities USING INDEX idx_cities_lat_lon (lat>? AND lat<?), an index search on the latitude range. The rectangle query returns Lisbon, Madrid and Porto. The radius search returns only Madrid (0 km) and Porto (about 423 km). Lisbon passes the cheap box test but is about 502 km away, so the exact distance rejects it, which is precisely why the second step exists. Paris, Berlin and Oslo never make it past the box.

Spatial indexes

A SPATIAL INDEX in InnoDB is an R-tree: it indexes bounding rectangles, so it can answer "which shapes overlap this region?" quickly. Rules:

  • The indexed column must be NOT NULL.
  • For the optimizer to use the index, the column should declare an SRID (as in POINT NOT NULL SRID 4326). Without it, the index may not be used for geographic queries.
  • The index serves relationship functions such as ST_Contains, ST_Within, ST_Intersects and the MBR* family (MBRContains, and so on). It does not serve ST_Distance_Sphere or ST_Distance in a WHERE clause. Use a region filter first, as shown above.
  • Check with EXPLAIN that the key column shows your spatial index (EXPLAIN).

Do you need geometry at all? If you only store points and only run radius queries, two DECIMAL(9,6) columns (about 10 cm of precision) and the box-then-distance pattern are simple, portable and easy for every tool to read. Reach for geometry types when you store areas or paths, need containment and intersection tests, or want the standard functions. For serious GIS work (routing, large polygons, advanced projections), PostGIS in PostgreSQL has a much deeper toolbox.

GeoJSON

GeoJSON is the JSON format that map libraries speak. A point is {"type": "Point", "coordinates": [lon, lat]}, always longitude first. MySQL converts in both directions and handles the axis order for you:

SQL
-- geometry to GeoJSON
SELECT ST_AsGeoJSON(location) FROM stores WHERE name = 'Madrid';

-- GeoJSON to geometry (the default SRID of the result is 4326)
SET @shape = ST_GeomFromGeoJSON('{"type": "Point", "coordinates": [-3.7038, 40.4168]}');

Look at the output once to confirm the order for your data. The shape of such a document can be built in SQL, which is handy for an API that returns a map layer:

SQL Building GeoJSON features

Press Run (or Ctrl + Enter) to see the result here.

You get one Feature per place with coordinates as [lon, lat], and then a single FeatureCollection holding both, which a map library can draw directly. Notice that the longitude comes first in the array.

What ran live here

The haversine distance, the bounding-box filter with an index plan and the GeoJSON documents ran in SQLite with ordinary columns and JSON functions. POINT and POLYGON types, SRIDs, ST_* functions, SPATIAL INDEX and ST_AsGeoJSON are MySQL server features and are shown as read-only code.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In MySQL 8 with SRID 4326, which order does a WKT point use by default?

    MySQL follows the axis order of the reference system, which is latitude first for 4326. Use 'axis-order=long-lat' when your data is longitude first.
  2. 2. What does ST_Distance_Sphere return?

    It returns the distance in meters, using a sphere with a default radius of about 6,370,986 meters.
  3. 3. Why is WHERE ST_Distance_Sphere(location, @me) <= 5000 slow on a large table?

    Distance functions are not index-aware. Filter first with a bounding box or region the R-tree can answer, then apply the exact distance.
  4. 4. What does a spatial index in InnoDB require of its column?

    Spatial indexed columns must be NOT NULL. Declaring SRID 4326 on the column lets the optimizer use the index for geographic queries.
  5. 5. In GeoJSON, which order do the coordinates of a point have?

    GeoJSON always lists longitude first, then latitude, so it is the opposite of the default SRID 4326 order in MySQL.

Summary

  • MySQL has POINT, LINESTRING, POLYGON and multi-types, created from WKT with ST_GeomFromText and read with ST_AsText, ST_Latitude and ST_Longitude.
  • SRID 0 is a flat unit-less plane. SRID 4326 is the Earth in degrees, with latitude first by default. Pass axis-order=long-lat when your data is longitude first, and never mix SRIDs.
  • Distances on the Earth come in meters from ST_Distance_Sphere (or ST_Distance on 4326 in recent versions).
  • A SPATIAL INDEX needs a NOT NULL column (with a declared SRID) and serves containment and intersection tests, not distance. Filter with a box first, then check the exact distance.
  • GeoJSON is longitude first. Two decimal columns remain a fine design for simple radius searches.

Next: Storage Engines Overview.