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

On 9 Srpen 2012, 13:32, Stefan Keller wrote:
> Hi
>
> I have two (hopefully) equivalent - and unfortunately very slow - queries
> which
> "Select all buildings that have >1 pharmacies and >1 schools within
> 1000m".
>
> In the first query there is an expression alias "b" and in the second
> there are two expression aliases: "b" and "p".
>
> Can someone tell me, why expression alias "p" is *not* recognized in
> the WHERE clause - whereas alias "b" is (parantheses missing)?
>
> And, has anyone an idea on how to reformulate this second query?
>
> Stefan
>
>
> SELECT way AS building_geometry
> FROM
> (SELECT osm_id, way
> FROM osm_polygon
> WHERE tags @> hstore('building','yes')
> ) AS b
> WHERE
> (SELECT count(*) > 1 FROM osm_poi AS p
> WHERE p.tags @> hstore('amenity','pharmacy')
> AND ST_DWithin(b.way,p.way,1000)
> )
> AND
> (SELECT count(*) > 1 FROM osm_poi AS p
> WHERE p.tags @> hstore('amenity','school')
> AND ST_DWithin(b.way,p.way,1000)
> )
>
>
> 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
> ^
>
> ********** Error **********
>
> ERROR: relation "p" does not exist
> SQL state: 42P01
> Character: 245

Well, that's pretty clear why it does not recognize the "p" alias in the
subquery - you're trying to do a SELECT from it, and it can't use the
alias like this. You can reference it in a condition (as you do with the
"b") but you can't do a SELECT.

You could do is use CTEs to do that, e.g. like this:

WITH
b AS (SELECT way FROM osm_polygon WHERE tags @> hstore('building','yes')),
p AS (SELECT way, tags->'amenity' as value FROM osm_poi WHERE (tags ?
'amenity')
SELECT b.way AS building_geometry
FROM b
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)
)

That should work.

Still, I believe you could use INTERSECT to handle this - I haven't
noticed the '>' in the original post and I've treated it like '>=', but I
believe the query may be easily tweaked to handle this. See 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)
GROUP BY b.osm_id HAVING COUNT(*) > 1
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)
GROUP BY b.osm_id HAVING COUNT(*) > 1

regards
Tomas