Postgis – wrong ST_Distance with geography points

PostGIS: That Pesky ST_Distance and Those Tricky Geography Points

PostGIS, bless its heart, is a spatial powerhouse for PostgreSQL. It lets you do all sorts of cool things with geographic data. One of its star players is ST_Distance, which, as you might guess, calculates the distance between two points. But here’s the thing: when you’re dealing with geography types, things can get a little…wonky. You might scratch your head and wonder, “Why is this distance so off?” Let’s dive into why that happens and how to fix it.

See, the geography type in PostGIS is smart. It knows the Earth isn’t flat! It uses latitude and longitude on the WGS 84 spheroid to represent locations. This is great because it accounts for the Earth’s curve, especially when you’re measuring distances across continents. The ST_Distance function, when used with geography, figures out the shortest distance over that curved surface.

So, what’s the catch? Where do things go sideways? Well, the biggest gotcha is the units. ST_Distance, with geography types, spits out the answer in meters. Yep, meters! I’ve been burned by this more than once. You’re used to geometry types where the units depend on your spatial reference system (SRID), and suddenly you’re getting meters. If you’re expecting kilometers or miles, you’ll need to do a little converting. It’s like accidentally ordering a coffee in milliliters when you wanted a pint!

Another sneaky culprit is coordinate precision. PostGIS can handle super-precise coordinates, but even tiny differences can add up, especially over vast distances. Think of it like aiming a laser pointer at the moon – a minuscule change at your end makes a huge difference up there. So, make sure your coordinates are as accurate as possible. Garbage in, garbage out, as they say!

And speaking of accuracy, keep an eye on your SRIDs. Geography types are WGS 84 by default, but if you mix them with geometry types that have different SRIDs, you’re asking for trouble. PostGIS might try to convert things behind the scenes, but it’s always safer to convert everything to the same SRID yourself before calculating distances. Trust me, it’ll save you a headache. Or, you can cast your geometries to geography types – that works too!

Finally, remember that ST_Distance gives you the shortest distance along the spheroid. For some super-specific scenarios, you might actually want the great-circle distance – the shortest distance on a perfect sphere. The Earth isn’t a perfect sphere, but it’s close enough that the difference is usually negligible, especially for shorter hops. If you absolutely need that great-circle distance, PostGIS has functions like ST_DistanceSpheroid or ST_DistanceSphere where you can specify the shape you want to use.

So, the bottom line? ST_Distance with geography points is powerful, but it’s not foolproof. By remembering that it returns meters, double-checking your coordinate precision, keeping your SRIDs straight, and picking the right distance calculation, you can avoid those head-scratching moments and get the accurate results you need. Happy mapping!