Three T-SQL Approaches to Distance Matching

Finding the Closest Store: Three T-SQL Approaches to Distance Matching

Three ways to match people to their nearest store in T-SQL — great-circle distance from latitude/longitude, arithmetic ZIP code matching when you have no coordinates at all, and a Pythagorean approximation compared against the trigonometric method. Same sample data, three techniques, one decision framework for picking the right one.

Published · Tags: SQL, T-SQL, Mapping, Geolocation, Distance Calculation, Pythagorean Theorem, Latitude and Longitude, ZIP Codes, Nearest Store Matching

"Which store is closest to this person?" sounds like a simple question, but the right T-SQL answer depends entirely on what data you actually have. If you're lucky enough to have latitude and longitude for both people and stores, the problem is straightforward geometry. If all you have is a five-digit ZIP code, you need a different strategy — one that degrades gracefully as your address data gets less precise. And if you do have coordinates, you still have a choice between a fast approximation and a more accurate (but pricier) trigonometric calculation.

This post walks through all three approaches against the same sample dataset — three people and eight stores in the Dallas–Fort Worth and Houston areas — so you can compare the SQL side by side and pick the method that fits the data you're working with.

Which method should I use? If you have latitude/longitude for both sides, use Method 1. If you only have ZIP codes and no coordinates, use Method 2. If you have coordinates and want to know whether a cheaper formula is accurate enough for your use case, read Method 3's comparison of the Pythagorean and trigonometric results.

Method 1: Closest Store by Latitude and Longitude

This is the simplest case: every person and every store has a latitude and longitude. Finding the closest match breaks into two parts. First, build a manageable set of candidate pairs — rather than cross-joining every person against every store, this query joins on the first three digits of ZIP code (the Sectional Center Facility, or SCF) on the theory that if a person and a store aren't even in the same SCF, they're probably not within practical driving distance anyway. Second, calculate the great-circle distance for every candidate pair, and keep only the shortest one per person.

create table #people (people varchar(15), zip char(5), latitude float, longitude float)
insert into #people values ('Me','76013','32.7358055','-97.130459')
insert into #people values ('You','75010','33.030107','-96.882747')
insert into #people values ('Her','77014','29.969033','-95.445635')

create table #store (store varchar(15), zip char(5), latitude float, longitude float)
insert into #store values ('MyStore1','77017','29.698602','-95.276513')
insert into #store values ('MyStore3','75019','32.985711','-96.99879')
insert into #store values ('MyStore5','76017','32.654885','-97.176705')
insert into #store values ('MyStore2','76016','32.683165','-97.216687')
insert into #store values ('MyStore4','76018','32.662862','-97.112965')
insert into #store values ('MyStore6','75010','33.010988','-96.941053')
insert into #store values ('MyStore7','77011','29.744389','-95.315496')
insert into #store values ('MyStore8','77015','29.798333','-95.163711')

select identity(int,1,1) as ident
, peop.people
, peop.zip as people_zip
, peop.latitude as people_latitude
, peop.longitude as people_longitude
, stor.store
, stor.zip as store_zip
, stor.latitude as store_latitude
, stor.longitude as store_longitude
, acos( sin(stor.latitude*pi()/180)*sin(peop.latitude*pi()/180) + cos(stor.latitude*pi()/180)*cos(peop.latitude*pi()/180)*cos(peop.longitude*pi()/180-stor.longitude*pi()/180) ) * 6371 as distance_km
, acos( sin(stor.latitude*pi()/180)*sin(peop.latitude*pi()/180) + cos(stor.latitude*pi()/180)*cos(peop.latitude*pi()/180)*cos(peop.longitude*pi()/180-stor.longitude*pi()/180) ) * 6371 * 0.621371 as distance_mi
into #stage
from #people as peop (nolock)
left outer join #store as stor (nolock)
on left(peop.zip,3)=left(stor.zip,3)

select * from #stage

select results.*
from #stage as results
inner join #stage as dedup
on results.ident=dedup.ident
and dedup.ident = (
select top 1 dedup2.ident
from #stage as dedup2
where dedup.people=dedup2.people
order by dedup2.distance_km, dedup2.ident)

