I have a table that is a cross join on itself to find the distance between all points. The structure is basically.
initial table: Parcels
ParcelID, X, Y
the derived distance table is built via the attached code and it's structure is:
ParcelID1, ParcelID2, Distance.
I now need a table that has the following structure:
gihbor5, Distance1, Distance2...Distance5
There are 37 million records in the derived table. There are 6130 ParcelID's for which I need to calculate their 5 closest neighbors. How should I approach building the final output table?
Select p.parcelID as ParcelID,
l.parcelID as ParcelID2,
) as Distance
from liberty p
cross join liberty l
where p.parcelID <> l.parcelID