- Solutions Summit 2016
- Jorge Sanz
- How to render points between 1M and 50M
- Point rendering time is linear
- We need to reduce the number of points
sent to the tiler (aggregate)
Used to convert scale denominator to zoom level
CREATE OR REPLACE FUNCTION zoom(scaledenominator numeric)
RETURNS integer
AS $$
BEGIN
CASE
WHEN scaleDenominator > 1000000000
THEN RETURN 0;
WHEN scaleDenominator <= 1000000000 AND scaleDenominator > 500000000
THEN RETURN 1;
WHEN scaleDenominator <= 500000000 AND scaleDenominator > 200000000
THEN RETURN 2;
WHEN scaleDenominator <= 200000000 AND scaleDenominator > 100000000
THEN RETURN 3;
...
WHEN scaleDenominator <= 100
THEN RETURN 23;
END CASE;
END;
$$ language 'plpgsql';One per zoom level
CREATE MATERIALIZED view ov_0_pois AS
WITH par AS (
WITH innerpar AS (
SELECT 1.0/(CDB_XYZ_Resolution(0)*2) AS resinv -- Zoom level here
)
SELECT
CDB_XYZ_Resolution(0)*2 AS res, -- Zoom level here
innerpar.resinv AS resinv
FROM innerpar
), geo AS (
SELECT
floor(st_x(i.the_geom_webmercator)*resinv) AS xx,
floor(st_y(i.the_geom_webmercator)*resinv) AS yy,
count(cartodb_id) AS c -- Aggregation function here
FROM (
SELECT * FROM pois -- Original data here
) i, par p
GROUP BY xx, yy
)
SELECT
c,
st_setsrid(st_makepoint(
xx*CDB_XYZ_Resolution(0)*2, -- Zoom level here
yy*CDB_XYZ_Resolution(0)*2 -- Zoom level here
),3857) AS the_geom_webmercator
FROM geo;
CREATE INDEX ov_0_pois_geom_idx ON ov_0_pois USING GIST(the_geom_webmercator);
GRANT SELECT ON ov_0_pois TO tileuser;
GRANT SELECT ON ov_0_pois TO publicuser;- Two sets of materialized views
- Refresh the one not used
- Swap all of them in one transaction
- Sleep until tomorrow
Using the !scale_denominator! mapnik variable to render different
geometries based on the zoom level.
WITH a AS (
SELECT zoom('!scale_denominator!') AS current_zoom -- zoom_level
)
SELECT
the_geom_webmercator,c,null AS cat
FROM ov_0_pois, a WHERE current_zoom = 0 UNION -- union zoom level 0
SELECT
the_geom_webmercator,c,null AS cat
FROM ov_1_pois, a WHERE current_zoom = 1 UNION -- union zoom level 1
...
SELECT
the_geom_webmercator,c,null AS cat
FROM ov_13_pois, a WHERE current_zoom = 13 UNION -- union zoom level 13
SELECT
the_geom_webmercator,
1 AS c,
category_name AS cat
FROM pois, a WHERE current_zoom >= 14 -- real data from level 14 and aboveThis technique is useful on other use cases when we want to change the geometries based on the zoom level!!
- Big datasets will be automatically detected
CDB_CreateOverviews('table')to start the overviews generation (tweakstatement_timeoutand maybe go topsql)- Look for
_vovw_ZOOM_LEVEL_tables or useCDB_Overviews('table'::regclass)function - Remove with
CDB_DropOverviews('table'::regclass) - Maps API will use it automatically (code)
- This change the way we use symbology (
comp-op's have no effect on overviews)
More info here
Run this to create overviews on your new table.
set statement_timeount=9999999;
SELECT
CDB_CreateOverviews('table_name');<iframe src="https://team.cartodb.com/u/jsanz/viz/2d2dbc20-0b3f-11e6-abc3-0ecd1babdde5/embed_map" frameborder="0" style="width:100%;height:500px;" > </iframe>