Step	Purpose
SCF join — left(zip,3)	Pairs each person with every store sharing the same first three ZIP digits, creating a manageable many-to-many candidate set instead of a full cross-join.
distance_km / distance_mi	Great-circle distance for each candidate pair, calculated with acos(), sin(), and cos() against the earth's radius of 6,371 km.
identity(int,1,1)	A row number added to the staged results so the final de-dup step has a stable tiebreaker if two stores are ever exactly the same distance from a person.
Final self-join	Keeps only the row with the smallest distance_km per person — the single closest store.

Method 2: Closest Store by Arithmetic Difference in ZIP Codes

What if you don't have coordinates at all — just ZIP codes? You can still estimate proximity by working outward from the most precise match available: try a full five-digit ZIP match first, fall back to a four-digit match, then fall back further to a three-digit SCF match. When you're matching on fewer than five digits, you'll typically get more than one candidate at that level, so you need a tiebreaker — and the smallest arithmetic difference between the two ZIP codes, treated as plain integers, is a serviceable one. coalesce() then takes the most precise match available for each person, falling back only when a more precise level has no match at all.

This is an approximation, not geocoding. Treating ZIP codes as plain numbers and taking the smallest arithmetic difference is a workaround for when true coordinates aren't available — it is not a substitute for geocoding ZIP centroids. If your store list is sparse, the extra accuracy of real coordinates may not change the outcome much anyway, but for dense store networks, prefer Method 1 if you can get the latitude/longitude data.

create table #people (people varchar(15), zip char(5), id int)
insert into #people values ('Me','76013',1)
insert into #people values ('You','75010',2)
insert into #people values ('Her','77014',3)

create table #store (store varchar(15), zip char(5), id int)
insert into #store values ('MyStore1','77017',1)
insert into #store values ('Mystore3','75019',3)
insert into #store values ('MyStore5','76017',5)
insert into #store values ('MyStore2','76016',2)
insert into #store values ('Mystore4','76018',4)
insert into #store values ('MyStore6','75010',6)
insert into #store values ('MyStore7','77011',7)
insert into #store values ('MyStore8','77015',8)

select identity(int,1,1) as ident
, peop.people
, peop.zip as people_zip
, peop.[id] as people_id
, coalesce(stor.store,stor4.store,stor3.store) as store
, coalesce(stor.zip,stor4.zip,stor3.zip) as store_zip
, coalesce(stor.[id],stor4.[id],stor3.[id]) as store_id
into #results
from #people as peop
left outer join #store as stor (nolock)
on peop.zip = stor.zip
left outer join #store as stor4 (nolock)
on left(peop.zip,4) = left(stor4.zip,4)
and stor4.[id] = (
  select top 1 stor4x.[id]
  from #store as stor4x (nolock)
  where left(peop.zip,4) = left(stor4x.zip,4)
  order by abs(convert(int,peop.zip)-convert(int,stor4x.zip)), stor4x.[id]
)
left outer join #store as stor3 (nolock)
on left(peop.zip,3) = left(stor3.zip,3)
and stor3.[id] = (
  select top 1 stor3x.[id]
  from #store as stor3x (nolock)
  where left(peop.zip,3) = left(stor3x.zip,3)
  order by abs(convert(int,peop.zip)-convert(int,stor3x.zip)), stor3x.[id]
)

select * from #results

Step	Purpose
stor join	Exact five-digit ZIP match — the most precise level, used first when it exists.
stor4 join	Four-digit prefix match, narrowed with a correlated subquery that picks the single closest store by smallest abs() arithmetic ZIP difference.
stor3 join	Three-digit SCF prefix match — the least precise fallback level, same arithmetic-difference tiebreaker as the four-digit join.
coalesce(...)	Returns the result from the most precise join that actually found a match, falling back to four digits, then three, only when needed.

Method 3: Pythagorean Approximation vs. Trigonometric Distance

If you do have coordinates, the great-circle formula from Method 1 isn't your only option. The Pythagorean theorem can approximate the same distance with simpler arithmetic — no trigonometric functions required — by treating latitude and longitude as if they were plotted on a flat grid. The query below runs both calculations side by side on the same data so you can see exactly how far apart their answers land.

Why Two Formulas Give Different Answers

The earth is a sphere, not a flat grid. Two points separated by one degree of longitude represent different real-world distances depending on how close they are to the equator versus the poles, because lines of longitude converge near the poles. The trigonometric method accounts for this curvature using acos(), sin(), and cos(), and is more accurate over long distances — its accuracy weakens only near the poles. The Pythagorean method ignores curvature entirely, applying a² + b² = c² to the raw latitude/longitude difference. It's simpler and cheaper to compute, but its error grows as the distance between points increases.

