From: Tomas Vondra <tv@fuzzy.cz>
To: Stefan Keller <sfkeller@gmail.com>
Cc: Tomas Vondra <tv@fuzzy.cz>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query: Select all buildings that have >1 pharmacies and >1 schools within 1000m
Date: Tue, 7 Aug 2012 15:56:46 +0200
Message-ID: <d4e1e0255d5658c1fec9033b59f238b0.squirrel@sq.gransy.com> (raw)
In-Reply-To: <CAFcOn28SbLbQ+3HtNXhSUiwiQ93un_PPP8sSA=Ut1kH_TyFtSA@mail.gmail.com>
References: <CAFcOn29ZAGzFjd+VnmAfF4iQigd8mJnACHjLMkow46V4+uUrLQ@mail.gmail.com>
<1e50929e6ba44cabb8deb2ad0ee98c82.squirrel@sq.gransy.com>
<CAFcOn28SbLbQ+3HtNXhSUiwiQ93un_PPP8sSA=Ut1kH_TyFtSA@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz, sfkeller@gmail.com
Subject: Re: Slow query: Select all buildings that have >1 pharmacies and >1 schools within 1000m
In-Reply-To: <d4e1e0255d5658c1fec9033b59f238b0.squirrel@sq.gransy.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox