Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 7EDF08820F3 for ; Tue, 7 Aug 2012 10:57:04 -0300 (ADT) Received: from elizabeth.gransy.com ([89.187.132.199]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SykHO-0006R7-HK for pgsql-performance@postgresql.org; Tue, 07 Aug 2012 13:57:03 +0000 Received: from sq.gransy.com (localhost [127.0.0.1]) by elizabeth.gransy.com (Postfix) with ESMTP id 3254015E42F9; Tue, 7 Aug 2012 15:56:46 +0200 (CEST) Received: from 212.47.19.46 (SquirrelMail authenticated user tv@fuzzy.cz) by sq.gransy.com with HTTP; Tue, 7 Aug 2012 15:56:46 +0200 Message-ID: In-Reply-To: References: <1e50929e6ba44cabb8deb2ad0ee98c82.squirrel@sq.gransy.com> Date: Tue, 7 Aug 2012 15:56:46 +0200 Subject: Re: Slow query: Select all buildings that have >1 pharmacies and >1 schools within 1000m From: "Tomas Vondra" To: "Stefan Keller" Cc: "Tomas Vondra" , pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.22 MIME-Version: 1.0 Content-Type: text/plain;charset=utf-8 X-Priority: 3 (Normal) Importance: Normal Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201208/36 X-Sequence-Number: 47574 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