<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<div class="moz-cite-prefix">Reply to self:</div>
<div class="moz-cite-prefix">BRIN index does not seems sufficient
for rendering performance.<br>
</div>
<div class="moz-cite-prefix">One can save quite some space with the
following:</div>
<div class="moz-cite-prefix"><br>
# No need to keep space for future INSERT / UPDATE:<br>
ALTER TABLE contours SET (fillfactor = 100);<br>
# Compress even smallgeometries (normally >2KB only), big
reduction in disk space in conjonction with
ST_QuantizeCoordinates():<br>
ALTER TABLE contours set (toast_tuple_target = 128);</div>
<div class="moz-cite-prefix">UPDATE contours set
geom=ST_QuantizeCoordinates(ST_SnapToGrid(geom, 2),0)<br>
</div>
<div class="moz-cite-prefix"># Cluster the data properly (big jump
in access performance):</div>
<div class="moz-cite-prefix">CREATE INDEX tmp_geohash ON contours
(ST_GeoHash(ST_Transform(ST_Envelope(geom),4326))) WITH
(fillfactor=100);<br>
nohup psql -c 'CLUSTER VERBOSE contours USING tmp_geohash;' -d
contours &</div>
<div class="moz-cite-prefix"># Douglas peucker 4meter is still quite
good for a 1arcsec resolution.</div>
<div class="moz-cite-prefix"><br>
</div>
<div class="moz-cite-prefix">Disk space reduction from 504GB to
327GB.<br>
</div>
<div class="moz-cite-prefix"><br>
</div>
<div class="moz-cite-prefix">Yves<br>
</div>
<div class="moz-cite-prefix"><br>
</div>
<div class="moz-cite-prefix">On 29.04.23 10:13, Yves wrote:<br>
</div>
<blockquote type="cite"
cite="mid:4A1A882F-0224-42EA-8998-59E0E9487D89@mailbox.org">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="auto">Hello,<br>
I'm reworking the DEM at Opensnowmap.org taking advantage of
modern lidar data while keeping a ~30m resolution. <br>
<br>
I'm planning to experiment on optimizing the world-wide contour
line DB. It's currently 504GB of dead data just for rendering,
and quite expensive in term of storage. <br>
The current Gist index takes 54GB on disk, so I guess I could
save this with a Brin index instead. After all contours requests
to the DB starts at zoom 11, so they are always big chunks in
the order of 0.1x0.1° max.<br>
Anyone has already experience in this? <br>
<br>
<br>
The other drastic measure I envision is to limit the data to
places with ski pistes, but that limit the usefulness of the
data : my last extract from 2015 was shared widely to a number
of projects. <br>
<br>
Other optimisation suggestions welcome. <br>
<br>
Regards, <br>
Yves <br>
<br>
</div>
</blockquote>
<p><br>
</p>
</body>
</html>