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 .

  • discard the hospital altogether if hospitals don't have a medical shop within 500 mtrs
    – Malice
    Sep 16 '13 at 9:49

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.