DROP TABLE coordinates;
DROP FUNCTION near;
DROP FUNCTION dist;
DROP TYPE result;
DROP TYPE res_record;

CREATE TABLE coordinates (
    id NUMBER PRIMARY KEY,
    latitude NUMBER,
    longitude NUMBER
);

BEGIN
    FOR i IN 1..1000000 LOOP
        INSERT INTO coordinates VALUES(i, (dbms_random.VALUE * 180) - 90, dbms_random.VALUE * 180);
    END LOOP;
END;

CREATE OR REPLACE FUNCTION dist(
    x1 NUMBER, y1 NUMBER, z1 NUMBER,
    x2 NUMBER, y2 NUMBER, z2 NUMBER
)
RETURN NUMBER
IS
    r1 NUMBER;
    r2 NUMBER;
    a NUMBER;
    b NUMBER;
BEGIN
    r1 := SQRT(x1*x1+y1*y1+z1*z1);
    r2 := SQRT(x2*x2+y2*y2+z2*z2);
    a := ACOS((x1*x2+y1*y2+z1*z2)/r1/r2);
    b := LN(r1/r2);
    RETURN SQRT(a*a+b*b);
END;

CREATE TYPE res_record IS object (
    id NUMBER,
    latitude NUMBER,
    longitude NUMBER,
    distance NUMBER
);

CREATE TYPE result IS TABLE OF res_record;

CREATE OR REPLACE FUNCTION near(
    lat NUMBER, lon NUMBER, n NUMBER
)
RETURN result IS
    res result;
BEGIN
    SELECT res_record(id, latitude, longitude, distance) BULK COLLECT INTO res FROM (
        SELECT  c.*,
                dist(
                    6371*SIN(c.latitude)*COS(c.longitude),
                    6371*SIN(c.latitude)*SIN(c.longitude),
                    6371*COS(c.latitude),
                    6371*SIN(lat)*COS(lon),
                    6371*SIN(lat)*SIN(lon),
                    6371*COS(lat)
                ) AS distance
        FROM    coordinates c
        ORDER BY distance)
    WHERE ROWNUM <= n;
    RETURN res;
END;