3

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

1 Answer 1

3

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

10
  • 1
    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!
    – geozelot
    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
  • 1
    Edited answer to use <-> Oct 30, 2019 at 17:09
  • 1
    @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
    – geozelot
    Oct 30, 2019 at 17:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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