TSQL stored procedure to read data from 2 tables and insert result into 3rd table

Question

I have 3 tables:
Outlets (Out_id, Out_name, Out_Latitude, Out_Longitude)
Competition (Comp_Name, comp_Latitude, comp_Longitude)
Mapping (Out_Id, Out_Name, Comp_Name, Comp_Distance)

For each of the Out_ID I want to calculate the radial distance corresponding to each competition and if the radial distance is less than 5KM then the corresponding details should be inserted to the Mapping Table.

Example sample data
Outlet

Out_id	Out_name	Out_Latitude	Out_Longitude
1	ABC	32.445	72
2	DEF	56.667	78.332

Competition

Comp_Name	comp_Latitude	comp_Longitude
PQR	13.334	45.662
UVW	12.445	78.587

So for out_ID 1 if the Radial distance is less than 5 for both the competition and for Out_Id 2 only the first competition radial distance is less than 5 then the mapping table should look like below:

Mapping

Out_Id	Out_Name	Comp_Name	Comp_Distance
1	ABC	PQR	4.2
1	ABC	UVW	3
2	DEF	PQR	1

Formula used for distance calculation is below

Comp_Distance = (sqrt(power((comp_Latitude-Out_Latitude),2) + Power((comp_Longitude-Out_Longitude),2)))*111

How can I achieve this using T-SQL Stored procedure on synapse dedicated pool. All the data are dummy data here.

Answer 1

I couldn't figure out that the formula returns in meter or kilometer or etc. but I suppose you need to use something like this:

insert Mapping (Out_Id, Out_Name, Comp_Name, Comp_Distance)
select Out_Id, Out_Name, Comp_Name, Comp_Distance
from
(
  select
    o.Out_Id, o.Out_Name, c.Comp_Name,
    (sqrt(power((c.comp_Latitude - o.Out_Latitude), 2) + 
    power((c.comp_Longitude - o.Out_Longitude), 2))) * 111 as Comp_Distance
  from Outlets o
  cross join Competition c
) q
where Comp_Distance < 5

note: I suppose that the formula returns in kilometers.