calculate min distance of each shop's location sql query

Question

I have a table which contains X,Y Coordinates of each shop. I want to find min distance with each other. I mean nearest shop.

For Ex: (Desired Output)

Shop_ID      Nearest_Shop_ID 
Shop_1       Shop_5
Shop_2       Shop_8
Shop_3       Shop_4


Select SHOP_ID, Longtitude, Latitude From PARTNER_ADDRESSES
group by SHOP_ID, Longtitude, Latitude;

This is my shop coordinates table;

SHOP_ID     LONGTITUDE  LATITUDE
38599       32.815282   39.882793
38613       25.965545   42.166315
38682       31.845157   37.419859
38686       34.027568   38.370871

I have a formula which could calculate distance between two location (with using latitude and longtitude). This is the formula;

(NVL(6387.7,0) * ACOS((sin(NVL(Latitude1,0) / 57.29577951) * SIN(NVL(Latitude2,0) / 57.29577951)) +
        (COS(NVL(Latitude1,0) / 57.29577951) * COS(NVL(Latitude2,0) / 57.29577951) *
         COS(NVL(Longtitude2,0) / 57.29577951 - NVL(Longtitude1,0)/ 57.29577951))))

Latitude1  : Shop 1's Latitude value
Longtitude1: Shop 1's Longtitude value

Latitude2  : Shop 2's Latitude value
Longtitude2: Shop 2's Longtitude value

Is it possible to write this kind of sql which will calculate each shop distances and list min distance value for each shop?

Answer 1

You can use self join and analytical function.

Select s1, s2 from
(Select a1.shop_id as s1, a2.shop_id as s2,
Row_number() 
over (partition by a1.shop_id
  Order by <your_formula>) as rn
From partner_adresses a1
Join partner_adresses a2
On (a1.shop_id <> a2.shop_id))
Where rn = 1;

Cheers!!