[postgis-users] Declarative SQL query for non-overlapping buffers given dense points?

[Stefan Keller]

Salut Pierre

Nice hint :->
I also wanted to have at least on labels even in dense areas (picking
one randomly or taking elevation as priority criterion).
I tried some variants like this - and it's really slow although there
are only about 1500 features involved!
I canceled the query after some minutes...

SELECT name, ele, ST_AsText(way)
FROM
  planet_osm_point A,
  (SELECT ST_Union(ST_Buffer(way, 1)) AS geom
   FROM planet_osm_point
   WHERE tourism='viewpoint') B
WHERE
  NOT ST_Within(ST_Buffer(A.way,1), B.geom)
  AND tourism='viewpoint'
ORDER BY 1

I think its the ST_Union and ST_Within which are to blame:

"Sort  (cost=2607.06..2607.74 rows=270 width=119)"
"  Sort Key: a.name"
"  ->  Nested Loop  (cost=1210.23..2596.16 rows=270 width=119)"
"        Join Filter: (NOT st_within(st_buffer(a.way, 1::double
precision), (st_union(st_buffer(planet_osm_point.way, 1::double
precision)))))"
"              ->  Bitmap Heap Scan on planet_osm_point
(cost=15.39..1193.83 rows=405 width=100)"
"                    Recheck Cond: (tourism = 'viewpoint'::text)"
"                    ->  Bitmap Index Scan on planet_osm_point_tourism
 (cost=0.00..15.29 rows=405 width=0)"
"                          Index Cond: (tourism = 'viewpoint'::text)"
"        ->  Bitmap Heap Scan on planet_osm_point a
(cost=15.39..1193.83 rows=405 width=119)"
"              Recheck Cond: (tourism = 'viewpoint'::text)"
"              ->  Bitmap Index Scan on planet_osm_point_tourism
(cost=0.00..15.29 rows=405 width=0)"
"                    Index Cond: (tourism = 'viewpoint'::text)"

Yours, S.

2011/2/14 Pierre Racine <Pierre.Racine at sbf.ulaval.ca>:
> I don't know if this would be fast but I would query those buffers which do not intersects with a Union of the layer... Something like:
>
> SELECT id, name, elevation, geom
> FROM peaktable A, (SELECT ST_Union(ST_Buffer(geom, 5)) AS geom FROM peaktable) B
> WHERE NOT ST_Intersects(ST_Buffer(A.geom, 5), B.geom)
>
> or maybe better:
>
> SELECT id, name, elevation, geom
> FROM peaktable A, (SELECT ST_Union(ST_Buffer(geom, 5)) AS geom FROM peaktable) B
> WHERE NOT ST_DWithin(ST_Buffer(A.geom, 5), B.geom, 0)
>
> But I haven't tried them.
>
> Pierre
>
>>-----Original Message-----
>>From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
>>bounces at postgis.refractions.net] On Behalf Of Stefan Keller
>>Sent: 14 février 2011 14:59
>>To: PostGIS Users Discussion
>>Subject: Re: [postgis-users] Declarative SQL query for non-overlapping buffers given dense points?
>>
>>I take the silence as an evidence that there is no declarative
>>solution to this challenge :->.
>>I think I have to write a stored procedure.
>>
>>Yours, S.
>>
>>2011/2/13 Stefan Keller <sfkeller at gmail.com>:
>>> Hi
>>>
>>> Given a table of peaks with the fields id, name, elevation, geom I'd
>>> like to write a query which returns to me only those (randomly
>>> selected) peaks - i.e. peak buffers say with radius 5 kilometers -
>>> which dont 'overlap'. This is motivated by a visualization use case
>>> where label names should'nt overlap because of lack of visual space -
>>> as well as because of low network capacity between db and client!
>>>
>>> Any ideas on how to solve this with a declarative SQL query?
>>>
>>> The only promising approach I found so far was is to define a subquery
>>> with ranked buffers and then to select some out of these...
>>>
>>> -S.
>>>
>>_______________________________________________
>>postgis-users mailing list
>>postgis-users at postgis.refractions.net
>>http://postgis.refractions.net/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>