title: How to select using lat/lng radius with a SQL join
body: <p>I have a <a href="http://weblogs.asp.net/jimjackson/archive/2009/02/13/calculating-distances-between-latitude-and-longitude-t-sql-haversine.aspx" rel="nofollow">SQL function</a> that calculates the distance between two points using their lat/lng coordinates. Using this function, I can shortlist a set of locations from a large set (<em>GEOCODES table</em>) based on their distance for a given point like so:</p>
  
  <pre><code>DECLARE @LAT1 float, @LNG1 float,
  SET @LAT1 = '34.169540'
  SET @LNG1 = '-92.590990'
  
  SELECT E.* FROM GEOCODES E
  WHERE dbo.fnGetDistance(E.lat, E.lng, @LAT1, @LNG1, 'miles') &lt; '20'
  </code></pre>
  
  <p>I have a second table (<em>GEOMILES</em>) with distances between points in the form:</p>
  
  <pre><code>LOCATION1  | STATE1  |  LOCATION2  |  STATE2  | DISTANCE
  -----------+---------+-------------+----------+---------
  New York   |   NY    |    Boston   |   MA     |   220
  </code></pre>
  
  <p>What I'm trying to achieve is a <code>SELECT</code> query that returns a distance of 220 if a departure point is within 20 miles of New York, and an arrival point is within 20 miles of Boston. So far, I have this but it returns nothing when I know from checking manually that the lat/lng points etc add up. Any help is much appreciated!</p>
  
  <pre><code>DECLARE @FROM_LOC VARCHAR(100), @FROM_STA VARCHAR(2), @TO_LOC VARCHAR(100), @TO_STA VARCHAR(2)
  
  SET @FROM_LOC = 'NEWARK'
  SET @FROM_STA = 'NJ'
  
  SET @TO_LOC = 'MEDFORD'
  SET @TO_STA = 'MA'      
  
  SELECT G.DIST FROM GEOMILES G, GEOCODES E
  JOIN GEOCODES C ON C.asciiname = @FROM_LOC AND C.admin1 = @FROM_STA
  JOIN GEOCODES D ON D.asciiname = @TO_LOC AND D.admin1 = @TO_STA
  WHERE dbo.fnGetDistance(E.lat, E.lng, C.lat, C.lng, 'miles') &lt; '20'
  AND dbo.fnGetDistance(E.lat, E.lng, D.lat, D.lng, 'miles') &lt; '20'
  </code></pre>