From: | Stefan Keller <sfkeller(at)gmail(dot)com> |
---|---|
To: | pgsql-general List <pgsql-general(at)postgresql(dot)org> |
Subject: | Expression alias not recognized in WHERE clause (ERROR: relation "p" does not exist) |
Date: | 2012-08-09 11:32:43 |
Message-ID: | CAFcOn2948+FwwpmTV73FG_58ABSeJm6FoAF5UGkgq2y8_eO_Ew@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2012-08-09 12:23:57 | Re: Expression alias not recognized in WHERE clause (ERROR: relation "p" does not exist) |
Previous Message | Sergey Konoplev | 2012-08-09 07:30:04 | Re: Problem running "ALTER TABLE...", ALTER TABLE waiting |