The Cartographer’s Confession: How PostGIS Turned Me from a SQL Hack into a Spatial Artist

Source: Dev.to

A Confession

For years I treated geospatial data like a messy closet—shove everything in, slam the door, and pray nobody asks for a “nearby” anything. Then came the project that broke me: a real‑time delivery tracker with 50 k points and a naive query:

WHERE sqrt((x1-x2)^2 + (y1-y2)^2)

But wait—that still scans everything? Right. Because we forgot the most important part.

Indexing – The Real Art

A normal B‑tree index is like alphabetizing a bookshelf—great for title = X. Spatial data, however, is a map. You don’t search a map by flipping pages; you fold it, you zoom, you glance at regions.

Enter GiST (Generalized Search Tree)

Think of GiST as an origami master that folds your 2‑D (or 3‑D, 4‑D) space into a tree of bounding boxes. When you query “find points within 1 km,” PostGIS uses the index to discard entire continents of data instantly.

CREATE INDEX idx_restaurants_geom
  ON restaurants
  USING GIST (geom);

That one line turned my 45‑second query into 80 ms. I literally laughed out loud. My cat left the room.

Note: GiST indexes are slightly slower to update (INSERT/UPDATE/DELETE) than B‑trees. For a write‑heavy geospatial table you’ll need to tune autovacuum or batch your writes. More on that later.

Art lesson: A GiST index is like the legend on a map—it doesn’t show every tree, but it tells you exactly how to find the forest.

A Handy Toolkit

PostGIS ships with hundreds of functions. You only need a dozen to be dangerous. Below is my everyday toolkit, refined through actual pain.

Real‑world example

Find the 10 closest coffee shops to a user, within 5 km, ordered by distance.

SELECT name,
       ST_Distance(geom, user_geom) AS dist
FROM   coffee_shops
WHERE  ST_DWithin(geom, user_geom, 5000)
ORDER BY geom  user_geom   -- KNN operator
LIMIT 10;

The “ operator (the K‑Nearest Neighbor operator) is index‑assisted magic. Without it, PostGIS would calculate distance for every shop within 5 km, then sort. With it, the index walks the tree and returns candidates in approximate order. It’s not exact until the final sort, but it’s blindingly fast.

Geometry vs. Geography

My rule of thumb

1. Store as geometry with SRID 4326 (lat/lon). It’s lightweight.
2. Cast to geography only when you need Earth‑aware calculations: geom::geography.
3. Index both – a GiST on geography is larger and slightly slower, but still useful for global queries.

Pro tip: For large tables with global queries, add a geog column as geography(Point, 4326) and index that. Then you can write clean queries like:

SELECT *
FROM sensors
WHERE ST_DWithin(
        geog,
        ST_MakePoint(lon, lat)::geography,
        50000   -- 50 km
      );

No casting in the query means the index gets used without hesitation.

Silent Killers of Performance

Even after indexing everything, you might see slowdown in production. Common culprits:

1. Implicit casting in the WHERE clause

   WHERE ST_DWithin(geom::geography, ...)   -- cast before index lookup

   The cast forces a full scan because PostGIS can’t use a GiST on geometry for a geography query. Keep types consistent.

2. Using ST_Distance for filtering

   WHERE ST_Distance(geom, point) < radius   -- forces full scan

   ST_Distance calculates the distance for every row before the filter can be applied. Use ST_DWithin instead.

3. Be mindful of type casting – it can silently bypass your index.

4. For massive tables, consider clustering or storing simplified envelopes.

With these practices, your geospatial queries will go from “it works on my laptop” to “this scales like a dream.” Happy mapping!

4 seconds to 200 ms

After two years of wrestling with PostGIS, I’ve developed a kind of intuition. It’s like learning to see negative space in a drawing. Here’s my mental checklist before writing any spatial query:

1. Draw it first – I keep a whiteboard or a quick QGIS window. Visualizing bounding boxes and intersections saves hours.

2. Start with the index – Write the query assuming the index will do the heavy lifting. Filter early, refine late.

3. Test with a point – Run

   EXPLAIN (ANALYZE, BUFFERS) <your_query>;

   on a single coordinate. Look for “Seq Scan” – if you see it, your index isn’t being used.

4. Think in meters, store in degrees – Use geography for distances, geometry for operations. Cast explicitly.

5. Batch your writes – A GiST index rebuild on 1 M rows takes minutes. Do it nightly, not per insert.

PostGIS isn’t just a library. It’s a lens that changes how you see data. Suddenly every “near me” button, every delivery route, every heatmap becomes a solvable puzzle instead of a performance nightmare.

The journey from sqrt(lat^2 + lon^2) to elegant ST_DWithin with a GiST index is the difference between a child’s crayon scribble and a Monet. You’ve learned the brushstrokes. Now go paint some maps.

And when someone asks you, “Can you find all points within a polygon?” – smile, open your terminal, and whisper: “Watch this.”