postgis - PosGIS KNN returns different results from QGIS hub spoke function

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.

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?

Answer

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 , LATERAL () or CROSS JOIN LATERAL (). 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.