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
