<html><head><meta http-equiv="Content-Type" content="text/html charset=windows-1252"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">Following advice from Mark and Remi, I am now tiling the troublesome layer. It's not going quickly. I'm trying an intersection of the layer with only 5 of my 10kmX10km grids and it has been running for 30 min. I have 8680 grids, which is more features than the layer I was originally working with (2709 polygons). I was hoping that intersection with squares would go much faster than an intersection with arbitrary polygons, but I can confirm that it's definitely not orders of magnitude faster.<div><br></div><div>So, I think my idea of simplifying things recursively with a quad grid is worth a shot. Mark, can you point me to your Quadgrid function?</div><div><br></div><div>Mark suggested counting points. Counting points (and rounding to the nearest 10000) gives:</div><div><br></div><div><div><font face="Courier New">select round(st_npoints(geom)/100,-2) as point100, count(*)</font></div><div><font face="Courier New">from lancover_polygons_snap group by round(st_npoints(geom)/100,-2) </font></div><div><font face="Courier New">order by point100 desc;</font></div><div><font face="Courier New"><br></font></div><div><div><font face="Courier New">26300;1</font></div><div><font face="Courier New">1500;1</font></div><div><font face="Courier New">1100;1</font></div><div><font face="Courier New">800;1</font></div><div><font face="Courier New">600;2</font></div><div><font face="Courier New">500;1</font></div><div><font face="Courier New">400;5</font></div><div><font face="Courier New">300;10</font></div><div><font face="Courier New">200;22</font></div><div><font face="Courier New">100;141</font></div><div><font face="Courier New">0;997846</font></div></div><div><br></div><div>The one with about 2630000 points represents the developed land in the region (towns and cities including roads that connect them).</div><div><br></div><div>Mark also suggested counting interior rings. </div><div><br></div><div><div><font face="Courier New">select round(ST_NumInteriorRings(st_geometryN(geom,1)),-1) as rings10, avg(st_numpoints(geom)) as avgpts,</font></div><div><font face="Courier New">count(*) as numfeatures</font></div><div><font face="Courier New">from lancover_polygons_snap group by round(ST_NumInteriorRings(st_geometryN(geom,1)),-1)</font></div><div><font face="Courier New">order by rings10 desc;</font></div></div><div><font face="Courier New"><br></font></div><div><div><div><font face="Courier New">43140;2629905;1</font></div><div><font face="Courier New">4230;147121;1</font></div><div><font face="Courier New">3800;110083;1</font></div><div><font face="Courier New">2480;76515;1</font></div><div><font face="Courier New">1760;50261;1</font></div><div><font face="Courier New">1720;55576;1</font></div><div><font face="Courier New">1680;64588;1</font></div><div><font face="Courier New">1350;41826;1</font></div><div><font face="Courier New">1130;38059;1</font></div><div><font face="Courier New">970;36425;1</font></div><div><font face="Courier New">950;37686;1</font></div><div><font face="Courier New">860;32952;1</font></div><div><font face="Courier New">850;34657;1</font></div><div><font face="Courier New">820;35285;2</font></div><div><font face="Courier New">810;24769;1</font></div><div><font face="Courier New">800;28113;1</font></div><div><font face="Courier New">760;30726;1</font></div><div><font face="Courier New">740;26296;1</font></div><div><font face="Courier New">720;24788;2</font></div><div><font face="Courier New">670;18874;1</font></div><div><font face="Courier New">660;22428;1</font></div><div><font face="Courier New">640;26258;1</font></div><div><font face="Courier New">630;27213;2</font></div><div><font face="Courier New">620;22056;2</font></div><div><font face="Courier New">610;15771;1</font></div><div><font face="Courier New">560;19942;2</font></div><div><font face="Courier New">540;23988;1</font></div><div><font face="Courier New">500;15763;2</font></div><div><font face="Courier New">480;16598;1</font></div><div><font face="Courier New">470;19865;2</font></div><div><font face="Courier New">410;15466;1</font></div><div><font face="Courier New">400;16023;1</font></div><div><font face="Courier New">380;13536;4</font></div><div><font face="Courier New">370;14646;3</font></div><div><font face="Courier New">360;14122;1</font></div><div><font face="Courier New">340;12910;1</font></div><div><font face="Courier New">330;13273;1</font></div><div><font face="Courier New">320;15222;2</font></div><div><font face="Courier New">300;13421;1</font></div><div><font face="Courier New">290;9072;3</font></div><div><font face="Courier New">280;10025;4</font></div><div><font face="Courier New">270;12505;4</font></div><div><font face="Courier New">260;11002;5</font></div><div><font face="Courier New">250;9843;3</font></div><div><font face="Courier New">240;9980;6</font></div><div><font face="Courier New">230;11456;3</font></div><div><font face="Courier New">220;9049;3</font></div><div><font face="Courier New">210;9194;6</font></div><div><font face="Courier New">200;8586;5</font></div><div><font face="Courier New">190;7089;5</font></div><div><font face="Courier New">180;6796;8</font></div><div><font face="Courier New">170;6913;8</font></div><div><font face="Courier New">160;6955;6</font></div><div><font face="Courier New">150;5911;10</font></div><div><font face="Courier New">140;5769;15</font></div><div><font face="Courier New">130;5377;13</font></div><div><font face="Courier New">120;5301;17</font></div><div><font face="Courier New">110;5246;17</font></div><div><font face="Courier New">100;4422;28</font></div><div><font face="Courier New">90;4332;28</font></div><div><font face="Courier New">80;3664;35</font></div><div><font face="Courier New">70;3292;43</font></div><div><font face="Courier New">60;2860;66</font></div><div><font face="Courier New">50;2236;74</font></div><div><font face="Courier New">40;1882;141</font></div><div><font face="Courier New">30;1493;215</font></div><div><font face="Courier New">20;993;618</font></div><div><font face="Courier New">10;445;3325</font></div><div><font face="Courier New">0;20;993265</font></div></div></div><div><font face="Courier New"><br></font></div><div><font face="Courier New"><br></font></div><div>--</div><div><div apple-content-edited="true"><span class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px;"><div>John Abraham</div><div><a href="mailto:jea@hbaspecto.com">jea@hbaspecto.com</a></div><div>403-232-1060</div></span>
</div>
<div><br></div><div><br></div><font face="Courier New" style="font-size: 12px;">"POSTGIS="2.0.3 r11128" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.8" LIBJSON="UNKNOWN" (core procs from "2.0.3 r11132" need upgrade) TOPOLOGY (topology procs from "2.0.3 r11132" need upgrade) RASTER (raster procs from "2.0.3 r11132" need upgrade)"</font></div><div><br></div><div><br><div><div>On Feb 24, 2015, at 3:31 PM, Rémi Cura <<a href="mailto:remi.cura@gmail.com">remi.cura@gmail.com</a>> wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><div dir="ltr"><div><div><div><div><div><div><div>Hey,<br></div>I highjack this interesting discussion, sorry =)<br><br></div>If you have one single massive Polygon (no multi).<br></div>You can generate a point table (keeping path of course) with ST_DumpPoints.<br><br></div>Then you construct an index on this table.<br></div>Then you perform your Intersection with indexed points, which will be very fast.<br></div><div>Then you reconstruct parts of polygon.<br></div><div>It will require some advanced to very advanced SQL (like windows function) to make it work tough.<br><br></div><div>I think it would be faster than other alternatives, because basicaly it amounts to using the optimized R-Tree index on points, vs a custom and non-otpimized Quad tree index on polygon.<br></div><div>Moreover when using a recusrive strategy, you end up computing the same think a lot with geos.<br><br></div>Cheers,<br></div>Rémi-C<br></div><div class="gmail_extra"><br><div class="gmail_quote">2015-02-24 23:10 GMT+01:00 Mark Wynter <span dir="ltr"><<a href="mailto:mark@dimensionaledge.com" target="_blank">mark@dimensionaledge.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><br>
> Thanks Mark. I will indeed do the st_dump, but I don't think it will help much (very few multis). I think the tiling will help a lot. What I wonder, though, is how long it will take to tile? Afterall, it's still an st_intersection operation to tile each geometry against each tile.<br>
<br>
I’ve almost finished writing the tutorial - where I address many of these points. The variables that affect performance are:<br>
* how you’ve written your ST_Intersection query<br>
* multi vs. non-multi<br>
* size and complexity of geoms<br>
* no. available CPUs (for parallelisation)<br>
* tile batch size - important!!!<br>
<br>
All strategies in combination may be necessary if your queries are taking forever.<br>
<br>
For the demonstration dataset (a multi polygon representing whole of Australia), my tutorial tiling query incorporates ST_Intersection and ST_Difference subqueries to produce tiled features representing land and water.<br>
I achieved a 49x reduction in the query time to tile the whole of Australia, starting with a single multi polygon.<br>
<br>
The more complex the query, the more significant this time saving is in absolute terms.<br>
<br>
> Is there a quad tree type tiling algorithm in a function? If I do 256 x 256 tiles, doing it all at once would be 65536 operations of st_intersection(complex_geom, square). With a quad tree I'll only have 4 operations of st_intersection(complex_geom, square), and then 16 of (a_little_less_complex_geom, square), and then 64 of (even_less_complex_geom, square) and so on, for 8 nests. The last one will still be 65536 operations, but the complex geoms should be a lot simpler by the time I get down to that level. What do you think, is this worth trying? Or is intersecting with a square fairly simple regardless of how complex the other geometry is?<br>
<br>
I do have a SQL quadgrid tiling function - where a cell divides recursively subject to a maximum number of levels or “value thresholds” - but I’m not sure if that’s the right approach.<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
</blockquote></div><br></div>
_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</blockquote></div><br></div></div></body></html>