Merging polygons for rendering with Geoserver

Question

For my application, I need to merge the polygons of different records into one polygon, but only for the rendering (WMS Map).

If it's not clear, here's the goal: a PostGIS table contains the buffers around linear structures (roads, railways,...). Each element of each the structure has its own buffer and I'd like to show on the map only the union of the buffers grouped by type (one for the rails, one for the roads,...).

I've tried the following:

• having only one buffer object per kind in the table (multipolygon): very slow, since it requires to load the entire polygon each time it is accessed, and not very flexible when the source data is modified

• creating a view in the PostGIS table that groups every thing using the following query:

  SELECT min(restriction.id) AS gid, st_multi(st_union(restriction.geom)) AS geom, restriction.type FROM public.restriction GROUP BY restriction.type

Also very slow, since the union is done each time the view is accessed

• Using Geoserver SQL View's feature. According to the following post, a where statement is added to the view to limit the request to the queried bounding box, but is even slower than the PostGIS view: https://getsatisfaction.com/opengeo/topics/postgis_parameterized_view_dynamic_bounding_box

It is not possible to it with SLDs.

Is there any way to improve the query or to achieve the desired result, but faster?

I'm using the latest version of OpenGeo (3.1).

Answer 1

Depending on how 'real-time' you need to be, what about doing some processing to make a separate table(s) soley for display purposes? Instead of performing those queries 'live', all the heavy lifting would be done ahead of time. Might be able to schedule it if things change frequently.