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:* [email protected]
[mailto:[email protected]] *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:* [email protected]
[mailto:[email protected]] *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
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
