https://postgis.net/docs/ST_DumpPoints.html

瓦片金字塔模型

PG库生成矢量瓦片

--经度转切片x
CREATE OR REPLACE FUNCTION lon2tile(lon DOUBLE PRECISION, zoom INTEGER)
  RETURNS INTEGER AS
$BODY$
    SELECT FLOOR( (lon + 180) / 360 * (1 << zoom) )::INTEGER;
$BODY$
  LANGUAGE SQL IMMUTABLE;
--纬度转切片y
CREATE OR REPLACE FUNCTION lat2tile(lat double precision, zoom integer)
  RETURNS integer AS
$BODY$
    SELECT floor( (1.0 - ln(tan(radians(lat)) + 1.0 / cos(radians(lat))) / pi()) / 2.0 * (1 << zoom) )::integer;
$BODY$
  LANGUAGE sql IMMUTABLE;
--xyz转几何
create or replace function TileBBox (z int, x int, y int, srid int = 3857)
    returns geometry
    language plpgsql immutable as
$func$
declare
    max numeric := 20037508.34;
    res numeric := (max*2)/(2^z);
    bbox geometry;
begin
    bbox := ST_MakeEnvelope(
        -max + (x * res),
        max - (y * res),
        -max + (x * res) + res,
        max - (y * res) - res,
        3857
    );
    if srid = 3857 then
        return bbox;
    else
        return ST_Transform(bbox, srid);
    end if;
end;
$func$;
--数据最大最小经纬度计算最大最小xyz
select lat2tile(ST_Y((pt).geom),12) y, lon2tile(ST_X((pt).geom),12) x  FROM (SELECT ST_DumpPoints(ST_Extent(geom))  AS pt from public.california20191107) as foo where (pt).path[2] in (1,3)
--网格表
CREATE TABLE public.ca_xyz  ( x integer,y integer,z integer,id integer NOT NULL )  WITH (OIDS = FALSE)    TABLESPACE pg_default;
--示例
insert into public.ca_xyz (id,z,x,y) select 1,3,1,3 from public.california20191107  where TileBBox(3,1,3,4326)&&geom limit 1 


--示例
SELECT ST_AsMVT(vt,'polygon',4096,'geo') tile  FROM (SELECT ST_AsMVTGeom(geom,Box2D(TileBBox(3,1,3,4326)),4096,0,true) AS geo FROM public.california20191107   where TileBBox(3,1,3,4326)&&geom) AS  vt

SELECT
   lat2tile ( ST_Y ( ( pt ).geom ), 12 ) y,
   lon2tile ( ST_X ( ( pt ).geom ), 12 ) x 
FROM
   ( SELECT ST_DumpPoints ( ST_Extent ( geo ) ) AS pt FROM PUBLIC.tb_szall ) AS foo 
WHERE
   ( pt ).PATH [ 2 ] IN ( 1, 3 )

--select *  FROM (SELECT ST_DumpPoints(st_astext(ST_Extent(geo)))  AS pt from public.tb_szall) as foo where (pt).path[2] in (1,3)
SELECT
   lat2tile ( ST_Y ( ( pt ).geom ), 8 ) y,
   lon2tile ( ST_X ( ( pt ).geom ), 8 ) x 
FROM
   ( SELECT ST_DumpPoints ( st_extent ( geo ) ) AS pt FROM tb_szall ) AS foo
where (pt).PATH[2] in (1,3)

--创建格网表
drop  TABLE if EXISTS  public.grid;
CREATE TABLE public.grid  ( x integer,y integer,z integer,id serial PRIMARY key )


insert into public.grid (id,z,x,y) select 1,3,1,3 from public.california20191107  where ST_Contains(TileBBox(3,1,3,4326)&&geom limit 1 


SELECT count(*) from tb_szall where ST_Contains(tilebbox(5,26,13,4326),geo)


--示例
   SELECT
   	ST_AsMVT ( vt, 'polygon', 4326, 'geo' ) tile 
   FROM
   	(
   	SELECT
   		 ST_AsMVTGeom ( geo, Box2D ( TileBBox ( 5, 26, 13, 4326 ) ), 4326, 0, TRUE ) AS geo 
   	FROM
   		PUBLIC.tb_szall 
   	WHERE
   		ST_Contains(tilebbox(5,26,13,4326),geo)
   	) AS vt

使用Redis缓存

1. 开发java后端服务来处理瓦片访问请求，加入redis缓存，发现，大部分时间无法去命中缓存。
   所以后面在系统开发后，必须要提前去生成缓存。在提前生成缓存的问题上，需要确定以下几个点：

• 预先生成哪些级别的缓存 ？
• 缓存参数 x,y,z的区间范围如何确定 可以确定

暂时生成6-13级别的缓存

1. 对于高缩放级别的数据，某个空间范围其实有一个最大缩放级别，超过这个级别之后，返回的其实是所有的要素。所以在请求时，可以将这个级别之上的所有请求都返回一个。

• 求出分界线
• 对数据进行抽稀或者聚集

1. 在高层缩放级别，以热力图的方式进行显示，在低层次级别以要素的方式展示 热力图后端sql

SELECT WIDTH_BUCKET ( st_x ( geo ), 112.49999998434, 123.749999982774,200) grid_x,
   WIDTH_BUCKET ( st_y ( geo ), 21.943045530533, 31.9521622340387,200) grid_y,
   COUNT ( * ),
   st_centroid ( st_collect ( geo ) ) geom
   --ARRAY_AGG ( id ) gids 
FROM
   tb_szall 
WHERE
   st_x ( geo ) BETWEEN 112.49999998434 
   AND 123.749999982774
   AND st_y ( geo ) BETWEEN 21.943045530533 
   AND 31.9521622340387
GROUP BY
   grid_x,
   grid_y

创建空间索引

CREATE INDEX tb_insar_geo_idx ON tb_insar USING GIST ( geom );