Spatial SQL -> PostGIS. Find nearest point to the given coordinates

Here is PostGIS plpgsql function that searches point nearest to the given Lat/Long coordinates. In our example the search aims to find nearest service station. Data table includes geometry column [geom] that signifies station location in WGS-84-LL (geography data type).

CREATE OR REPLACE FUNCTION get_nearest_station
(IN x_long double precision, IN y_lat double precision) -- input parameters
RETURNS TABLE -- structure of output
(
  station_id integer,
  city_name character varying,
  station_name character varying,
  dist integer -- distance to the nearest station
) AS $$

BEGIN

RETURN QUERY

  SELECT id, city, station,
     CAST
     (st_distance_sphere(geom, st_setsrid(st_makepoint(x_long,y_lat),4326)) AS INT)
     AS d
  FROM stations
  ORDER BY geom <-> st_setsrid(st_makepoint(x_long, y_lat), 4326)
  LIMIT 1;

  -- geometric operator <-> means "distance between"

END;
$$ LANGUAGE plpgsql;


Test call:
SELECT get_nearest_station ( 9.18233279, 45.47184631);




sqlexamples.info