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
