From: | "Tomas Vondra" <tv(at)fuzzy(dot)cz> |
---|---|
To: | "Stefan Keller" <sfkeller(at)gmail(dot)com> |
Cc: | "Tomas Vondra" <tv(at)fuzzy(dot)cz>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Slow query: Select all buildings that have >1 pharmacies and >1 schools within 1000m |
Date: | 2012-08-07 13:56:46 |
Message-ID: | d4e1e0255d5658c1fec9033b59f238b0.squirrel@sq.gransy.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 7 Srpen 2012, 14:22, Stefan Keller wrote:
> Your proposal lacks the requirement that it's the same building from
> where pharmacies and schools are reachable.
> But I think about.
I don't know the dataset so I've expected the osm_id to identify the
building - then the intersect should work as AND for the conditions.
And I see I've forgot to include the 'is building' condition, so it should
be like this:
SELECT b.osm_id FROM osm_poi AS p, osm_polygon b
WHERE p.tags @> hstore('amenity','pharmacy')
AND b.tags @> hstore('building','yes')
AND ST_DWithin(b.way,p.way,1000)
INTERSECT
SELECT b.osm_id FROM osm_poi AS p, osm_polygon b
WHERE p.tags @> hstore('amenity','school')
AND b.tags @> hstore('building','yes')
AND ST_DWithin(b.way,p.way,1000)
Tomas
From | Date | Subject | |
---|---|---|---|
Next Message | Craig James | 2012-08-07 14:37:42 | Re: Slow query: Select all buildings that have >1 pharmacies and >1 schools within 1000m |
Previous Message | Stefan Keller | 2012-08-07 12:22:53 | Re: Slow query: Select all buildings that have >1 pharmacies and >1 schools within 1000m |