Skip to content
Tutorials Center
My learning

Topics

Lesson 106 of 125 ยท Database Systems

Geospatial Data in SQL

Store locations in SQL, compute distances with the haversine formula, find the nearest places with a bounding box, and learn how PostGIS and SpatiaLite do it.

"Find the five closest coffee shops." "Which delivery zone is this address in?" "How many customers live within 20 km of a warehouse?" Questions about where things are come up in maps, logistics, real estate, ride sharing and many other apps. Databases can answer them, and in this lesson you will see how, starting with plain SQL that runs in your browser and ending with the specialized spatial extensions that production systems use.

You do not need any geography background. You need to know that a place on Earth is described by two numbers, and that the Earth is round, which is the one fact that makes distance calculations trickier than they first appear.

Describing a place: latitude and longitude

A point on the globe is a pair of angles:

  • Latitude is how far north or south of the equator you are, from -90 (south pole) to 90 (north pole).
  • Longitude is how far east or west of the prime meridian, from -180 to 180. East is positive.

The simplest storage is two REAL columns, as you learned in Data Types. Add a CHECK constraint so that impossible values are rejected (see Constraints).

SQL
CREATE TABLE places (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  lat REAL NOT NULL CHECK (lat BETWEEN -90 AND 90),
  lon REAL NOT NULL CHECK (lon BETWEEN -180 AND 180)
);

Latitude first or longitude first? People say "lat, lon" (Google Maps, most phones), while mathematical and spatial software, including PostGIS and GeoJSON, uses "x, y", which is lon, lat. Mixing the order puts Paris in Antarctica. Name your columns lat and lon and never rely on position.

Distance on a sphere: the haversine formula

You cannot measure distance between two cities with the Pythagorean theorem on raw degrees. One degree of latitude is always about 111 km, but one degree of longitude shrinks as you move toward the poles. This query shows how much:

SQL A degree of longitude is not a fixed length

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

At the equator a degree of longitude is about 111 km, but at 60 degrees north it is about half that. Treating degrees as flat coordinates would distort distances badly in northern cities.

The haversine formula handles this by treating the Earth as a sphere with radius about 6,371 km. For two points it works out the great-circle distance, the shortest path along the surface. In SQL it uses the trigonometry functions sin, cos, asin, sqrt and radians (see Numeric Functions):

TEXT
a = sinยฒ(ฮ”lat / 2) + cos(lat1) ยท cos(lat2) ยท sinยฒ(ฮ”lon / 2)
distance = 2 ยท R ยท asin(โˆša)
SQL Distance from Berlin to other cities

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

The self-join (see Self Joins) puts Berlin's coordinates next to every other row, and the formula runs once per row. Amsterdam, Warsaw and Vienna come out as the nearest neighbors, and Madrid is the farthest. The answer is a straight "as the crow flies" distance: roads, rivers and borders are not considered.

Real databases ship these functions so you do not copy the formula around. PostgreSQL has earth_distance in the cube/earthdistance extensions, MySQL has ST_Distance_Sphere, and PostGIS has ST_Distance on its geography type. Writing the formula yourself is still useful for small datasets and for understanding what those functions do.

Nearest places fast: the bounding box

The query above computes trigonometry for every row. That is fine for eight cities and hopeless for ten million shops. The standard trick is a bounding box: first discard everything outside a square around the search point using cheap comparisons on indexed columns, then run the exact formula only on the few survivors.

flowchart LR
    A["All places<br/>(millions)"] -->|"cheap filter:<br/>lat/lon between ..."| B["Candidates<br/>(hundreds)"]
    B -->|"exact haversine<br/>distance"| C["Within radius,<br/>sorted"]

A cheap, index-friendly filter shrinks the data before the expensive distance math runs.

To search within a radius of r km, the box extends r / 111.195 degrees north and south, and r / (111.195 ยท cos(lat)) degrees east and west. A composite index on (lat, lon) can then answer the box filter quickly (see Indexes).

SQL Cities within 900 km of Vienna

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

The result lists Vienna itself (0 km) and three cities within 900 km: Berlin, Warsaw and Rome. Paris, London and Madrid fall outside the box and never reach the exact check. Amsterdam passes the box but is just over 900 km away, so the exact check removes it. To get the five nearest places, drop the radius condition, widen the box until you have enough candidates, and finish with ORDER BY km LIMIT 5. The box is only a filter: the exact check afterwards is what makes the answer correct, because a box corner is farther away than the box edge.

Grouping by area with grid cells

Sometimes you do not need exact distances, just "how many per area". Rounding coordinates into grid cells and using GROUP BY gives a quick heat map, or the clusters on a zoomed-out map:

SQL Counting stores per one-degree grid cell

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

