[postgis-users] Fwd: A problematic query
SamuelStar
simple_hot_ice at yahoo.com
Mon Jun 27 04:20:42 PDT 2011
Hello,
Thanks for your answers. Tho there is something unclear to me in both
queries:
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;_
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
here is how i expected it to be:
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, ( poi AS p INNER JOIN poi_category AS pc ON
p.c_id=pc.id) *
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
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
the rest of the query was clear
> 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:
>> 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
>
>
> _______________________________________________
> 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/20110627/9f45eb36/attachment.html>
More information about the postgis-users
mailing list