[postgis-users] Fwd: A problematic query

[SamuelStar]

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: 
> http://gis.stackexchange.com/questions/11445/postgis-advanced-selection-query
>  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/20110625/6becd420/attachment.html>