SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table dbo.WSource
(
    Id int identity,
    StoreName varchar(15),
    Lat float,
    Lon float,
    GeoLocation geography
)

create table WDest
(
    Id int identity,
    StoreName varchar(15),
    Lat float,
    Lon float,
    GeoLocation geography
)

insert into WSource (StoreName, Lat, Lon)
select 'Walmart1', 28.064924, -81.652854 union
select 'Walmart2', 28.073512, -81.655092 union
select 'Walmart3', 28.063939, -81.665558 union
select 'Walmart4', 28.061616, -81.630152

update WSource
set geolocation = geography::STPointFromText('POINT(' + CAST(Lon AS VARCHAR(20)) + ' ' + 
                    CAST(Lat AS VARCHAR(20)) + ')', 4326)


insert into WDest (StoreName, Lat, Lon)
select 'Walmart5', 28.078024, -81.675264 union
select 'Walmart6', 28.079128, -81.693943 union
select 'Walmart7', 28.089832, -81.642243 union
select 'Walmart8', 28.062409, -81.677583

update WDest
set geolocation = geography::STPointFromText('POINT(' + CAST(Lon AS VARCHAR(20)) + ' ' + 
                    CAST(Lat AS VARCHAR(20)) + ')', 4326)
16 rows affected

SELECT
  WSource.StoreName AS Source,
  WDest.StoreName AS ClosestToSource,
  WDest.DistanceToSource
FROM WSource
CROSS APPLY (
    SELECT TOP (1)
      WDest.*,
      WDest.geolocation.STDistance(WSource.geolocation) AS DistanceToSource
    FROM WDest
    WHERE WDest.geolocation.STDistance(WSource.geolocation) IS NOT NULL
    ORDER BY DistanceToSource
) WDest;


Source
ClosestToSource
DistanceToSource


Walmart1
Walmart8
2443.86397765091

Walmart2
Walmart5
2047.1880184087

Walmart3
Walmart8
1191.3082084792

Walmart4
Walmart7
3340.32606807059


SELECT
  w.*
FROM (
    SELECT
      WSource.StoreName AS Source,
      WDest.StoreName AS ClosestToSource,
      WSource.geolocation.STDistance(WDest.geolocation) AS DistanceToSource,
      ROW_NUMBER() OVER (PARTITION BY WSource.Id ORDER BY WDest.geolocation.STDistance(WSource.geolocation)) AS rn
    FROM WSource
    CROSS JOIN WDest
    WHERE WDest.geolocation.STDistance(WSource.geolocation) IS NOT NULL
) w
WHERE w.rn = 1;


Source
ClosestToSource
DistanceToSource
rn


Walmart1
Walmart8
2443.86397765091
1

Walmart2
Walmart5
2047.1880184087
1

Walmart3
Walmart8
1191.3082084792
1

Walmart4
Walmart7
3340.32606807059
1

Source	ClosestToSource	DistanceToSource
Walmart1	Walmart8	2443.86397765091
Walmart2	Walmart5	2047.1880184087
Walmart3	Walmart8	1191.3082084792
Walmart4	Walmart7	3340.32606807059