[OSM-dev] Brin index on contour lines DB

[yvecai]

Reply to self:
BRIN index does not seems sufficient for rendering performance.
One can save quite some space with the following:

# No need to keep space for future INSERT / UPDATE:
ALTER TABLE contours SET (fillfactor = 100);
# Compress even smallgeometries (normally >2KB only), big reduction in 
disk space in conjonction with ST_QuantizeCoordinates():
ALTER TABLE contours set (toast_tuple_target = 128);
UPDATE contours set geom=ST_QuantizeCoordinates(ST_SnapToGrid(geom, 2),0)
# Cluster the data properly (big jump in access performance):
CREATE INDEX tmp_geohash ON contours 
(ST_GeoHash(ST_Transform(ST_Envelope(geom),4326))) WITH (fillfactor=100);
nohup psql -c 'CLUSTER VERBOSE contours USING tmp_geohash;' -d contours &
# Douglas peucker 4meter is still quite good for a 1arcsec resolution.

Disk space reduction from 504GB to 327GB.

Yves

On 29.04.23 10:13, Yves wrote:
> Hello,
> I'm reworking the DEM at Opensnowmap.org taking advantage of modern 
> lidar data while keeping a ~30m resolution.
>
> 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.
> 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.
> Anyone has already experience in this?
>
>
> 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.
>
> Other optimisation suggestions welcome.
>
> Regards,
> Yves
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20230529/1275891d/attachment.htm>