Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux