Calculating distance between every entry in Table A and nearest record in Table B using PostGIS

I am new to PostGIS.

How do I calculate the distance and direction between every site (approx 30,000 record) and the nearest entity in a gazetteer (approx 300,000 records)?

Ideally a separate table would be created that contains the site_id, entity_id, distance between, direction between (entity & site). I have looked at an answer supplied by Tobias Herrmann in late 2014 - paraphrased below.

This code was written to do the calculation of distance, but is a work in progress.

``````SELECT site.gid AS gid_1, gazetteer.gid AS gid_2, ST_Distance(site.geom, gazetteer.geom) AS mindist
FROM table site, table gazetteer WHERE site.gid != gazetteer.gid AND ST_Distance(site.geom, gazetteer.geom) != 0
ORDER BY ST_Distance(site.geom, gazetteeer.geom)
LIMIT 1;
``````

Modified from:

PostGIS nearest point with LATERAL JOIN in PostgreSQL 9.3+

Lateral joins really are the best way to do nearest searches in PostGIS.

``````create table nearest_w_dir as
select
a.gid as site_id, ij.entity_id, ij.distance_between, ij.direction_between
from site_table a
left join lateral
(select b.gid as entity_id, ST_Distance(a.geom, b.geom) as distance_between,
ST_Azimuth(a.geom, b.geom) as direction_between
from gazetteer_table b
order by a.geom <-> b.geom asc nulls last
limit 1
) ij on true;
``````

You can read up on the direction from:

https://postgis.net/docs/ST_Azimuth.html

• Or indeed this answer, see edit 2. Oct 30, 2019 at 13:39
• One big thing to note: adding a second limiting expression (e.g. `ST_DWithin`, `a.geom && ST_Expand(b.geom, ...)` adds significant overhead to the highly optimized `<->` distance order. Don't use that syntax anymore! Oct 30, 2019 at 13:55
• Thanks @JohnPowell I knew there was a good lateral join answer somewhere, but din't come up in the search. Oct 30, 2019 at 14:06
• Edited answer to use <-> Oct 30, 2019 at 17:09
• @HeikkiVesanto actually, the whole point of that operator is that it doesn't; it will look up the index of the running table to preselect geometries, and calculates distance only to those where bbox comparison cannot decide. this does only work if it is used as the ordering clause, otherwise it will just calculate plain distance. using any other means of limiting the geometries to calculate distance on (e.g. `ST_DWithin`), together with e.g. `ST_Distance`, semantically does the same thing, however the size of the result set to calculate on is bound to what is found within the threshold Oct 30, 2019 at 17:15