## A problematic query

```
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:
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 ;;)

```

### pgsql-novice by date

 Next: From: Frank Lanitz Date: 2011-06-27 12:59:30 Subject: What is || doing Previous: From: Roy's Email Date: 2011-06-25 07:05:11 Subject: Linking libpq.lib with VC++