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?