Re: Expression alias not recognized in WHERE clause (ERROR: relation "p" does not exist)

> 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.