| 1 | create or replace function FindNearestAirport(
|
|---|
| 2 | longitude_P numeric,
|
|---|
| 3 | latitude_P numeric,
|
|---|
| 4 | distanceKm int
|
|---|
| 5 | ) returns table (
|
|---|
| 6 | AirportCode char(3),
|
|---|
| 7 | AirportName text,
|
|---|
| 8 | AirportLocation geography(Point, 4326),
|
|---|
| 9 | Distance_KM numeric
|
|---|
| 10 | ) as $$
|
|---|
| 11 | begin
|
|---|
| 12 | return query
|
|---|
| 13 | select a.code, a.name, a.location, (ST_Distance(a.location, ST_MakePoint(longitude_P, latitude_P)::geography) / 1000)::numeric
|
|---|
| 14 | from airport as a
|
|---|
| 15 | where ST_DWithin(a.location, ST_MakePoint(longitude_P, latitude_P)::geography, distanceKm * 1000)
|
|---|
| 16 | order by a.location <-> ST_MakePoint(longitude_P, latitude_P)::geography;
|
|---|
| 17 | end;
|
|---|
| 18 | $$ language plpgsql;
|
|---|
| 19 |
|
|---|
| 20 | select * from FindNearestAirport(21.62, 41.96, 100000);
|
|---|
