Geospatial Queries in SQL: From Basic Distance to Complex Polygons

You've probably punched an address into an app a dozen times this week. Finding coffee, tracking a delivery, you name it. Ever wonder how that *actually* works under the hood? It's not magic. It's just data. Your database can crunch location numbers like nothing, turning raw coordinates into real-world answers. This stuff moves beyond "find the nearest store." We're talking delivery route optimization, territory management, environmental mapping. Your data isn't just rows and columns anymore. It's a map. Let's learn how to read it.

The ABCs of Shapes: Points, Lines, and Polygons in Code

First things first. Forget regular data types for a second. Geospatial has its own vocabulary. At its core, you've got three big ones. A **Point** is a single location: latitude and longitude. A **LineString** is a sequence of points, like a road or a river. A **Polygon** is a closed shape, like a store's delivery zone or a national park boundary. Tools like PostGIS add these types to PostgreSQL. Storing them is step one. Querying them is where the fun starts. `SELECT * FROM coffee_shops WHERE ST_DWithin(location, your_house, 1609);` Boom. That's a search for shops within a mile. It's that simple to start.

Beyond "As The Crow Flies": Calculating What Matters

"Show me everything within 5 miles." Sounds straightforward. But is it? The naive way is a straight line, Euclidean distance. On a map, that's a perfect circle. In reality, you follow roads. Rivers get in the way. The earth is a sphere, not a flat piece of paper. A simple `ST_Distance` gets you the basics. But that flat-earth math breaks down over long distances. That's where `ST_Distance_Sphere` or `ST_DistanceSpheroid` come in. They account for the planet's curvature. Crucial for logistics, travel, any serious analysis. It's the difference between a theoretical circle and a real-world service area.

Does This Pin Fall Inside That Area? The Magic of Intersections.

This is it. The killer app. Distance is cool, but relationships are everything. Is this customer address inside our service area? Did that ship cross into restricted waters? Which salesperson owns this lead's zip code? You answer these with spatial relationships. Functions like `ST_Within`, `ST_Contains`, and `ST_Intersects` are your workhorses. `SELECT lead_id FROM prospects WHERE ST_Within(address_point, sales_territory_polygon);`. That's powerful. You're not just filtering by a state name in a text column. You're using precise, mathematical boundaries. It's exact. It scales. It handles shapes no text description ever could.

Making It Fast: Why Your Spatial Queries Are Probably Slow

Here's the thing. Once you have a million polygons and ten million points, those cool queries grind to a halt. Checking if a point is inside a polygon is computationally expensive. Doing it ten million times sequentially? Forget it. You need a spatial index. In PostGIS, that's usually a GiST index on your geometry column. It's like a regular database index, but for shapes. It creates a bounding box for every geometry, letting the database instantly eliminate 99% of impossible matches *before* doing the hard math. Creating one is dead simple: `CREATE INDEX idx_table_geom ON your_table USING GIST (geom_column);`. It's not an optimization. It's a requirement. Do it first.