Re: Slow query: Select all buildings that have >1 pharmacies and >1 schools within 1000m - Mailing list pgsql-performance

On 7 Srpen 2012, 14:01, Stefan Keller wrote:
> Hi
>
> I have an interesting query to be optimized related to this one [1].
>
> The query definition is: Select all buildings that have more than 1
> pharmacies and more than 1 schools within a radius of 1000m.
>
> The problem is that I think that this query is inherently O(n^2). In
> fact the solution I propose below takes forever...

What about plain INTERSECT? Something like

SELECT osm_id FROM osm_poi AS p, osm_polygon b
   WHERE p.tags @> hstore('amenity','pharmacy')
   AND ST_DWithin(b.way,p.way,1000)
INTERSECT
SELECT osm_id FROM osm_poi AS p, osm_polygon b
   WHERE p.tags @> hstore('amenity','school')
   AND ST_DWithin(b.way,p.way,1000)

Or something like that. But maybe it's a complete nonsense ...

Tomas