1

I'd like to make the equivalent function of QGIS's distance to hub algorithms. Is ST_Distance the right approach to start with? Or is that algorithm transposable into something I can let Postgres PostGIS?

mapping dom
  • 1,492
  • 1
  • 11
  • 24
  • depends on the parameters you parse to St_Distance. If you parse the ST_Union of your hubs the result could be the same I guess. – RutgerH Dec 06 '18 at 12:36
  • it's not eqivalent, but has to be utilized in order to mimic the QGIS functionality; what you're after is a (K) Nearest Neighbor search. @RutgerH, your suggestion does work as expected; currently, the most performant (and versatile) solution to this, even on large tables, should however be a LATERAL JOIN construct using the <-> KNN operator; the spatial index will be used to it's full potential (whereas the on-the-fly ST_Union/ST_Collect geometry can't). check a working exampe (yes, it's my own answer...) for the general structure. – geozelot Dec 07 '18 at 10:40
  • @ThingumaBob many thanks for this, I've tried adapting your example but it's not giving the same results as QGIS as posted https://gis.stackexchange.com/questions/307701/posgis-knn-returns-different-results-from-qgis-hub-spoke-function – mapping dom Jan 06 '19 at 17:17

1 Answers1

1

Yeah should be very simple:

select a.id, b.id, st_distance(a.geom, b.geom)
from
hub_table a,
locations_table b

Which would get the distance to all points from all hubs.

HeikkiVesanto
  • 16,433
  • 2
  • 46
  • 68