Обсуждение: A problematic query
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 ;;)