PostGIS combine ST_DistanceSphere with Nearest Neighbor search

Question

I have two tables both have point geometry with SRID 4326 (WGS84) I need to find the nearest point in 1 table to every point in another table

PostGIS KNN search is written

t1.geometry <-> t2.geometry AS distance;

as I'm using WGS84 distance is in degrees I'd like distance to be in meters

https://postgis.net/docs/ST_DistanceSphere.html takes two points and calculates the distance between them like

SELECT round(CAST(ST_DistanceSphere(t1.geometry, t2.geometry ) As numeric),2) As distance

How do I combine the two functions? This errors

round(CAST(ST_DistanceSphere(t1.geometry <-> t2.geometry ) As numeric),2) As dist

ERROR: function st_distancesphere(double precision) does not exist

PostGIS version: 3.0

Answer 1

You have two choices: you can swap <-> for st_distanceSphere, or you can use geography

select a.id, b.id, st_distanceSphere(a.geom,b.geom)
from a,b
order by st_distanceSphere(a.geom,b.geom);

select a.id, b.id, a.geom::geography <-> b.geom::geography
from a,b
order by a.geom::geography <-> b.geom::geography;

Let's not forget that <-> makes use of spatial indexes and is therefore more efficient for KNN search. You would have to index the geographies though.

It is important not to use <-> directly on lat-long geometries, as it uses planar arithmetic in the CRS units, so it would compute distances in "degrees" which is meaningless (a degree of latitude doesn't have the same ground length as a degree of longitude). By casting to geography, the unit is meters.