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

[Stefan Keller]

No this was something I also thought of.

Now I got an idea:

SELECT ST_AsText(way) as geom, name as label
FROM
  peaks t1
WHERE
  t1.id = (
   SELECT id
   FROM (
     SELECT ST_SnapToGrid(geom, 5) as geom, ele, id
     FROM peaks
   ) t2
   WHERE ST_Equals(ST_SnapToGrid(t1.geom, 5), t2.geom)
   ORDER BY ele DESC LIMIT 1
  )

... where 5 has to be parametrised according to srid units and "density"

I think, there could be perhaps an even more elegant solution with the
new WINDOW syntax?

Yours, S.

2011/2/15 Birgit Laggner <birgit.laggner at vti.bund.de>:
>  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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>