[postgis-users] Does anyone have multipolygons with large numbers of polygons

[Martin Davis]

This is an interesting problem.  I think the general pattern can be 
stated as something like: "Split a complex column value across multiple 
rows, along with other columns from the original record".  (A nice way 
of thinking of this is that it's the inverse of GROUP BY).

This is surprisingly awkward to do in standard SQL.  It's easier in 
PostgreSQL since it has the generate_series() enhancement, but as you 
are seeing it's still not a great general solution to the problem.

The annoying thing is is that the underlying processing required to 
compute the desired result is actually pretty trivial.  This is one of 
those situations where the fairly limited declaritive semantics of SQL 
get in your way.

Manifold SQL has an extension which adds a SPLIT BY clause to handle 
exactly this problem.  I suspect they have a pretty efficient 
implementation, since it is implemented natively in the engine.

http://www.manifold.net/doc/queries_and_geoms.htm

Maybe it's time to lobby to get this added to PostgreSQL as well?

Obe, Regina wrote:
> I know there has been some discussion in the past about how
> generate_series doesn't scale well when you are dealing with large
> numbers of geometries.  My suspicion was that it was because
> generate_series has to materialize the result before outputting it and
> that the result key is not indexed.
>
> Unfortunately I don't have geometries large enough to test the theory
> out, but does seem to be faster for me even for  structures where I have
> more than 10 or geometries per multipolygon.  Can some people try this
> out.  The below will take each multi geom and break it out into single
> geom structures and will handle in theory a MULTI structure with up to
> 500,000 geoms and can easily handle more by increasing the number of
> records in the materialized generate series table.
>
> Can you compare this:
>
> SELECT g.n
> 	INTO pgis_gs
> FROM generate_series(1,500000) g(n);
>
> ALTER TABLE pgis_gs
>   ADD CONSTRAINT pgis_gs_pkey PRIMARY KEY(n);
>
> SELECT t.gid, ST_GeometryN(the_geom, g.n) As poly
> FROM somtable t CROSS JOIN pgis_gs g
> WHERE g.n <= ST_NumGeometries(the_geom);
>
> With these:
>
> --this will handle at most 5000 (for small sets of geometries - incurs a
> huge penalty)
>
> SELECT t.gid, ST_GeometryN(the_geom, g.n) As poly
> FROM somtable t CROSS JOIN generate_series(1,5000) g(n)
> WHERE g.n <= ST_NumGeometries(the_geom);
>
> -this can handle more but I suspect gets really bad for large numbers
> SELECT t.gid, ST_GeometryN(the_geom, ST_NumGeometries(the_geom)) As poly
> FROM somtable t;
>
> Thanks,
> Regina
> -----------------------------------------
> The substance of this message, including any attachments, may be
> confidential, legally privileged and/or exempt from disclosure
> pursuant to Massachusetts law. It is intended
> solely for the addressee. If you received this in error, please
> contact the sender and delete the material from any computer.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>   

-- 
Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022