> SELECT b.way AS building_geometry
> FROM
> (SELECT way FROM osm_polygon
> WHERE tags @> hstore('building','yes')
> ) AS b,
> (SELECT way, tags->'amenity' as value FROM osm_poi
> WHERE (tags ? 'amenity')
> ) AS p
> WHERE
> (SELECT count(*) > 1 FROM p
> WHERE p.value = 'pharmacy'
> AND ST_DWithin(b.way,p.way,1000)
> )
> AND
> (SELECT count(*) > 1 FROM p
> WHERE p.value = 'school'
> AND ST_DWithin(b.way,p.way,1000)
> )
>
> ERROR: relation "p" does not exist
> LINE 10: (SELECT count(*) > 1 FROM p
You're referencing "p" as a table, not as a table-alias, because you
select FROM p.
Your join appears to be unconstrained as well: you get every record in
p for each record in b. That's probably not what you want.
And you're using WHERE count(*) > 1 where you actually mean WHERE
EXISTS; you're not interested in the actual count, which is quite a
bit less efficient to determine than just existence.
You probably meant to write something like this:
SELECT b.way AS building_geometry
FROM osm_polygon AS b
WHERE tags @> hstore('building','yes')
AND EXISTS (
SELECT 1 FROM osm_poi AS p
WHERE p.value = 'pharmacy'
AND ST_DWithin(b.way,p.way,1000)
)
AND EXISTS (
SELECT 1 FROM osm_poi AS p
WHERE p.value = 'school'
AND ST_DWithin(b.way,p.way,1000)
)
If you're on Postgres 9 then you can put the common osm_poi part in a CTE.
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
