title: Haversine query into Oracle
body: <p>I'm currently having to go through my queries and transfer them over to using Oracle rather than SQLSERVER and i'm a bit stuck with this query which i'm using from <a href="https://stackoverflow.com/questions/5362475/haversine-formula-using-sql-server-to-find-closest-venue-vb-net">here</a></p>
  
  <pre><code>SELECT TOP 1 * FROM ( SELECT o.outcode AS lead_postcode, v.location, 
  v.location_name, v.outcode AS venue_postcode, 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 venue_postcodes v, uk_postcodes o 
  WHERE o.outcode = @nrpostcode ) i WHERE distance&lt;100 ORDER BY distance
  </code></pre>
  
  <p>Now I know this is a horrible query to look at but Oracle seems to be having a lot of problems with it.</p>
  
  <p>Firstly it doesn't like the <code>E</code> in <code>6371E</code> and all the subsequent <code>E</code>'s</p>
  
  <p>Secondly it doesn't like the <code>square</code> function so I decided to use the <code>power</code> function but this still gave me errors.</p>
  
  <p>Thirdly it doesn't like the <code>radians</code> function</p>
  
  <p>Fourthly it doesn't like the <code>TOP 1</code> part so I had changed this to use <code>ROWNUM</code> in the <code>WHERE</code> clause</p>
  
  <p>I'm completely lost as to what to do here.</p>
  
  <p>Any ideas as to what I can do to make it work?</p>
  
  <p>Thanks in advance</p>