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

[Birgit Laggner]

  Hallo Stefan and Pierre,

are you sure this could work? I would expect that you can't find any 
results because every geometry you are testing with NOT ST_Within() is 
part of the ST_Union() in B. Unfortunately, I have no other idea how to 
solve that without a stored procedure...

Regards,

Birgit


On 15.02.2011 07:52, Stefan Keller wrote:
> 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
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users