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).