Map Tiles (Trip Server Application Manual version 2.0.0-rc.5)

9.1.1 Map Tiles

Monthly cumulative totals of map tile usage for the last year:

SELECT year, month, max(count) AS cumulative_total FROM (
        SELECT time, extract(year from time) AS year,
        extract(month from time) AS month,
        extract(day from time) AS day,
        count FROM tile_metric ORDER BY time DESC) AS q
GROUP BY q.year, q.month ORDER BY q.year desc, q.month DESC LIMIT 12;

Count of expired tiles:

SELECT count(*) FROM tile WHERE expires < now();

Count of unexpired tiles;

SELECT count(*) FROM tile WHERE expires >= now();

Count of expired tiles older than 90 days:

SELECT count(*) FROM tile WHERE expires < now() AND
updated < now()::timestamp::date - INTERVAL '90 days';

Delete expired tiles older than 90 days:

DELETE FROM tile WHERE expires < now() AND
updated < now()::timestamp::date - INTERVAL '90 days';

Delete all expired tiles:

DELETE FROM tile WHERE expires < now();