2

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;
2

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

| improve this answer | |
  • 1
    Or indeed this answer, see edit 2. – John Powell Oct 30 '19 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! – geozelot Oct 30 '19 at 13:55
  • Thanks @JohnPowell I knew there was a good lateral join answer somewhere, but din't come up in the search. – HeikkiVesanto Oct 30 '19 at 14:06
  • @ThingumaBob. When was that fixed, ie, which version of Postgres (as it is Postgres, not Postgis functionality, correct)? It always used to be the case that unless one side of the <-> operator was a constant, you needed something like ST_DWithin to limit it, such as the case when you have a.geom <-> b.geom. – John Powell Oct 30 '19 at 15:16
  • 1
    Edited answer to use <-> – HeikkiVesanto Oct 30 '19 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.