Nearest neighbours (KNN) from a selected point

Question

I have a small point dataset, I perform the following I get the 10 KNN from the coordinate. How do I first select a point from the dataset and with that select the 10 nearest neighbours (not including the original point - if possible)?

SELECT spatial_id, geom
FROM rhp_tvp.spatial
ORDER BY geom <-> 'SRID=23033;POINT(280100 4683200)'::geometry
LIMIT 10

Answer 1

With a SELF JOIN, in the form of an inner join if you know the distance. Or a cross if you don't.

SELECT spatial_id, geom
FROM rhp_tvp.spatial AS rt1
JOIN rhp_tvp.spatial AS rt2
  USING ST_DWithin( rt1.geom, rt2.geom, MAX_DISTANCE )
WHERE rt1 ... -- SELECT POINT.
ORDER BY rt1.geom <-> rt2.geom
LIMIT 10

If you don't know the max distance of one point to another,

SELECT spatial_id, geom
FROM rhp_tvp.spatial AS rt1
CROSS JOIN rhp_tvp.spatial AS rt2
WHERE rt1 ..  -- SELECT POINT
ORDER BY rt1.geom <-> rt2.geom
LIMIT 10;