Massive data display (2) - Performance Optimization

background

The massive data display done before                               .

Get ready

     software environment: PostGIS(3.0.0rc2 r17909) and PostgreSQL (12.0, compiled by visual c + + build 1914, 64 bit), the data is part of the open-source housing data of Microsoft public.california 20191107 (10988317 pieces).

Preprocessing

                      . First, get the maximum and minimum xyz of data level 12, generate grid through this range, and then get the one-to-one corresponding grid table public.ca_xyz by intersecting with data.

--Longitude slicing 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;
--Latitudinal transect 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 Rotational geometry
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$;
--Calculation of maximum and minimum longitude and latitude of data 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)
--Grid table
CREATE TABLE public.ca_xyz  ( x integer,y integer,z integer,id integer NOT NULL )  WITH (OIDS = FALSE)    TABLESPACE pg_default;
--Example
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



                         .

--Example
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

Back-up services

                              . When the data request is more than 12 levels, we use database query to obtain vector slices. For data larger than level 12 but not cached, it will determine whether it is a child of level 12 xyz. If it is, it will query the database and cache it in the program. If it is not, it will return invalid url to reduce the database query time.


Tags: Go Database SQL PostgreSQL

Posted on Sun, 10 Nov 2019 09:24:33 -0800 by sws