[postgis-users] Optimizations: splitting large features

[Brent Wood]

--- Jeff Dege <jdege at korterra.com> wrote:

> Which involves learning PGSQL.  Which I'll have to do, eventually.
>  
> It's not clear to me that this approach would work for polygons, but
> it's not clear to me that I'm having this problem with anything but
> linestrings, so that probably doesn't matter.  (I sometimes have to draw
> maps showing hundreds of linear features criss-crossing over an area.
> When I have hundreds of two-dimensional overlapping features, my maps
> always seem to involve displaying just a few of them.)


Hi Jeff,

Some general comments:

Generally I have found a well indexed PostGIS table will very quickly return
just the few appropriate features to the renderer.

Tiling & other approaches to improve performance are often required for sorting
out which images, or shapefiles are required. I think it is fair to say that
any decent DBMS has similar capabilities built in, under the "M" word in DBMS,
so using the DB tools for this is usually adequate, and resorting to other ways
to augment performance is mostly unnecessary. 

The same features will be plotted whatever the source or access method, so I
assume the issue is one of addressing performance. I have found PostGIS on
reasonable hardware can easily cope with tens of thousands of features selected
and rendered from tables containing millions, and for a few seconds wait,
occasionally hundreds of thousands of rendered line & polygon features.

For large numbers of records (rather than large or geometries) you can
partition and cluster your table. For zoom layering, simplified versions are
easily constucted & work well for plotting, but as discussed previously, are
not appropriate for querying against due to the introduced inaccuracies &
errors.

Perhaps you could clarify what your performance problem is, and describe the
tables & their indices & the hardware setup if you think things are slower than
they should be.


Cheers,
  
   Brent Wood