<HTML dir=ltr><HEAD><TITLE>Re: [postgis-devel] Split By function</TITLE>
<META http-equiv=Content-Type content="text/html; charset=unicode">
<META content="MSHTML 6.00.6000.16674" name=GENERATOR></HEAD>
<BODY>
<DIV id=idOWAReplyText66624 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>Instinct. I guess I just never put two and two together that it can be used with dump until Dave asked that question and I remembered Kevin using dump in an example. Strange how if you are asked the same question over and over again you come up with a different answer.</FONT></DIV></DIV>
<DIV dir=ltr><BR>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> postgis-devel-bounces@postgis.refractions.net on behalf of Kevin Neufeld<BR><B>Sent:</B> Mon 6/23/2008 4:44 PM<BR><B>To:</B> PostGIS Development Discussion<BR><B>Subject:</B> Re: [postgis-devel] Split By function<BR></FONT><BR></DIV>
<DIV>
<P><FONT size=2>Where did you see this syntax? This is great. I had no idea you could<BR>query like that. Very nice.<BR>SELECT (ST_Dump(the_geom)).geom<BR>FROM ...<BR><BR>So, here's a test sequence of commands to answer your question.<BR>CREATE SCHEMA tmp;<BR>SET search_path to tmp, public;<BR>-- Create a point table with 5000 points<BR>CREATE TABLE _5000 AS SELECT makepoint(random(), random()) AS the_geom<BR>FROM generate_series(1, 5000);<BR>-- Create a point table with 10000 points<BR>CREATE TABLE _10000 AS SELECT makepoint(random(), random()) AS the_geom<BR>FROM generate_series(1, 10000);<BR>CREATE TABLE _20000 AS SELECT makepoint(random(), random()) AS the_geom<BR>FROM generate_series(1, 20000);<BR>-- Create a multipoint table with the 5000 points<BR>CREATE TABLE _5000_m AS SELECT collect(the_geom) AS the_geom FROM _5000 ;<BR>CREATE TABLE _10000_m AS SELECT collect(the_geom) AS the_geom FROM _10000 ;<BR>CREATE TABLE _20000_m AS SELECT collect(the_geom) AS the_geom FROM _20000 ;<BR><BR><BR>As I recall, we determined that generate_series() *does* scale well, but<BR>the ST_GeometryN function does not.<BR>EXPLAIN ANALYZE<BR>SELECT count(*)<BR>FROM (<BR> SELECT ST_GeometryN(the_geom, generate_series(1,<BR>ST_Numgeometries(the_geom)))<BR> FROM _1000_m<BR>) a;<BR>-- Total runtime: 398.582 ms<BR><BR>EXPLAIN ANALYZE<BR>SELECT count(*)<BR>FROM (<BR> SELECT ST_GeometryN(the_geom, generate_series(1,<BR>ST_Numgeometries(the_geom)))<BR> FROM _5000_m<BR>) a;<BR>-- Total runtime: 11793.898 ms<BR><BR>EXPLAIN ANALYZE<BR>SELECT count(*)<BR>FROM (<BR> SELECT ST_GeometryN(the_geom, generate_series(1,<BR>ST_Numgeometries(the_geom)))<BR> FROM _10000_m<BR>) a;<BR>-- ERROR: out of memory<BR>-- DETAIL: Failed on request of size 12.<BR><BR><BR>Ok, so now using the Regina's notation,<BR>EXPLAIN ANALYZE<BR>SELECT count(*)<BR>FROM (<BR> SELECT (ST_Dump(the_geom)).geom<BR> FROM _5000_m<BR>) a;<BR>-- Total runtime: 79.239 ms<BR><BR>EXPLAIN ANALYZE<BR>SELECT count(*)<BR>FROM (<BR> SELECT (ST_Dump(the_geom)).geom<BR> FROM _10000_m<BR>) a;<BR>-- Total runtime: 158.046 ms<BR><BR>EXPLAIN ANALYZE<BR>SELECT count(*)<BR>FROM (<BR> SELECT (ST_Dump(the_geom)).geom<BR> FROM _20000_m<BR>) a;<BR>-- Total runtime: 319.109 ms<BR><BR><BR>So, not only does ST_GeometryN not scale well (what is that, quadratic<BR>scaling?), but it consumes all memory on my 4GB server.<BR><BR>SELECT (ST_Dump(geom)).geom on the other hand is linear and has no<BR>problems.<BR><BR>Unfortunately, this doesn't solve the ST_PointN equivalent problem.<BR><BR>I suggest we add a ST_DumpPoints() method to the TODO list!<BR><BR>Cheers,<BR>Kevin<BR><BR><BR>Obe, Regina wrote:<BR>> I recall we were discussing a need for a split by function and that<BR>> ST_GeometryN etc. is slow for large geometries. Unfortunately I don't<BR>> have any humongous geometries to test with.<BR>> <BR>> I just looked at ST_Dump (not sure why I never used it before, I guess<BR>> because generate_series is more versatile.). Anyrate it seems to me it<BR>> comes very close to a split by and with range ability.<BR>> <BR>> I presume ST_Dump is much more efficient than ST_GeometryN since it<BR>> would make only one call out. Kevin can you test out that old<BR>> generate_series one you had with dump?<BR>> <BR>> E.g. I can do this<BR>> <BR>><BR>> SELECT a.somefield1, a.somefield2, a.geom<BR>> FROM<BR>> (SELECT somefield1, somefield2, (ST_Dump(the_geom)).*<BR>> FROM sometablewithmulties) as a<BR>> WHERE<BR>> a.path[1] between 2 and 5;<BR>> <BR>> To grab all geometries in all records in a MULTI between 2 and 5 slot.<BR>> Granted I can't think of a use for such a thing.<BR>> <BR>> Which allows me to wrap this in an sql function wrapper to generate a<BR>> pgis_GeometryRangeN(start int, end int)<BR>> <BR>> <BR>> or<BR>> pgis_GeometryAll() returns geometry[]<BR>> <BR>> similarly the below denatures a MULTIPOLYGON/LINESTRING to single<BR>> POLYGON/LINESTRINGS<BR>> SELECT a.somefield, (ST_Dump(the_geom)).geom<BR>> FROM sometable as a;<BR>> <BR>> Which allows me to wrap this in a simple SQL function that does all<BR>> sorts of split by stuff. E.g. for geometry collections I suppose it<BR>> could get interesting with those multidimensional arrays. Unfortunately<BR>> I guess ST_Dump is not going to deal with single geometries and breaking<BR>> into points etc. But seems if its efficient for the other case - we can<BR>> create a similar one to deal with the other cases by cutting and pasting<BR>> some of the logic.<BR>> <BR>> Thanks,<BR>> Regina<BR>> <BR>><BR>> <BR>> <BR>> <BR>><BR>> ------------------------------------------------------------------------<BR>><BR>> * The substance of this message, including any attachments, may be<BR>> confidential, legally privileged and/or exempt from disclosure pursuant<BR>> to Massachusetts law. It is intended solely for the addressee. If you<BR>> received this in error, please contact the sender and delete the<BR>> material from any computer. *<BR>><BR>> ------------------------------------------------------------------------<BR>><BR>> * Help make the earth a greener place. If at all possible resist<BR>> printing this email and join us in saving paper. *<BR>><BR>> * *<BR>><BR>> * *<BR>><BR>><BR>> ------------------------------------------------------------------------<BR>><BR>> _______________________________________________<BR>> postgis-devel mailing list<BR>> postgis-devel@postgis.refractions.net<BR>> <A href="http://postgis.refractions.net/mailman/listinfo/postgis-devel">http://postgis.refractions.net/mailman/listinfo/postgis-devel</A><BR>_______________________________________________<BR>postgis-devel mailing list<BR>postgis-devel@postgis.refractions.net<BR><A href="http://postgis.refractions.net/mailman/listinfo/postgis-devel">http://postgis.refractions.net/mailman/listinfo/postgis-devel</A><BR></FONT></P></DIV></BODY></HTML>