On Aug 9, 2012, at 7:32, Stefan Keller <sfkeller@gmail.com> 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)?
The subqueries can make use of values attached to the outer (aliased) relation but you cannot use the alias itself as a
FROMsource. The error is stemming from your use of "FROM p" inside the WHERE.
>
> And, has anyone an idea on how to reformulate this second query?
Use a common table expression (CTE) (sql command: WITH). Those can be attached to any FROM clause in the 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
