title: Oracle circle distance search: missing results
body: <p>EDIT: (If u want see the old problematic, see at the bottom of this question)</p>
  
  <p>With your helps i modified my statement:</p>
  
  <pre><code>SELECT *
    FROM (
            SELECT 
                  dest.ZC_ZIP, 
                  dest.ZC_LOCATION_NAME,
                  ACOS(
                      SIN(RADIANS(src.ZC_LAT)) * SIN(RADIANS(dest.ZC_LAT)) 
                      + COS(RADIANS(src.ZC_LAT)) * COS(RADIANS(dest.ZC_LAT))
                      * COS(RADIANS(src.ZC_LON) - RADIANS(dest.ZC_LON))
                      ) * 6371 AS DISTANCE
            FROM USER.ZC_COORDINATES dest
            CROSS JOIN USER.ZC_COORDINATES src
            WHERE src.ZC_ID = 
                  (SELECT 
                          ZC_ID
                          FROM USER.ZC_COORDINATES
                          WHERE ZC_ZIP = '64289'
                          GROUP BY ZC_ID
                  )
                  AND
                  (dest.ZC_ID &lt;&gt; src.ZC_ID OR dest.ZC_ID = src.ZC_ID)
        )
    HAVING DISTANCE &lt;= 25 /* km */
    ORDER BY DISTANCE;
  </code></pre>
  
  <p>After this i get the error: "Radians" are invalid identifier.</p>
  
  <p>Reason: Oracle doenst implements that function (MySQL does!).</p>
  
  <p>So i search and found this Code and use it:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION calc_distance(pLat1 NUMBER, pLon1 NUMBER, pLat2 NUMBER, pLon2 NUMBER)
   RETURN NUMBER IS
  
  -- r is the spherical radius of earth in Kilometers 
  cSpherRad CONSTANT NUMBER := 6371;
  
  -- The spherical radius of earth in miles is 3956
  a        NUMBER;
  vLat     NUMBER;
  vLat1Rad NUMBER;
  vLat2Rad NUMBER;
  vLon     NUMBER;
  vLon1Rad NUMBER;
  vLon2Rad NUMBER;
  
  BEGIN
    /*
    Most computers require the arguments of trigonometric functions to be
    expressed in radians. To convert lon1, lat1 and lon2,lat2 from
    degrees,minutes, seconds to radians, first convert them to decimal
    degrees. To convert decimal degrees to radians, multiply the number
    of degrees by pi/180 = 0.017453293 radians/degrees.
    */
  
    vLat1Rad := pLat1 * 0.017453293;
    vLat2Rad := pLat2 * 0.017453293;
    vLon1Rad := pLon1 * 0.017453293;
    vLon2Rad := pLon2 * 0.017453293;
  
    vLon := vLon2Rad - vLon1Rad;
    vLat := vLat2Rad - vLat1Rad;
  
    a := POWER(SIN(vLat/2),2) + COS(vLat1Rad) * COS(vLat2Rad) * POWER(SIN(vLon/2),2);
  
    /*
    The intermediate result c is the great circle distance in radians.
    Inverse trigonometric functions return results expressed in radians.
    To express c in decimal degrees, multiply the number of radians by
     180/pi = 57.295780 degrees/radian.
    The great circle distance d will be in the same units as r.
    */
  
    RETURN ROUND(cSpherRad * 2 * ATAN2(SQRT(a), SQRT(1-a)),1);
  EXCEPTION
    WHEN OTHERS THEN
      RETURN 999;
  END calc_distance;
  /
  </code></pre>
  
  <p>Successfully compilation.</p>
  
  <p>Now the correct modification without group by errors:</p>
  
  <pre><code>SELECT *
    FROM (
            SELECT 
                  dest.ZC_ZIP AS ZIP, 
                  dest.ZC_LOCATION_NAME AS LOCNAME,
                  calc_distance(src.ZC_LAT, src.ZC_LON, dest.ZC_LAT, dest.ZC_LON) AS DISTANCE
            FROM BASE.ZC_COORDINATES dest
            CROSS JOIN BASE.ZC_COORDINATES src
            WHERE src.ZC_ID = 
                  (SELECT 
                          ZC_ID
                          FROM BASE.ZC_COORDINATES
                          WHERE ZC_ZIP = '64289'
                          GROUP BY ZC_ID
                  )
                  AND
                  (dest.ZC_ID &lt;&gt; src.ZC_ID OR dest.ZC_ID = src.ZC_ID)
        )
    HAVING DISTANCE &lt;= 25 /* km */
    GROUP BY ZIP, LOCNAME, DISTANCE;
    --ORDER BY DISTANCE;
  </code></pre>
  
  <p>Whats the problem now?</p>
  
  <p>Ok, on my old locals MySQL system i get these sql results (correct solution):</p>
  
  <pre><code>zc_zip  zc_location_name  distance     
  64291   Darmstadt         0 
  64297   Darmstadt         0 
  64289   Darmstadt         0 
  64283   Darmstadt         0 
  64285   Darmstadt         0 
  64295   Darmstadt         0 
  64293   Darmstadt         0 
  64287   Darmstadt         0 
  64347   Griesheim, Hessen 5.385545333978872 
  64331   Weiterstadt       5.671376373674798 
  64367   M├╝hltal, Hessen  6.992565870106159 
  64319   Pfungstadt        7.7621346384241585 
  64380   Ro├ƒdorf bei Darmstadt 8.134881711148836 
  64372   Ober-Ramstadt     8.421977582053422 
  64390   Erzhausen, Hessen 9.419234655429722 
  64572   B├╝ttelborn       9.739076077060767 
  64409   Messel            9.962635340560048 
  63329   Egelsbach, Hessen 11.274247321555363 
  64342   Seeheim-Jugenheim 11.743554066395413 
  64560   Riedstadt         12.135040456984065 
  64404   Bickenbach        12.597317271640899 
  64521   Gro├ƒ-Gerau       12.637599535794854 
  64397   Modautal          13.260629389533909 
  64846   Gro├ƒ-Zimmern     13.270265030251164 
  63225   Langen (Hessen)   13.745513110307494 
  </code></pre>
  
  <p>Thats i get from the oracle system:</p>
  
  <pre><code>ZIP     LOCNAME     DISTANCE
  64293   Darmstadt   0
  64283   Darmstadt   0
  64295   Darmstadt   0
  64285   Darmstadt   0
  64297   Darmstadt   0
  64287   Darmstadt   0
  64289   Darmstadt   0
  64291   Darmstadt   0
  </code></pre>
  
  <p>You see, some reults are missing.
  I think the problem is the "GROUP BY" line in the oracle statement, which are needed.
  So i cant delete this line. But how i can get my other results? I'm realy confused :(</p>
  
  <hr>
  
  <p>(Old problematic) </p>
  
  <p>At the moment i program a circle zip search for my employee.
  My tool works under construction on my MySQL Database (local).</p>
  
  <p>Now i migrate my OpenGeoDB into our oracle database (successfully) and want run my sql statement which works perfectly on my local MySQL database.</p>
  
  <p>The full error message:</p>
  
  <pre><code>ORA-00904: "DISTANCE": invalid indentifier
  00904. 00000 -  "%s: invalid identifier"
  *Cause:    
  *Action:
  Error in line: 20 col: 7
  </code></pre>
  
  <p>My SQL Statement:</p>
  
  <pre><code>SELECT 
      dest.zc_zip, 
      dest.zc_location_name,
      ACOS(
          SIN(RADIANS(src.zc_lat)) * SIN(RADIANS(dest.zc_lat)) 
          + COS(RADIANS(src.zc_lat)) * COS(RADIANS(dest.zc_lat))
          * COS(RADIANS(src.zc_lon) - RADIANS(dest.zc_lon))
      ) * 6371 as distance
  FROM USER.ZC_COORDINATES dest
  CROSS JOIN USER.ZC_COORDINATES src
  WHERE src.zc_id =
      (
      SELECT zc_id
      FROM USER.zip_coordinates
      WHERE zc_zip = '64289' /* Platzhalter fuer PLZ */
      GROUP BY zc_zip
      )
  
  AND (dest.zc_id &lt;&gt; src.zc_id OR dest.zc_id = src.zc_id)
  HAVING distance &lt;= 25 /* km */
  ORDER BY distance;
  </code></pre>
  
  <p>What I do to solve this problem?:
  I switch the word "distance" with the full math calc, but after this, Oracle call invalid identifier "Radians"</p>
  
  <p>Thats my problem because I dont know how I can solve this problem.</p>
  
  <p>Have someone any ideas to solve that problem?</p>
  
  <p>Thanks.</p>