It has been almost twenty years since Major League Baseball added a new team. Now there’s talk of one being added in the West. Should that happen, one of the many questions MLB will have to answer is: Which of the West’s three divisions makes the most sense?



<h2 class="wp-block-heading">Adding a Baseball Team in the West</h2>



Several cities have been considered for the next Major League Baseball team, and San Antonio is a frequent nominee. Would San Antonio get added to the National League West, with the San Diego Padres, Colorado Rockies, and San Francisco Giants, or the American League West, with the Texas Rangers, Oakland Athletics, and Seattle Mariners?



Traveling to other cities and into and out of different time zones can be exhausting. This is why travel distance between San Antonio and the other cities in the division is such an important factor.



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-01-7.png" alt="Map of baseball stadiums" class="wp-image-78012"/></figure>



<h2 class="wp-block-heading">Building a Distance Metric</h2>



We can compare the effect on travel distance by adding San Antonio to each division. The travel distance can be computed as the Euclidean distance between cities using their latitude and longitude.



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-02-8.png" alt="Euclidean distance" class="wp-image-78006"/></figure>



Intuitively, teams like Seattle, which are geographically far from all the other teams, will cause problems with this approach. Another metric we can look at is the impact on the nearest neighbors of each team.



For each team, we’ll build a list of all the other teams, ordered by their geographical distance:



<pre class="wp-block-code"><code>select
 team_name
from
 mlb_stadiums
order by
 sqrt(power(source_lat - lat, 2) + power(source_long - long, 2))
limit 5</code></pre>



Here are the 5 closest stadiums to San Antonio:



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-03-8.png" alt="5 closest teams" class="wp-image-78000"/></figure>



With the two other teams in Texas being our closest neighbors, we might be tempted to look to the AL West as San Antonio’s home.



Unfortunately, the Seattle Mariners are also in the AL West and they rank 29th on San Antonio’s list of nearest neighbors. The only team further away being the Boston Red Sox!



A better way to think about the effect on the neighborhood is to ask the question in the opposite direction &#8211; if a city is added to the league, where does it rank on each team’s list?



<h3 class="wp-block-heading">Reversing the Ranking</h3>



We can build the ranked order for each team individually. First, let us compute the distances between each pair of cities: ​



<pre class="wp-block-code"><code>with
 distances as (
 select
 sqrt(power(d.lat - s.lat, 2) 
 + power(d.long - s.long, 2)) as distance
 , s.team_name as src
 , d.team_name as dest
 from
 mlb_stadiums s
 , mlb_stadiums d
 where
 s.team_name != d.team_name
 )</code></pre>





Now instead of ordering the entire list by distance, we want to&nbsp;partition&nbsp;the ordering by the source city: ​





<pre class="wp-block-code"><code>with ranking as (
 select
 src
 , dest
 , row_number() over(partition by src order by distance asc)
 from
 distances
)
select * from ranking where destination = 'San Antonio'</code></pre>





We can compare San Antonio’s position in the NL West:





<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-04-8.png" alt="San Antonio position" class="wp-image-77994"/></figure>



and in the AL West: 



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-05-6.png" alt="AL West position" class="wp-image-77988"/></figure>



 While San Antonio has its closest neighbors in the American League, everyone in the National League has San Antonio in their top 8 closest cities. So if you’re listening, MLB, our recommendation is the National League West for San Antonio!

Finding Nearest Neighbors in SQL

curve-image-unique-image-unique