Real systems use finer and more uniform cell schemes, such as geohashes or H3, which turn a location into a short text or integer key. Nearby places share a key prefix, so an ordinary index on that column can find them. The idea is the same as the grid above.

Spatial databases: PostGIS and SpatiaLite

Plain latitude and longitude columns are enough for points. When you need shapes (a delivery zone is a polygon, a road is a line) and fast spatial queries over millions of rows, use a spatial extension:

  • PostGIS adds geometry and geography types to PostgreSQL, hundreds of ST_ functions, and spatial indexes.
  • SpatiaLite does the same for SQLite.
  • MySQL, SQL Server and Oracle have built-in spatial types with a similar ST_ function style.

The core concepts carry over between them:

Concept Meaning
Geometry types POINT, LINESTRING, POLYGON, and collections of them
SRID Numeric id of the coordinate system. 4326 is plain lon/lat on the WGS 84 globe (what GPS uses)
geometry vs geography Geometry works on a flat plane; geography works on the curved Earth and returns meters
Spatial index A tree (GiST in PostGIS, R-tree in SpatiaLite and MySQL) that finds shapes by location without scanning the table
Predicates ST_Contains, ST_Intersects, ST_DWithin: yes or no questions about shapes
Measures ST_Distance, ST_Area, ST_Length

Here is the whole earlier workflow in PostGIS. These snippets are read-only because the browser runner has no spatial extension:

SQL
CREATE EXTENSION postgis;

CREATE TABLE shops (
  id   bigserial PRIMARY KEY,
  name text NOT NULL,
  geog geography(Point, 4326) NOT NULL
);

CREATE INDEX shops_geog_idx ON shops USING gist (geog);

-- Note the order: longitude first, then latitude
INSERT INTO shops (name, geog)
VALUES ('Corner Cafe', ST_SetSRID(ST_MakePoint(16.374, 48.208), 4326));

-- Shops within 2 km of a point, nearest first
SELECT name,
       ST_Distance(geog, ST_SetSRID(ST_MakePoint(16.37, 48.21), 4326)::geography) AS meters
FROM shops
WHERE ST_DWithin(geog, ST_SetSRID(ST_MakePoint(16.37, 48.21), 4326)::geography, 2000)
ORDER BY meters
LIMIT 10;

ST_DWithin uses the spatial index for the box filter and the exact distance check in one step, which is the two-phase approach you built by hand. Asking "which zone is this point in?" is a join with a spatial predicate:

SQL
SELECT z.name
FROM delivery_zones AS z
WHERE ST_Contains(z.area, ST_SetSRID(ST_MakePoint(16.37, 48.21), 4326));

Common mistakes

  • Using Euclidean distance on degrees. Fine for a tiny area near the equator, wrong almost everywhere else.
  • Swapping latitude and longitude. Check one known point on a map after every import.
  • Comparing floating-point coordinates for equality. Use a distance threshold instead.
  • Forgetting the antimeridian. A bounding box that crosses longitude 180 needs special handling; spatial databases do it for you.
  • Treating distance as travel time. Great-circle distance ignores roads. Routing needs a graph, covered by tools such as pgRouting.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why can't you measure the distance between two cities by applying the Pythagorean theorem to latitude and longitude in degrees?

    One degree of longitude shrinks with cos(latitude), so flat degree math distorts distances, especially far from the equator.
  2. 2. What is the purpose of the bounding-box step before the haversine calculation?

    Simple range comparisons on indexed lat and lon columns shrink the data, and the exact distance is then computed only for the remaining candidates.
  3. 3. In PostGIS and GeoJSON, in which order are the coordinates of a point written?

    Spatial software uses x, y, which is longitude then latitude. Everyday speech and map apps say latitude first, which is a classic source of bugs.
  4. 4. What does SRID 4326 mean?

    An SRID identifies the coordinate system. 4326 is the standard lon/lat system used by GPS.
  5. 5. Which query asks "which delivery zone contains this point?"

    Point-in-polygon tests are spatial predicates such as ST_Contains, usually backed by a spatial index.

Summary

  • Store locations as lat and lon columns (with CHECK ranges), and keep the order straight: spatial software expects lon first.
  • Distances on the globe need the haversine formula (or a built-in spatial function), not Pythagoras on degrees.
  • For "nearby" queries, filter by a bounding box first so an index helps, then compute the exact distance on the survivors.
  • Grid cells and geohashes turn locations into groupable keys for quick counts and maps.
  • PostGIS, SpatiaLite and the built-in spatial types of other databases add shapes, SRIDs, spatial indexes and ST_ functions such as ST_DWithin and ST_Contains.

Next: Project: Sales Analysis, the first of the hands-on projects.