postgis - Nearest neighbour for every row

I'm relatively new to PostGIS and postgresql . I'm trying to build a simple query containing two relations as follows :

I've two relations R1 and R2 both of which contains geometries(Points) . I need to find out the closest point of every point in R1 which is in R2 . Say R1 is a relation of all hospitals and R2 is a relation of all medical shops , i need to find a medical shop closest to each hospital . More over i've a geometry types of srid 4326 in case you want to use ST_Distance or similar functions . Assume i need the medical shops within say 500 meters,say .

Answer

You'll need a correlated query for each row (or a Lateral join in 9.3+...maybe). Do note also that although indices on the geometry columns will be used it still will mean one query per hospital so it will be slow.

SELECT p1.gid,p1.name, p2.gid,p2.name, p2.geom <-> p1.geom as dist FROM
  ( SELECT p1.gid as g1,
     (SELECT p.gid
      FROM shops AS p

      WHERE p1.gid<>p.gid
      ORDER BY p.geom2<->p1.geom2 ASC LIMIT 1) AS g2
      FROM hospitals AS p1
      OFFSET 0
) AS q
JOIN hospitals AS p1
  ON q.g1=p1.gid
JOIN shops AS p2
  ON q.g2=p2.gid
WHERE dist<=500;

I used as parts of the code I used here so you may want to check it in case I messed up.