Selecting POIs around specific buildings using PostGIS

Question

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?

Answer 1

Based on the second example given on that page, I would try something like :

SELECT b.gid, b.name, [+ any fields] -- here you retrieve POI data
    FROM building b
        LEFT JOIN building f ON ST_DWithin(b.the_geom, f.the_geom, 1000) -- farmacy
        LEFT JOIN building ec ON ST_DWithin(b.the_geom, ec.the_geom, 1000) -- ed. center
        -- LEFT JOIN POI AND POI_CATEGORY tables + join conditions
    WHERE f.id_pc='pharmacy' AND ec.id_pc='ed. centre'
    AND COUNT(f.gid) >1 AND COUNT(ec.gid)>1;

Of course, it is not tested and the query is not complete but the logic would be to join tables regarding geometrical criteria. If that work, then add instructions to retrieve your POI data.

Answer 2

I usually do these in plpgsql as I find it much easier to read and the results are usually more efficient because you can control the flow (exit early, etc):

CREATE OR REPLACE FUNCTION getpois() RETURNS text as $$
DECLARE
br record;
phcount integer;
edcount integer;
FOR br IN SELECT id, name, geom FROM building LOOP
  SELECT count(id) from poi where ST_DWITHIN(br.geom, geom, 1000) and c_id = pharmacycateg into phcount;
  IF phcount < 2 THEN
     EXIT; --i think this is the equiv of continue, i.e. skip to next building
  ENDIF;
  SELECT count(id) from poi where ST_DWITHIN(br.geom, geom, 1000) and c_id = pharmacycateg into edcount;
  IF edcount < 2 THEN
     EXIT;
  ENDIF;
  -- now select all POIS within 1KM and maybe return them as array, etc
END LOOP;
END
$$ LANGUAGE 'plpgsql' strict;