<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Hello,<br>
Thanks for your answers. Tho there is something unclear to me in
both queries:<br>
<div dir="ltr" align="left"><font color="#000000"><span
class="474464620-26062011"><font face="Arial" size="2"> <br>
</font></span></font>
<div dir="ltr" align="left"><span class="985140919-26062011"><font
color="#0000ff" face="Arial" size="2">WITH </font></span></div>
<div dir="ltr" align="left"><span class="985140919-26062011"><font
color="#0000ff" face="Arial" size="2">-- find nearbys and
distance between</font></span></div>
<div dir="ltr" align="left"><span class="985140919-26062011"> <font
color="#0000ff" face="Arial" size="2">nn_matches AS</font></span></div>
<div dir="ltr" align="left"><span class="985140919-26062011"> <font
color="#0000ff" face="Arial" size="2">(<font color="#000000"
face="Times New Roman" size="3"> 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>
<big><b>FROM building AS b INNER JOIN poi AS p
INNER JOIN poi_category AS pc</b></big><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">
<u>FROM nn_matches INNER JOIN b ON nn_matches.id_b =
b.id_b;</u></span></div>
</font></span></div>
<font color="#000000"><span class="474464620-26062011"></span><span
class="985140919-26062011"><span class="474464620-26062011"><font
face="Arial" size="2"><br>
<br>
<br>
aren't the joins supposed to have ON conditions ( or a
USING clause at the very least) like the ones written in
blue just above? i have searched the documentation for
both postgres 8.3 and 8.4 and did not find that type of
"multiple inner join"; i only found examples for two
tables and every join example i saw ( except for the
natural join) had an ON or USING clause<br>
<br>
here is how i expected it to be:<br>
<br>
</font></span></span></font>
<div dir="ltr" align="left"><font color="#000000"><span
class="985140919-26062011"><font face="Arial" size="2">WITH
</font></span></font></div>
<div dir="ltr" align="left"><font color="#000000"><span
class="985140919-26062011"><font face="Arial" size="2">--
find nearbys and distance between</font></span></font></div>
<div dir="ltr" align="left"><font color="#000000"><span
class="985140919-26062011"> <font face="Arial" size="2">nn_matches
AS</font></span></font></div>
<div dir="ltr" align="left"><font color="#000000"><span
class="985140919-26062011"> <font face="Arial" size="2">(<font
face="Times New Roman" size="3"> 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>
<big><b>FROM building AS b, ( poi AS p INNER
JOIN poi_category AS pc ON p.c_id=pc.id) </b></big><br>
WHERE ST_DWithin(b.geom,p.geom,
1000) -- there's no more need for
AND p.c_id=pc.id since it has already been tested in
the inner join above</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<br>
<br>
the rest of the query was clear<br>
</span></div>
<div dir="ltr" align="left"><span
class="985140919-26062011"></span><span
class="985140919-26062011"> <br>
<br>
</span></div>
</font></span></font></div>
</div>
<br>
<blockquote cite="mid:06F69F4CADD0415D8639EC6861074498@J"
type="cite">
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<meta name="GENERATOR" content="MSHTML 8.00.7601.17622">
<div dir="ltr" align="left"><span class="474464620-26062011"><font
color="#0000ff" face="Arial" size="2">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"></span> </div>
<div dir="ltr" align="left"><span class="474464620-26062011"></span> </div>
<div dir="ltr" align="left"><span class="474464620-26062011"><font
color="#0000ff" face="Arial" size="2">SELECT id_b, id_p,
cname, dist</font></span></div>
<div dir="ltr" align="left"><span class="474464620-26062011"><font
color="#0000ff" face="Arial" size="2">FROM </font></span></div>
<div dir="ltr" align="left"><span class="474464620-26062011"><font
color="#0000ff" face="Arial" size="2">(<font color="#000000"
face="Times New Roman" size="3"> 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" face="Arial" size="2"><font color="#000000"
face="Times New Roman" size="3"> ,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" face="Arial" size="2"><span
class="985140919-26062011"> AND pc.<span
class="474464620-26062011">cname</span> IN('pharmacy',
'ed. centre', etc.....) <span class="474464620-26062011">
</span></span></font></span></div>
<div dir="ltr" align="left"><span class="985140919-26062011"><span
class="474464620-26062011"><font color="#0000ff"
face="Arial" size="2">) As nn_matches</font></span></span></div>
<div dir="ltr" align="left"><span class="985140919-26062011"><span
class="474464620-26062011"><font color="#0000ff"
face="Arial" size="2">WHERE pharmcnt > 1 AND edcnt >
1;</font></span></span></div>
<div dir="ltr" align="left"><span class="985140919-26062011"><span
class="474464620-26062011"></span></span><font
color="#0000ff" face="Arial" size="2"> </font></div>
<div dir="ltr" align="left"><span class="985140919-26062011"><span
class="474464620-26062011"><font color="#0000ff"
face="Arial" size="2">Leo and Regina</font></span></span></div>
<div dir="ltr" align="left"><span class="985140919-26062011"><span
class="474464620-26062011"><font color="#0000ff"
face="Arial" size="2"><a moz-do-not-send="true"
href="http://www.postgis.us">http://www.postgis.us</a></font></span></span></div>
<div dir="ltr" align="left"><span class="985140919-26062011"><span
class="474464620-26062011"></span></span><font
color="#0000ff" face="Arial" size="2"> </font></div>
<div dir="ltr" align="left"><span class="985140919-26062011"></span><font
color="#0000ff" face="Arial" size="2"> </font></div>
<br>
<div dir="ltr" class="OutlookMessageHeader" align="left"
lang="en-us">
<hr tabindex="-1">
<font face="Tahoma" size="2"><b>From:</b>
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>
[<a class="moz-txt-link-freetext" href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</a>] <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 dir="ltr" align="left"><span class="985140919-26062011"><font
color="#0000ff" face="Arial" size="2">Sam,</font></span></div>
<div dir="ltr" align="left"><span class="985140919-26062011"></span> </div>
<div dir="ltr" align="left"><span class="985140919-26062011"><font
color="#0000ff" face="Arial" size="2">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"></span> </div>
<div dir="ltr" align="left"><span class="985140919-26062011"><font
color="#0000ff" face="Arial" size="2">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"></span> </div>
<div dir="ltr" align="left"><span class="985140919-26062011"><font
color="#0000ff" face="Arial" size="2">WITH </font></span></div>
<div dir="ltr" align="left"><span class="985140919-26062011"><font
color="#0000ff" face="Arial" size="2">-- find nearbys and
distance between</font></span></div>
<div dir="ltr" align="left"><span class="985140919-26062011"> <font
color="#0000ff" face="Arial" size="2">nn_matches AS</font></span></div>
<div dir="ltr" align="left"><span class="985140919-26062011"> <font
color="#0000ff" face="Arial" size="2">(<font color="#000000"
face="Times New Roman" size="3"> 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"
face="Arial" size="2"><span class="985140919-26062011"></span></font></span> </div>
<div><span class="985140919-26062011"><font color="#0000ff"
face="Arial" size="2"><span class="985140919-26062011"></span></font></span> </div>
<div><span class="985140919-26062011"><font color="#0000ff"
face="Arial" size="2"><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"
face="Arial" size="2"><span class="985140919-26062011"></span></font></span> </div>
<div><span class="985140919-26062011"><font color="#0000ff"
face="Arial" size="2"><span class="985140919-26062011"><font
color="#000000">Leo and Regina</font></span></font></span></div>
<div><span class="985140919-26062011"><font color="#0000ff"
face="Arial" size="2"><span class="985140919-26062011"><font
color="#000000"><a moz-do-not-send="true"
href="http://www.postgis.us">http://www.postgis.us</a></font></span></font></span></div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"
size="2"><br>
</font></div>
<div dir="ltr" class="OutlookMessageHeader" align="left"
lang="en-us">
<hr tabindex="-1">
<font face="Tahoma" size="2"><b>From:</b>
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>
[<a class="moz-txt-link-freetext" href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</a>] <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>
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 moz-do-not-send="true" class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a moz-do-not-send="true" 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>
<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>