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
