CREATE INDEX IDX_ROUTES_COORD ON ROUTES (LAT, LON);

CREATE OR ALTER PROCEDURE sp_i_routes_find_near (
  in_lat dm_latitude,
  in_lon dm_longitude,
  in_radius_from dm_km,
  in_radius_to dm_km,
  in_radius_step dm_km)
RETURNS (
  out_pnt_id TYPE OF COLUMN routes.id)
AS
  DECLARE VARIABLE var_lb_lat dm_latitude;
  DECLARE VARIABLE var_lb_lon dm_longitude;
  DECLARE VARIABLE var_rt_lat dm_latitude;
  DECLARE VARIABLE var_rt_lon dm_longitude;
BEGIN
  out_pnt_id = NULL;
  WHILE (:in_radius_from <= :in_radius_to) DO BEGIN
    EXECUTE PROCEDURE pkg_geo.circum_rect (
      :in_lat,
      :in_lon,
      :in_radius_from
    ) RETURNING_VALUES (
      :var_lb_lat,
      :var_lb_lon,
      :var_rt_lat,
      :var_rt_lon
    );
    FOR
      SELECT
        rt."ID"
      FROM
        routes rt
      WHERE
        rt.lat BETWEEN :var_lb_lat AND :var_rt_lat AND
        rt.lon BETWEEN :var_lb_lon AND :var_rt_lon
      ORDER BY
        pkg_geo.distance(
          rt.lat,
          rt.lon,
          :in_lat,
          :in_lon
        )
      ROWS 1
      INTO
        :out_pnt_id
    DO
      EXIT;
    in_radius_from = :in_radius_from + :in_radius_step;
  END
END