I remember looking at Manifold's SPLIT BY function and thinking - "that's what I'm missing in my life - a inverse GROUP BY."
I was disappointed to find out that there SPLIT BY only supports 3 split functions which makes it much more limiting than the generate_series approach. It makes nice syntactic sugar at the very least and perhaps if PostgreSQL could implement SPLIT TYPE functions which would be the inverse of the AGGREGATE functions, then this would be much more interesting. Looking at Kevin's results I am beginning to think the bottleneck may be in the GeomN type functions and maybe he is right that its doing a copy call for each or something silly like that or a memory leak (I wasn't looking is it linear or quadratic as the number of geometries increases?). I'm relieved the sub select thing came up with the same results as if it was better it would have shattered some of my beliefs on how things work. Kevin, What happens if you leave out the PointN call altogether just to see what kind of penalty is being incurred there. e.g. SELECT t.gid, generate_series(1, ST_NumPoints(the_geom)) FROM kneufeld.tmp t Thanks, Regina -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Martin Davis Sent: Friday, April 18, 2008 2:22 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Does anyone have multipolygons with large numbersof polygons 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@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > -- Martin Davis Senior Technical Architect Refractions Research, Inc. (250) 383-3022 _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