create table #people (people varchar(15), zip char(5), latitude float, longitude float)
insert into #people values ('Me','76013','32.7358055','-97.130459')
insert into #people values ('You','75010','33.030107','-96.882747')
insert into #people values ('Her','77014','29.969033','-95.445635')

create table #store (store varchar(15), zip char(5), latitude float, longitude float)
insert into #store values ('MyStore1','77017','29.698602','-95.276513')
insert into #store values ('MyStore3','75019','32.985711','-96.99879')
insert into #store values ('MyStore5','76017','32.654885','-97.176705')
insert into #store values ('MyStore2','76016','32.683165','-97.216687')
insert into #store values ('MyStore4','76018','32.662862','-97.112965')
insert into #store values ('MyStore6','75010','33.010988','-96.941053')
insert into #store values ('MyStore7','77011','29.744389','-95.315496')
insert into #store values ('MyStore8','77015','29.798333','-95.163711')

select identity(int,1,1) as ident
, peop.people
, peop.zip as people_zip
, peop.latitude as people_latitude
, peop.longitude as people_longitude
, stor.store
, stor.zip as store_zip
, stor.latitude as store_latitude
, stor.longitude as store_longitude
, acos( sin(stor.latitude*pi()/180)*sin(peop.latitude*pi()/180) + cos(stor.latitude*pi()/180)*cos(peop.latitude*pi()/180)*cos(peop.longitude*pi()/180-stor.longitude*pi()/180) ) * 6371 as distance_km
, acos( sin(stor.latitude*pi()/180)*sin(peop.latitude*pi()/180) + cos(stor.latitude*pi()/180)*cos(peop.latitude*pi()/180)*cos(peop.longitude*pi()/180-stor.longitude*pi()/180) ) * 6371 * 0.621371 as distance_mi
, sqrt( power(stor.latitude-peop.latitude,2) + power(stor.longitude-peop.longitude,2) ) * 69.172 * 1.60934 as pythagorean_km
, sqrt( power(stor.latitude-peop.latitude,2) + power(stor.longitude-peop.longitude,2) ) * 69.172 as pythagorean_mi
into #stage
from #people as peop (nolock)
left outer join #store as stor (nolock)
on left(peop.zip,3)=left(stor.zip,3)

select * from #stage

select results.*
from #stage as results
inner join #stage as dedup
on results.ident=dedup.ident
and dedup.ident = (
select top 1 dedup2.ident
from #stage as dedup2
where dedup.people=dedup2.people
order by dedup2.distance_km, dedup2.ident)

Column	Formula	Notes
distance_km / distance_mi	Trigonometric (great-circle)	Accounts for earth's curvature; preferred for longer distances.
pythagorean_km / pythagorean_mi	Flat-plane Pythagorean	No trig functions; cheaper to compute, but error grows with distance.

"As the crow flies": all three methods in this post calculate straight-line distance, not driving distance. None of them account for roads, bridges, one-way streets, or other real-world obstacles. If you need actual driving distance or drive time, you'll need a routing API layered on top of whichever method you choose here.

Comparing All Three Methods

Method	Data Required	Accuracy	Best For
1. Latitude/Longitude (Trigonometric)	Lat/long for people and stores	Most accurate; small error only near the poles	You have real coordinates and want the most reliable result
2. Arithmetic ZIP Difference	ZIP codes only — no coordinates needed	Rough estimate; degrades as match precision falls from 5 to 3 digits	No coordinates available, and a "good enough" match is acceptable
3. Pythagorean Approximation	Lat/long for people and stores	Close to trigonometric at short range; diverges at longer distances	You have coordinates and want faster math for large-scale scoring

What You Can Do With These

• Use Method 1 as your default whenever you have real coordinates — it's the most reliable across the widest range of distances
• Fall back to Method 2 when onboarding a new data source that only provides ZIP codes, and re-run Method 1 later once geocoding is available
• Use the Pythagorean formula from Method 3 to pre-score a large candidate set cheaply, then re-rank the top few candidates with the trigonometric formula for a final answer
• Compare the distance_km and pythagorean_km columns side by side on your own data to see how much the two formulas diverge at the distances you actually care about
• Within a single metropolitan area, expect all three methods to land within about half a mile of each other — the differences matter most at longer range