[postgis-users] Fwd: A problematic query

[SamuelStar]

Hello,
Let's cut to the chase: 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 X km, all POIs (pharmacies, comercial centres, 
medical centers, education centers, police stations, fire stations) 
which are within X km 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)

so the structure would be : building, poi >----- poi_category

*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  -- 
ST_DWithin (a,b, x) makes sure the distance between a & b is less or 
equal than x
          ) 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?

Problem posted here also: 
http://gis.stackexchange.com/questions/11445/postgis-advanced-selection-query
  but i received an answer that seems wrong or not optimal at the very least

And an example table that shows distances between :
       -- Ed. centers have poi.c_id  3 and and pharmacies have poi.c_id 1
building.id   1    1    1    1    1    1    1    1    1    2    2    
2    2    2    2    2    2    2    3    3    3    3    3    3    3    
3    3    4    4    4    4    4    4    4    4    4    5    5    5    
5    5    5    5    5    5
poi.id           1    2    3    4    5    6    7    8    9    1    2    
3    4    5    6    7    8    9    1    2    3    4    5    6    7    
8    9    1    2    3    4    5    6    7    8    9    1    2    3    4 
    5    6    7    8    9
poi.c_id       1    3    1    2    3    4    1    2    3    1    3    
1    2    3    4    1    2    3    1    3    1    2    3    4    1    
2    3    1    3    1    2    3    4    1    2    3    1    3    1    
2    3    4    1    2    3
distances *10* _26_ *14*  15 _60_  28 *65*  49 _46_ *23* _54_ *27*   16 
_15_  48 *26*  47 _22_ *19* _11_ *55 * 34 _53_  15 *31*  58 _39_ *19* 
_36_ *92*  47 _16_ 30 *25*  59 _25_ *35* _23_ *21* 40 _51_ 13 *43*  45 _42_
between
building
and poi
(i have also decorated the distances depending on wether they are for a 
pharmacy (bold) or an ed. center (underlined) )

and the resulting table and the logic : I am looking for the buildings 
that have at least 2 pharmacies and 2 ed.centers within 30 respectively 
25 distance units (DU)
So  building 1 has  2 pharmacies within 30 DU but only one ed. center 
within 25 DU
building 2 has  3 pharmacies within 30 DU and  two  ed. centers  within 
25 DU
building 3 has  only 1 pharmacy within 30 DU so the rest doesn't matter
building 4 has  2 pharmacies within 30 DU and  two  ed. centers  within 
25 DU ( one of them is exactly 25 DU away)
building 5 has  1 pharmacies within 30 DU , we don't care about the rest

The resulting table would then be:
building.id   2    2    2    2    2    2    2    2    2    4    4    
4    4    4    4    4    4    4
poi.id           1    2    3    4    5    6    7    8    9    1    2    
3    4    5    6    7    8    9
poi.c_id       1    3    1    2    3    4    1    2    3    1    3    
1    2    3    4    1    2    3
distances ** *23* _54_ *27* 16 _15_  48 *26*  47 _22_ *19* _36_ *92*  47 
_16_ 30 *25*  59 _25_ **-- without the distances column which i'm just 
printing to make it easier to understand the results
between



   Do help a fellow postgres user ;;)

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110625/e2dda58d/attachment.html>