<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.7601.17622"></HEAD>
<BODY bgColor=#ffffff text=#000000>
<DIV dir=ltr align=left><SPAN class=474464620-26062011><FONT color=#0000ff
size=2 face=Arial>Here is the window solution. Should give the same
answer (we realized we misunderstood your id and thought it was a
text)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=474464620-26062011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=474464620-26062011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=474464620-26062011><FONT color=#0000ff
size=2 face=Arial>SELECT id_b, id_p, cname, dist</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=474464620-26062011><FONT color=#0000ff
size=2 face=Arial>FROM </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=474464620-26062011><FONT color=#0000ff
size=2 face=Arial>(<FONT color=#000000 size=3 face="Times New Roman"> 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,</FONT></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN
class=474464620-26062011> COUNT(CASE
WHEN pc.id = 3 THEN 1 ELSE NULL END) OVER(PARTITION BY b.id) As
edcnt</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=474464620-26062011><FONT color=#0000ff
size=2 face=Arial><FONT color=#000000 size=3
face="Times New Roman"> ,ST_Distance(b.geom, p.geom) As
dist<BR> FROM building AS b
INNER JOIN poi AS p INNER JOIN poi_category AS
pc<BR> WHERE
ST_DWithin(b.geom,p.geom, 1000) AND
p.c_id=pc.id </FONT></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=474464620-26062011><FONT color=#0000ff
size=2 face=Arial><SPAN
class=985140919-26062011> AND
pc.<SPAN class=474464620-26062011>cname</SPAN> IN('pharmacy', 'ed. centre',
etc.....) <SPAN class=474464620-26062011> </SPAN></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011><SPAN
class=474464620-26062011>) As nn_matches</SPAN></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011><SPAN
class=474464620-26062011>WHERE pharmcnt > 1 AND edcnt >
1;</SPAN></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011><SPAN
class=474464620-26062011></SPAN></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011><SPAN
class=474464620-26062011>Leo and Regina</SPAN></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011><SPAN
class=474464620-26062011><A
href="http://www.postgis.us">http://www.postgis.us</A></SPAN></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011><SPAN
class=474464620-26062011></SPAN></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN
class=985140919-26062011></SPAN> </DIV></FONT></SPAN><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Paragon Corporation<BR><B>Sent:</B> Sunday, June 26, 2011 4:12
PM<BR><B>To:</B> 'PostGIS Users Discussion'<BR><B>Subject:</B> Re:
[postgis-users] Fwd: A problematic query<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011><FONT color=#0000ff
size=2 face=Arial>Sam,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011><FONT color=#0000ff
size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011><FONT color=#0000ff
size=2 face=Arial>Try this (this assumes you are using 8.4 or above.
It's a bit easier and generally more efficient with a CTE)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011><FONT color=#0000ff
size=2 face=Arial>WITH </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011><FONT color=#0000ff
size=2 face=Arial>-- find nearbys and distance between</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN
class=985140919-26062011> <FONT color=#0000ff size=2
face=Arial>nn_matches AS</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011> <FONT
color=#0000ff size=2 face=Arial>(<FONT color=#000000 size=3
face="Times New Roman"> 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<BR> FROM building AS b
INNER JOIN poi AS p INNER JOIN poi_category AS
pc<BR> 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</FONT>
<DIV dir=ltr align=left><SPAN
class=985140919-26062011> AND
pc.id_pc IN('pharmacy', 'ed. centre', etc.....) </SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011>),</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011>-- Give ids of
buildings with more than 1 pharmacy and more than one ed center within
1000</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011></SPAN><SPAN
class=985140919-26062011> b AS </SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011>(</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011>SELECT
r.id_b</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011> FROM
nn_matches AS r</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011> GROUP
BY r.id_b</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011> HAVING COUNT(CASE
WHEN r.id_pc = 'pharmacy' THEN 1 ELSE NULL END) > 1</SPAN></DIV>
<DIV dir=ltr align=left><SPAN
class=985140919-26062011> AND
COUNT(CASE WHEN r.id_pc = 'ed. center' THEN 1 ELSE NULL END) > 1</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011>)</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011>-- List near by
pois for each building with more than 1 pharmacy and more than one ed
center</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011>SELECT nn_matches.*
</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=985140919-26062011> FROM
nn_matches INNER JOIN b ON nn_matches.id_b = b.id_b;</SPAN></DIV><SPAN
class=985140919-26062011></SPAN></FONT></SPAN></DIV>
<DIV><SPAN class=985140919-26062011><FONT color=#0000ff size=2 face=Arial><SPAN
class=985140919-26062011></SPAN></FONT></SPAN> </DIV>
<DIV><SPAN class=985140919-26062011><FONT color=#0000ff size=2 face=Arial><SPAN
class=985140919-26062011></SPAN></FONT></SPAN> </DIV>
<DIV><SPAN class=985140919-26062011><FONT color=#0000ff size=2 face=Arial><SPAN
class=985140919-26062011>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.</SPAN></FONT></SPAN></DIV>
<DIV><SPAN class=985140919-26062011><FONT color=#0000ff size=2 face=Arial><SPAN
class=985140919-26062011></SPAN></FONT></SPAN> </DIV>
<DIV><SPAN class=985140919-26062011><FONT color=#0000ff size=2 face=Arial><SPAN
class=985140919-26062011><FONT color=#000000>Leo and
Regina</FONT></SPAN></FONT></SPAN></DIV>
<DIV><SPAN class=985140919-26062011><FONT color=#0000ff size=2 face=Arial><SPAN
class=985140919-26062011><FONT color=#000000><A
href="http://www.postgis.us">http://www.postgis.us</A></FONT></DIV>
<DIV dir=ltr align=left><BR></DIV></SPAN></FONT></SPAN>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>SamuelStar<BR><B>Sent:</B> Saturday, June 25, 2011 8:35 AM<BR><B>To:</B>
PostGIS Users Discussion<BR><B>Subject:</B> Re: [postgis-users] Fwd: A
problematic query<BR></FONT><BR></DIV>
<DIV></DIV>As a side note, this post was originally intended for a postgres
mail-list <BR><BR>On 6/25/2011 1:25 PM, SamuelStar wrote:
<BLOCKQUOTE cite=mid:4E05B7B4.9070705@yahoo.com
type="cite"><BR><BR><BR>Hello,<BR>Let's cut to the chase: I need to select,<B>
for each residential building</B> in my table <B>that has say at least 2
pharmacies and 2 education centers</B> 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.
<BR><BR>table structure-><BR><BR>building (<BR>id serial, <BR>name varchar
)<BR><BR>poi_category(<BR>id serial, <BR>cname varchar) --cname being the
category name of course<BR><BR>poi(<BR>id serial, <BR>name
varchar,<BR> c_id integer)-- c_id is the FK referencing
poi_category(id)<BR><BR>so the structure would be : building, poi >-----
poi_category<BR><BR><B>all coordinate columns are of type geometry</B> not
geography (<U>let's call them geom</U>)<BR><BR>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<BR><BR> SELECT r.id_b,
r.id_p<BR> FROM
(<BR> SELECT b.id AS id_b,
p.id AS id_p, pc.id AS
id_pc,pc.cname<BR> FROM
building AS b, poi AS p, poi_category AS
pc<BR> 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<BR> ) AS
r,<BR>
(<BR> SELECT * FROM r GROUP BY
id_b<BR> ) AS
r1<BR><BR> HAVING count
(<BR>
SELECT
*<BR>
FROM r,
r1<BR>
WHERE r1.id_b=r.id_b AND
r.id_pc='pharmacy'<BR><BR>
)>1<BR>
AND<BR>
count
(<BR>
SELECT
*<BR>
FROM r,
r1<BR>
WHERE r1.id_b=r.id_b AND r.id_pc='ed.
centre'<BR><BR>
)>1<BR><BR>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?<BR><BR>Problem posted here also: <A
class=moz-txt-link-freetext
href="http://gis.stackexchange.com/questions/11445/postgis-advanced-selection-query"
moz-do-not-send="true">http://gis.stackexchange.com/questions/11445/postgis-advanced-selection-query</A><BR> but
i received an answer that seems wrong or not optimal at the very
least<BR><BR>And an example table that shows distances between
:<BR> -- Ed. centers have poi.c_id 3 and
and pharmacies have poi.c_id 1<BR>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<BR>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<BR>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<BR>distances <B>10</B> <U>26</U> <B>14</B> 15
<U>60</U> 28 <B>65</B> 49 <U>46</U> <B>23</B>
<U>54</U> <B>27</B> 16 <U>15</U> 48 <B>26</B> 47
<U>22</U> <B>19</B> <U>11</U> <B>55 </B> 34
<U>53</U> 15 <B>31</B> 58 <U>39</U> <B>19</B>
<U>36</U> <B>92</B> 47 <U>16</U> 30 <B>25</B>
59 <U>25</U> <B>35</B> <U>23</U> <B>21</B> 40 <U>51</U>
13 <B>43</B> 45
<U>42</U><BR>between<BR>building<BR>and poi <BR>(i have also decorated the
distances depending on wether they are for a pharmacy (bold) or an ed. center
(underlined) ) <BR>
<BR>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)<BR>So building 1 has 2
pharmacies within 30 DU but only one ed. center within 25 DU<BR>building 2
has 3 pharmacies within 30 DU and two ed. centers
within 25 DU<BR>building 3 has only 1 pharmacy within 30 DU so the rest
doesn't matter<BR>building 4 has 2 pharmacies within 30 DU and
two ed. centers within 25 DU ( one of them is exactly 25 DU
away)<BR>building 5 has 1 pharmacies within 30 DU , we don't care about
the rest <BR><BR>The resulting table would then be:<BR>building.id
2 2 2
2 2 2 2
2 2 4 4
4 4 4
4 4 4 4
<BR>poi.id 1
2 3 4 5 6
7 8 9
1 2 3 4
5 6 7 8
9 <BR>poi.c_id
1 3 1
2 3 4 1
2 3 1 3
1 2 3
4 1 2 3
<BR>distances <B>23</B> <U>54</U> <B>27</B> 16
<U>15</U> 48 <B>26</B> 47
<U>22</U> <B>19</B> <U>36</U> <B>92</B> 47
<U>16</U> 30 <B>25</B> 59 <U>25</U> -- without the
distances column which i'm just printing to make it easier to understand the
results<BR>between<BR><BR><BR><BR> Do help a fellow postgres user
;;)<BR><BR><PRE wrap=""><FIELDSET class=mimeAttachmentHeader></FIELDSET>
_______________________________________________
postgis-users mailing list
<A class=moz-txt-link-abbreviated href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>
<A class=moz-txt-link-freetext href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A>
</PRE></BLOCKQUOTE><BR></BODY></HTML>