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<[email protected]>:
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: [email protected] [mailto:postgis-users-
[email protected]] 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<[email protected]>:
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
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
