[postgis-users] Fwd: A problematic query
Paragon Corporation
lr at pcorp.us
Sun Jun 26 13:59:03 PDT 2011
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-quer
y
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/20110626/385b232d/attachment.html>
More information about the postgis-users
mailing list