5

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?

4
  • 1
    In my opinion it would be better to rephrase the question for something like: "Advanced selection query in postgis"
    – com
    Commented Jun 24, 2011 at 11:05
  • 1
    @com i doubt this is really advanced selection.. i probably make it look advanced because of the way i'm complicating things but the problem seems rather simple to me and, i assume, the solution to the problem is much more simple than the one i have written (which i'm not sure is correct BTW) . Commented Jun 24, 2011 at 11:50
  • 1
    You should take a look at windowing function in postgresql doc if you are using PostgreSQL 8.4+. Commented Jun 24, 2011 at 13:54
  • @ nicklas-aven i have postgres 8.3 currently installed Commented Jun 24, 2011 at 14:06

2 Answers 2

2

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.

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.