title: Haversine formula using SQL server to find closest venue - vb.net
body: <p>I am grabbing a postcode from a form. I can then convert this postcode to lng,lat coordinates as I have these stored in a table.</p>
  
  <p><code>SELECT lng, lat from postcodeLngLat WHERE postcode = 'CV1'</code></p>
  
  <p>I have another table which stores the lng,lat of a selection of venues.</p>
  
  <p><code>SELECT v.lat, v.lng, v.name, p.lat, p.lng, p.postcode, 'HAVERSINE' AS distance FROM venuepostcodes v, postcodeLngLat p WHERE p.outcode = 'CB6' ORDER BY distance</code> </p>
  
  <p>What I am trying to do is create a datagrid which shows the distance of each venue from the postcode (CV1 in this case). I know that the Haversine formula should do what I am trying to achieve but I'm lost as to where I should start incorporating it into my query. I think the formula needs to go where I've put <code>'HAVERSINE'</code> in the query above.</p>
  
  <p>Any ideas?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p><code>SELECT o.outcode AS lead_postcode, v.venue_name, 6371.0E * ( 2.0E *asin(case when 1.0E &lt; (sqrt(square(sin(((RADIANS(CAST(o.lat AS FLOAT)))-(RADIANS(CAST(v.lat AS FLOAT))))/2.0E)) + (cos(RADIANS(CAST(v.lat AS FLOAT))) * cos(RADIANS(CAST(o.lat AS FLOAT))) * square(sin(((RADIANS(CAST(o.lng AS FLOAT)))-(RADIANS(CAST(v.lng AS FLOAT))))/2.0E))))) then 1.0E else (sqrt(square(sin(((RADIANS(CAST(o.lat AS FLOAT)))-(RADIANS(CAST(v.lat AS FLOAT))))/2.0E)) + (cos(RADIANS(CAST(v.lat AS FLOAT))) * cos(RADIANS(CAST(o.lat AS FLOAT))) * square(sin(((RADIANS(CAST(o.lng AS FLOAT)))-(RADIANS(CAST(v.lng AS FLOAT))))/2.0E))))) end )) AS distance FROM venuepostcodes v, outcodepostcodes o WHERE o.outcode = 'CB6' ORDER BY distance</code></p>