1

Following example here I have tried to get nearest neighbours where code is;

CREATE TABLE model.hub_spoke_spokes3f AS
SELECT 
   hub.id AS hub_id,
   spoke.id,
   spoke.point_geom,
   ST_Distance(hub.geom, spoke.point_geom) AS dist
FROM hub_data.geom_data_unique AS hub
JOIN LATERAL (
SELECT id,
     point_geom
FROM spoke_data.data_set
WHERE ST_DWithin(hub.geom, point_geom, 150000) --geom is 277000, units metres 
AND hub.id <> id
ORDER BY hub.geom <-> point_geom
LIMIT 100
) AS spoke
ON TRUE;

I then compared the results with QGIS Hub and spoke function which I was trying to replicate.

enter image description here

As screen shot below the results differ. The lines are created by QGIS but the spokes (smaller dots) are from the query above. Whats causing the difference?

mapping dom
  • 1,492
  • 1
  • 11
  • 24
  • Are both the input geometries points, because, the <-> operator calculates closest distance to any point, while the <#> operator calculates distance using bounding boxes, which for anything other than points or circles, will likely give different results. I have absolutely no idea how the QGIS function operates, so this is a guess. – John Powell Jan 06 '19 at 19:03
  • Thank you, yes both are point. Documentation for QGIS function here; https://github.com/qgis/QGIS/blob/master/python/plugins/processing/algs/qgis/HubDistanceLines.py – mapping dom Jan 06 '19 at 21:57

1 Answers1

3

Order of the join is crucial here; your query selects the 100 nearest points (with different id) to each hub (and cross-joins them with the respective hub), whereas you rather want to select the one closest hub to each point.

The LATERAL join will execute the right hand (table) expression for each row of the left hand table.

Running

CREATE TABLE model.hub_spoke_spokes3f AS
  SELECT b.id AS hub_id,
         a.id AS spoke_id,
         ST_Distance(a.geom, b.geom) AS dist,
         a.point_geom
         -- ST_MakeLine(b.geom, a.geom) AS spoke_geom
  FROM   spoke_data.data_set AS a
  JOIN LATERAL (
    SELECT q.id,
           q.geom
    FROM   hub_data.geom_data_unique AS q
    ORDER BY
           q.geom <-> a.geom
    LIMIT  1
  ) AS b
  ON true;

will assign the hub_id of the closest hub to each of your points. With the (out-commented) ST_MakeLine you will get the corresponding lines between them.

I used to get just a bit better performance with the ON true construct; since PostgreSQL > 10, this has become equal to <table_a>, LATERAL (<expression>) or <table_a> CROSS JOIN LATERAL (<expression>). Not sure why.

Note that this is slightly different to the 'Join by lines (hub lines) - Hub and Spokes' function, that connects points to hubs with lines (spokes) based on a common attribute. If I get that right, you would then just join both tables on e.g. hub.id = spoke.hub_id.

geozelot
  • 30,050
  • 4
  • 32
  • 56