# 海量数据展示(二）-性能优化

### 预处理

--经度转切片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

image

image

--示例
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

image

image

image