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?