<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<div class="moz-cite-prefix">If it is 20 mill. coordinates, it could
be faster than 80 minutes, I guess in the region of 10 minutes.<br>
<br>
I would:<br>
1 Combine 3+4 to something like (I assume ST_Multi is not needed)
- this avoids data duplication and one unnecessary transaction
commit<br>
SELECT randfield, (st_dump(<font face="monospace, monospace">ST_Union(f.geom)</font>)).geom
AS geom FROM <font face="monospace, monospace">fishnet AS f GROUP
BY randfield</font><br>
<font face="monospace, monospace"><br>
2 R</font>emove the CREATE INDEX and/or add a spatial index - I
do not really understand the random union logic<br>
<br>
3 CLUSTER any index and <font face="monospace, monospace">ANALYZE
(after </font>CREATE INDEX)<br>
<br>
4 Check PostgreSQL configs (work_mem, shared_buffers etc) - your
stats shows a non-linear scaling above 100000 polys so db/hardware
limits are hit<br>
<br>
5 Disable autovacuum - may interfere during ST_Union execution<br>
<br>
6 Execute on real hardware - no (cheap) laptop and Linux is faster
- as an example this kind of PostGIS execution can be 2-3 times
faster on iMac compared with "standard laptop"<br>
<br>
7 Step 2 may be more efficient as well with <br>
<font face="monospace, monospace">ADD COLUMN randField integer
DEFAULT </font><font face="monospace, monospace">ceil(RANDOM()*10)</font><br>
<br>
</div>
<blockquote type="cite"
cite="mid:CAHNf2YSh+aUCVe6p_ViJn-VnDEqrX+SaNkr09mWW8oQVjqLEsw@mail.gmail.com">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<div dir="ltr">
<div>
<div dir="ltr" class="gmail_signature">
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">I took the advice of Andreas and
converted my code to using PostGIS.</div>
<div dir="ltr">And the speed difference is enormous.</div>
<div dir="ltr"><br>
</div>
<div dir="ltr">The commands I've used:</div>
<div dir="ltr"><font face="monospace, monospace">//
Import shapefile into PostGIS:</font></div>
<div dir="ltr"><font face="monospace, monospace">ogr2ogr
-f PostgreSQL PG:"host=localhost user=..."
fishnet.shp -gt unlimited -lco
GEOMETRY_NAME=geom -a_srs "EPSG:28992"</font></div>
<div dir="ltr"><font face="monospace, monospace">//
Add random data: </font></div>
<div dir="ltr"><font face="monospace, monospace">ogrinfo
PG:"host=localhost user=..." -sql "ALTER TABLE
fishnet ADD COLUMN randField integer;UPDATE
fishnet SET randField = ceil(RANDOM()*10);CREATE
INDEX randfield_idx ON fishnet (randField);</font></div>
<div dir="ltr"><font face="monospace, monospace">//
Dissolve:</font></div>
<div dir="ltr"><font face="monospace, monospace">ogrinfo
PG:"host=localhost user=..." -sql "CREATE TABLE
dissolved AS SELECT randfield,
ST_Multi(ST_Union(f.geom)) AS geom FROM fishnet
AS f GROUP BY randfield"</font></div>
<div dir="ltr"><font face="monospace, monospace">//
Export to shapefile</font></div>
<div dir="ltr"><font face="monospace, monospace">ogr2ogr
-f "ESRI Shapefile" taskmap.shp
PG:"host=localhost user=..." -sql "SELECT
randfield, (st_dump(geom)).geom AS geom FROM
dissolved" -overwrite -gt unlimited</font></div>
<div dir="ltr"><font face="monospace, monospace">//
Clean up:</font></div>
<div dir="ltr"><font face="monospace, monospace">ogrinfo
PG:"host=localhost user=..." -sql "DROP TABLE IF
EXISTS fishnet CASCADE;DROP TABLE IF EXISTS
dissolved CASCADE"</font></div>
<div dir="ltr"><br>
</div>
<div dir="ltr">The timing of the steps, after I
converted the above commands to C#:</div>
<div dir="ltr"><font face="monospace, monospace">|
#shapes |Import |Add values | Dissolve |
Export |</font></div>
<div dir="ltr"><font face="monospace, monospace">|1,677
|00:00:00.29|00:00:00.14|00:00:00.10|00:00:00.05|</font></div>
<div dir="ltr"><font face="monospace, monospace">|4,810
|00:00:00.28|00:00:00.20|00:00:01.11|00:00:00.15|</font></div>
<div dir="ltr"><font face="monospace, monospace">|18,415
|00:00:00.78|00:00:00.53|00:00:04.57|00:00:00.31|
</font></div>
<div dir="ltr"><font face="monospace, monospace">|72,288
|00:00:02.07|00:00:02.13|00:00:22.56|00:00:01.02|</font></div>
<div dir="ltr"><font face="monospace, monospace">|285,927
|00:00:07.58|00:00:06.68|00:01:59.59|00:00:03.82|</font></div>
<div dir="ltr"><font face="monospace, monospace">|1,139,424|00:00:26.63|00:00:33.51|00:11:34.63|00:00:15.19|</font></div>
<div dir="ltr"><font face="monospace, monospace">|4,546,854|00:01:46.19|00:03:51.92|01:10:07.41|00:00:57.51|</font></div>
<div dir="ltr"><br>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
4.5 million squares of 0.3m have a total area of about 40 ha.
<div>That is good enough for me. The total time will be around
80 min, instead of days using SQLite.</div>
<div><br>
</div>
<div>I'm assuming I can't speed up this command anymore, right?</div>
<div>
<span
style="font-family:monospace,monospace;font-size:small;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">ogrinfo
PG:"host=localhost user=..." -sql "CREATE TABLE dissolved AS
SELECT randfield, ST_Multi(ST_Union(f.geom)) AS geom FROM
fishnet AS f GROUP BY randfield"</span> <br>
</div>
<div><br>
</div>
<div>Thanks all for your valuable help.</div>
<div><br>
</div>
<div>Regards,</div>
<div><br>
</div>
<div>Paul<br>
<br>
<div class="gmail_quote">
<div dir="ltr">Op ma 16 jul. 2018 om 11:30 schreef Andreas
Oxenstierna <<a href="mailto:ao@t-kartor.se"
moz-do-not-send="true">ao@t-kartor.se</a>>:<br>
</div>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="auto">ST_Union in PostGIS should scale better
than SQLite.
<div>ST_Dump gives you singlepart geometries. </div>
<div><br>
<div>Best Regards
<div><br>
</div>
<div>Andreas Oxenstierna</div>
</div>
</div>
</div>
</blockquote>
</div>
</div>
</div>
</blockquote>
<p><br>
</p>
<pre class="moz-signature" cols="72">--
Hälsningar
Andreas Oxenstierna
T-Kartor Geospatial AB
mobile: +46 733 206831
mailto: <a class="moz-txt-link-abbreviated" href="mailto:ao@t-kartor.se">ao@t-kartor.se</a>
<a class="moz-txt-link-freetext" href="http://www.t-kartor.com">http://www.t-kartor.com</a></pre>
</body>
</html>