The problem: I need to select, for each residential building in my table that has say at least 2 pharmacies and 2 education centers within a radius of 1km, all POIs (pharmacies, comercial centres, medical centers, education centers, police stations, fire stations) which are within 1km of the respective building. table structure->
building (id serial, name varchar )
poi_category(id serial, cname varchar) --cname being the category name of course
poi(id serial, name varchar, c_id integer)-- c_id is the FK referencing poi_category(id)
all coordinate columns are of type geometry not geography (let's call them geom)
here's the way i thought it should be done but i'm not sure it's even correct let alone the optimal solution to this problem
SELECT r.id_b, r.id_p FROM ( SELECT b.id AS id_b, p.id AS id_p, pc.id AS id_pc,pc.cname FROM building AS b, poi AS p, poi_category AS pc WHERE ST_DWithin(b.geom,p.geom, 1000) AND p.c_id=pc.id ) AS r, ( SELECT * FROM r GROUP BY id_b ) AS r1 HAVING count ( SELECT * FROM r, r1 WHERE r1.id_b=r.id_b AND r.id_pc='pharmacy' )>1 AND count ( SELECT * FROM r, r1 WHERE r1.id_b=r.id_b AND r.id_pc='ed. centre' )>1
Is this the way to go for what i need ? What solution would be better from a performance point of view? What about the most elegant solution?