3

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

Tony Sansom
  • 109
  • 7

1 Answers1

5

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.

JGH
  • 41,794
  • 3
  • 43
  • 89
  • Do you mean cast the geometry column(s) into a new geography column(s), index them and then use t1.geog <-> t2.geog ? – Tony Sansom Oct 07 '20 at 13:44
  • You can do that, or you can create an index on the geometry cast to a geography (create index geogidx ON mytable USING gist(geography(geom));). If you need to use the distances in meters in several places, I would convert the column to geography. If you need it just once, I would keep the geometry and add an index on the cast. – JGH Oct 07 '20 at 13:59