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

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


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.

  • I don't need to be real time, I'm just trying to optimise the stuff. I forgot to mention it, but I only display the buffers when the scale is smaller than 1/50000. As I understand it, the heavy lifting is done when seeding/generating the tiles. In that case, since the geometry is fairly complex and cannot be split into multiple small polygons (since all roads are interconnected), the entire buffer has to be loaded each time a tile is rendered. It seems more efficient when caching the tiles to only query a limited number of polygons, merge them and then render them. Hence the question.
    – Renaud
    Aug 20 '13 at 19:36

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .