# [postgis-users] Fwd: A problematic query

Paragon Corporation lr at pcorp.us
Sun Jun 26 13:59:03 PDT 2011

```Here is the window solution.  Should give the same answer  (we realized we
misunderstood your id and thought it was a text)

SELECT id_b, id_p, cname, dist
FROM
( SELECT b.id AS id_b, p.id AS id_p, pc.id AS id_pc,pc.cname, COUNT(CASE
WHEN pc.id = 1 THEN 1 ELSE NULL END) OVER(PARTITION BY b.id) As pharmcnt,
COUNT(CASE WHEN pc.id = 3 THEN 1 ELSE NULL END) OVER(PARTITION BY
b.id) As edcnt
,ST_Distance(b.geom, p.geom) As dist
FROM building AS b INNER JOIN  poi AS p INNER JOIN  poi_category AS
pc
WHERE ST_DWithin(b.geom,p.geom, 1000) AND p.c_id=pc.id
AND pc.cname IN('pharmacy', 'ed. centre', etc.....)
) As nn_matches
WHERE pharmcnt > 1 AND edcnt > 1;

Leo and Regina
http://www.postgis.us

_____

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paragon
Corporation
Sent: Sunday, June 26, 2011 4:12 PM
To: 'PostGIS Users Discussion'
Subject: Re: [postgis-users] Fwd: A problematic query

Sam,

You are using HAVING incorrectly.  HAVING is like a WHERE for aggregate
calculations.  If you are just going to throw a subselect in there, you are
defeating the purpose of HAVING.

Try this  (this assumes you are using 8.4 or above.  It's a bit easier and
generally more efficient with a CTE)

WITH
-- find nearbys and distance between
nn_matches AS
(   SELECT b.id AS id_b, p.id AS id_p, pc.id AS id_pc,pc.cname,
ST_Distance(b.geom, p.geom) As dist
FROM building AS b INNER JOIN  poi AS p INNER JOIN  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
AND pc.id_pc IN('pharmacy', 'ed. centre', etc.....)
),
-- Give ids of buildings with more than 1 pharmacy and more than one ed
center within 1000
b AS
(
SELECT r.id_b
FROM nn_matches AS r
GROUP BY r.id_b
HAVING COUNT(CASE WHEN r.id_pc = 'pharmacy' THEN 1 ELSE NULL END) > 1
AND COUNT(CASE WHEN r.id_pc = 'ed. center' THEN 1 ELSE NULL END) > 1
)
-- List near by  pois for each building with more than 1 pharmacy and more
than one ed center
SELECT nn_matches.*
FROM nn_matches INNER JOIN b ON nn_matches.id_b = b.id_b;

There is another way to do it with a WINDOW query that would be shorter
(possibly doable in a single query), we haven't thought that far thru.  Not
sure if it would be faster or not.

Leo and Regina
http://www.postgis.us

_____

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
SamuelStar
Sent: Saturday, June 25, 2011 8:35 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Fwd: A problematic query

As a side note, this post was originally intended for a postgres mail-list

On 6/25/2011 1:25 PM, SamuelStar wrote:

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

_______________________________________________

postgis-users mailing list

postgis-users at postgis.refractions.net

http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110626/385b232d/attachment.html>
```