calculate min distance of each shop's location sql query

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?

Solution

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!!