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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
