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

Salut Alban

Thanks for your patient hints.
As your signature suggests, I probably could not see the forest for the trees.
But now I think I do (see below) - except for the following:

2012/8/9 Alban Hertroys <haramrae@gmail.com> wrote:
> You're referencing "p" as a table, not as a table-alias, because you
> select FROM p.

That's true but sorry that I can see any difference between
referencing a table or a table alias.

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

Well, in fact, that was what I wanted and what I finally got with CTE
as you suggested below.

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

That's a good and usual performance hint to test for existence instead
of counting.
But there's one of the challenges of this query:
"Select all buildings that have >1 pharmacies and >1 schools within 1000m".
So it's really forcing a count because it's asking for more than one (not >=1).

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

Below you find what I finally got with CTE.
Look at the two "select count(*)>1 which need to be made two times and
contain a count.

WITH
building AS (
  SELECT way FROM osm_polygon
  WHERE tags @> hstore('building','yes')
  --LIMIT 1000
),
pharmacy AS (
  SELECT way FROM osm_poi
  WHERE tags @> hstore('amenity','pharmacy')
),
school AS (
  SELECT way FROM osm_poi
  WHERE tags @> hstore('amenity','school')
)
SELECT ST_AsText(building.way) AS building_geometry
FROM building
WHERE
  (SELECT count(*) > 1 FROM pharmacy
   WHERE ST_DWithin(building.way,pharmacy.way,1000))
  AND
  (SELECT count(*) > 1 FROM school
   WHERE ST_DWithin(building.way,school.way,1000))

I also tried alternatives like:
* "WHERE tags ? 'amenity" which is a kind of tag existence function
* doing a GROUP BY
* or applying a "JOIN .... ON ST_DWithin(building.way,school.way,1000)"
... but no one was as "fast" as this one.

Unfortunately it's still VERY SLOW and takes more than 6 minutes on a
current 72GB memory(!) server!
No idea on how to speed up this tough nut to crack any more...

Yours, Stefan