I am trying to find the closest health centers (points) to the education centers (points) located within the "danger zone" (a specific polygon) while excluding those health centers located in the "danger zone".

I'm pretty sure I need to join the three tables to do so...

so to get the education centers within the danger zone I have a virtual table:

   with edu_t as(
   select distinct on (e.gid) e.gid as edu_gid, e.name as edu_center, e.geom as edu_geom 
   from education_centers e, danger_zones d
   where (st_contains(d.geom, e.geom)) and d.name = 'Danger Zone 1')

and to get the closest health center from these points I have:

   select distinct on (et.edu_gid) et.edu_gid, et.edu_center, h.gid, h.name, 
   st_distance(et.edu_geom, h.geom) as dist
   from edu_t et, health_centers h
   where st_dwithin(et.edu_geom, h.geom, 50000)
   order by et.edu_gid, st_distance(et.edu_geom, h.geom) asc;

but that mainly gives me health centers in Danger Zone 1.

How can I exclude them?

  • You probably have to work with st_contains. I don't have my proper laptop with me right now, but I'll check it this evening (if no one else has replied by then) – Wout Oct 30 '15 at 15:45
  • That's what I've been trying, but if I do that it only returns the health centers in other danger zones... I guess I'm missing a command somewhere. – Julia Oct 30 '15 at 16:26

To exclude the points, you can use st_disjoint. This sql gives the results you want, but I haven't spent any time trying to optimize. You'll have to rewrite this to use your with and st_dwithin clauses.

 select distinct on (e.objectid) e.objectid e_oid, 
      h.objectid h_oid, st_distance(e.shape, h.shape) as dist
 from gdb.edu_center e, gdb.health_center h, gdb.danger_zone d
 where st_contains(d.shape, e.shape) and st_disjoint(d.shape, h.shape)
 order by e.objectid, st_distance(e.shape, h.shape) asc;

If you don't want to use st_disjoint you could do it with a except (this doesn't work with geometries) or a not in clause. Something like:

 with edu_t as(
      select e.objectid, e.shape
      from gdb.edu_center e, gdb.danger_zone d
      where st_contains(d.shape, e.shape))
 select distinct on (et.objectid) et.objectid e_oid, h.objectid h_oid, st_distance(et.shape, h.shape) as dist
 from edu_t et, gdb.health_center h
 where h.objectid not in (
      select e.objectid
      from gdb.health_center e, gdb.danger_zone d
      where st_contains(d.shape, e.shape)
 order by et.objectid, st_distance(et.shape, h.shape) asc; 

With my very small dataset, the second query performs better.

  • Thank you very much! The second one works perfectly =) For some reason, the first one still tries to include a health center that's inside the danger zone – Julia Oct 30 '15 at 18:20

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.