postgresql - Finding the closest Geometry in PostGIS

I've been looking over the PostGIS "API" of functions, and I notice that most of them take two elements to compare. For example, the ST_Distance function takes two geometry/geography elements to find the distance.

There is no function to do something like: "Given a geometry G, give me the closest geometry GClosest in Table T where G.id <> GClosest.id"

I realize I could write a PL/PgSQL function to iterate over the table and call ST_Distance on each element, but I'm hoping there's a better, more efficient, solution.

Answer

Your question can be also answered by a single (albeit complex) query like the following which returns the whole record and the distance to the reference geometry. Please note that if more than one record matches the min distance they are all returned.

SELECT 
  i.*,
  md.min_distance
FROM
  address AS i, 

  (SELECT 
     ga.address_geom,
     min( ST_Distance(
            ga.address_geom,
            gb.address_geom)
        ) AS min_distance
   FROM
     address AS ga,
     address AS gb 
   WHERE 

     ga.id <> gb.id 
   AND 
     ga.id = 3
   GROUP BY 
     ga.address_geom
  ) AS md 
WHERE 
  ST_Distance( i.address_geom, md.address_geom) = md.min_distance;

I have tested this query on table of addresses and it works. In the query above I am looking for nearest point to that with id=3.