Finding closest outside point (health center) to point (education center) inside polygon (danger zone) using PostGIS?

Question

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?

Answer 1

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.