<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
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">
<meta http-equiv="content-type" content="text/html;
charset=ISO-8859-1">
<br>
<br>
<br>
<meta http-equiv="content-type" content="text/html;
charset=ISO-8859-1">
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 moz-do-not-send="true"
class="moz-txt-link-freetext"
href="http://gis.stackexchange.com/questions/11445/postgis-advanced-selection-query">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>