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

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

> 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

You're referencing "p" as a table, not as a table-alias, because you
select FROM p.
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.
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.

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.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

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

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

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

On 13 Aug 2012, at 1:15, Stefan Keller wrote:

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

I don't think SQL allowed re-referencing an earlier mentioned table like that until CTE's were introduced to take care
ofit instead. Not sure what's behind that. 

Normally people only use table aliases to have easier access to columns in a specific table in their query.
Additionally,aliases are sometimes required to disambiguate which instance of the same table in a join is meant.
Perhapsthere are other users still, but using an alias as a table to select from is not among them I'm afraid. 

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

Ah yes, you're correct about that.

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


A start would be to post an explain analyse (or a link to the results from http://explain.depesz.com/).

It's possible that you can about half that time by using something like this instead of the separate pharmacy and
schoolexpressions (I haven't used hstore before, so I may have made an error or two): 

nr_by_type AS (
SELECT way,
    SUM(CASE WHEN tags.value = 'pharmacy' THEN 1 ELSE 0 END) AS nr_pharmacies,
    SUM(CASE WHEN tags.value = 'school' THEN 1 ELSE 0 END) AS nr_schools
FROM osm_poi
WHERE tags.key = 'amenity'
GROUP BY way
)

But that's just a guess on my part of where the bottleneck is.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.