PostGIS实现（大数据量，大图斑）图层间相交工具

书接上文《使用PostGIS求两个要素类的相交部分》 https://qlygmwcx.blog.csdn.net/article/details/119415622

文中介绍了两个要素类，求取两个要素类的相交部分，其中介绍了索引以及通过执行计划对sql进行调优。同时也介绍了一种特殊情况：当行政区与要素类数据叠加分析。

今天(写完估计也是2022年的元宵之后才会发布了，要给自己充充电)我们就来介绍一下这种特殊情况的计算。

1. 情况说明

现有两个要素类XZQ（行政区）和JBNT（基本农田）数据需要获取出JBNT与XZQ相交的部分并且获取其属性值XZQ数据为11条，JBNT数据大约为25万条

按照上文《使用PostGIS求两个要素类的相交部分》中增加索引之后执行，笔者的电脑执行下述sql，经过一晚上的运行依旧无法得到结果

explain analyze 
create table ana_test as
with 
a as (select * from  xzqxs_temp ),
b as (select * from  yjjbntcbq_temp ),
resultTable as (select ST_Multi(st_intersection(ST_Makevalid(a.wkb_geometry),ST_Makevalid(b.wkb_geometry))) as wkb_geometry from a,b where ST_Intersects(a.wkb_geometry,b.wkb_geometry))
select *,ST_Area(resultTable.wkb_geometry) as inter_area
from resultTable where ST_Area(wkb_geometry)>0

2. 问题分析

经过总结得到两个原因

XZQ数据单个要素类太大

要素范围太大，导致经过空间索引筛选效果下降，筛选要素多，导致后续相交计算多要素节点多，图斑较为复杂，而JBNT图斑较小，复杂且大图版与JBNT图斑计算时有很多无效计算，所以计算效率慢 3. 解决方法

我们将XZQ数据做一个拆分，将XZQ数据拆分，从而使问题2的两个原因产生的执行时间下降；由于前面做了拆分，所以我们需要将结果通过XZQ的FID和JBNT的FID分组并进行几何图形合并。 4. 拆分方式 4.1. 使用泰森多边形Voronoi图拆分

按照遥想公瑾当年大佬 https://www.jianshu.com/p/94bfbc374d82 中提到的我们通过泰森多边形Voronoi图的方式进行拆分。

create or replace function freegis_polygon_split(
    in split_geom geometry(MultiPolygon),--输入的面
    in split_num int,--分割的数量
    out geom geometry(Polygon)--输出切割的面
) returns setof geometry as $$ 
declare  
    rec record; 
begin
    --使用原来的面，切割voronoi算法生成的面
    for rec in SELECT ST_Intersection(split_geom, t.geom) AS geom FROM (
        --voronoi算法生成面
        SELECt (ST_Dump(ST_VoronoiPolygons(ST_collect(voronoi.geom)))).geom AS geom FROM (
            --簇的均值中心
            SELECt clusters.cluster, ST_Centroid(ST_collect(clusters.geom)) AS geom FROM (
                --点ST_ClusterKMeans聚合成簇
                SELECt points.geom, ST_ClusterKMeans(points.geom, split_num) over () AS cluster from (
                    --面内生成点
                    SELECt row_number() over() as gid,(ST_Dump(ST_GeneratePoints(split_geom, 2000))).geom
                ) points
            ) clusters GROUP BY clusters.cluster
        ) voronoi
    ) t loop
        geom:=rec.geom;
        return next;
    end loop;
    return;
end;  
$$ language plpgsql strict; 

insert into subdivide_t1(geom) select ST_Multi(freegis_polygon_split(a.wkb_geometry,10)) as geom from xzqxs a

4.2. 使用ST_SubDivide函数拆分

每50个节点组成一个新要素

create table subdivide_t50
(
	gid serial primary key,
    geom geometry(MultiPolygon,4528)
);

INSERT INTO subdivide_t50(geom) select ST_Multi(ST_SubDivide(wkb_geometry,50)) geom from xzqxs

CREATE INDEX spatial_subdivide_t50_geom_idx
ON subdivide_t50
USING GIST (geom);

每200个节点组成一个新要素

create table subdivide_t200
(
	gid serial primary key,
    geom geometry(MultiPolygon,4528)
);

INSERT INTO subdivide_t200(geom) select ST_Multi(ST_SubDivide(wkb_geometry,200)) geom from xzqxs

CREATE INDEX spatial_subdivide_t200_geom_idx
ON subdivide_t200
USING GIST (geom);

5. 叠加效果 5.1. 使用泰森多边形Voronoi图叠加

create table ana_voronoi
(
	gid serial primary key,
    geom geometry(MultiPolygon,4528)
);

explain analyze 
insert into ana_voronoi(geom) 
with 
a as (select geom as wkb_geometry  from  subdivide_t1 ),
b as (select * from  yjjnbt ),
resultTable as (select st_intersection(a.wkb_geometry,b.wkb_geometry) as wkb_geometry from a,b where ST_Intersects(a.wkb_geometry,b.wkb_geometry))
select st_multi(ST_CollectionExtract(wkb_geometry,3)) as geom
from resultTable where ST_Area(wkb_geometry)>0 and ST_IsValid(wkb_geometry)

5.2. 使用ST_SubDivide结果叠加

50个节点要素叠加

create table ana_subdivide_t50
(
	gid serial primary key,
    geom geometry(MultiPolygon,4528)
);

explain analyze 
insert into ana_subdivide_t50(geom) 
with 
a as (select geom as wkb_geometry  from  subdivide_t50 ),
b as (select * from  yjjnbt ),
resultTable as (select st_intersection(a.wkb_geometry,b.wkb_geometry) as wkb_geometry from a,b where ST_Intersects(a.wkb_geometry,b.wkb_geometry))
select st_multi(ST_CollectionExtract(wkb_geometry,3)) as geom
from resultTable where ST_Area(wkb_geometry)>0 and ST_IsValid(wkb_geometry)

200个节点要素叠加

create table ana_subdivide_t200
(
	gid serial primary key,
    geom geometry(MultiPolygon,4528)
);

explain analyze 
insert into ana_subdivide_t200(geom) 
with 
a as (select geom as wkb_geometry  from  subdivide_t200 ),
b as (select * from  yjjnbt ),
resultTable as (select st_intersection(a.wkb_geometry,b.wkb_geometry) as wkb_geometry from a,b where ST_Intersects(a.wkb_geometry,b.wkb_geometry))
select st_multi(ST_CollectionExtract(wkb_geometry,3)) as geom
from resultTable where ST_Area(wkb_geometry)>0 and ST_IsValid(wkb_geometry)

500个节点要素叠加

create table subdivide_t500
(
	gid serial primary key,
    geom geometry(MultiPolygon,4528)
);

INSERT INTO subdivide_t500(geom) select ST_Multi(ST_SubDivide(wkb_geometry,500)) geom from xzqxs

CREATE INDEX spatial_subdivide_t500_geom_idx
ON subdivide_t500
USING GIST (geom);

create table ana_subdivide_t500
(
	gid serial primary key,
    geom geometry(MultiPolygon,4528)
);

explain analyze 
insert into ana_subdivide_t500(geom) 
with 
a as (select geom as wkb_geometry  from  subdivide_t500 ),
b as (select * from  yjjnbt ),
resultTable as (select st_intersection(a.wkb_geometry,b.wkb_geometry) as wkb_geometry from a,b where ST_Intersects(a.wkb_geometry,b.wkb_geometry))
select st_multi(ST_CollectionExtract(wkb_geometry,3)) as geom
from resultTable where ST_Area(wkb_geometry)>0 and ST_IsValid(wkb_geometry)

1000个节点要素叠加

create table subdivide_t1000
(
	gid serial primary key,
    geom geometry(MultiPolygon,4528)
);

INSERT INTO subdivide_t1000(geom) select ST_Multi(ST_SubDivide(wkb_geometry,1000)) geom from xzqxs

CREATE INDEX spatial_subdivide_t1000_geom_idx
ON subdivide_t1000
USING GIST (geom);

create table ana_subdivide_t1000
(
	gid serial primary key,
    geom geometry(MultiPolygon,4528)
);

explain analyze 
insert into ana_subdivide_t1000(geom) 
with 
a as (select geom as wkb_geometry  from  subdivide_t1000 ),
b as (select * from  yjjnbt ),
resultTable as (select st_intersection(a.wkb_geometry,b.wkb_geometry) as wkb_geometry from a,b where ST_Intersects(a.wkb_geometry,b.wkb_geometry))
select st_multi(ST_CollectionExtract(wkb_geometry,3)) as geom
from resultTable where ST_Area(wkb_geometry)>0 and ST_IsValid(wkb_geometry)

6. 整体解决方案 6.1. 泰森多边形Voronoi图整体解决方案

create table xzqxs_voronoi as select * from xzqxs where 1=2;

insert into xzqxs_voronoi(ogc_fid,bsm,ysdm,xzqdm,xzqmc,jsmj,bz,wkb_geometry) select ogc_fid,bsm,ysdm,xzqdm,xzqmc,jsmj,bz,ST_Multi(freegis_polygon_split(a.wkb_geometry,10)) as wkb_geometry from xzqxs a;

CREATE INDEX spatial_xzqxs_voronoi_geom_idx
ON xzqxs_voronoi
USING GIST (wkb_geometry);

CREATE TABLE voronoi_xzqxs_inter_jbntbt_process (
	fid serial PRIMARY KEY,
	wkb_geometry geometry ( MultiPolygon, 4528 ),
	xzqxs_fid int4,
	bsm VARCHAR ( 18 ),
	ysdm VARCHAR ( 10 ),
	xzqdm VARCHAR ( 12 ),
	xzqmc VARCHAR ( 100 ),
	jsmj float4,
	bz VARCHAR ( 255 ),
	jbntbt_fid int4,
	bsm_1 VARCHAR ( 18 ),
	ysdm_1 VARCHAR ( 10 ),
	xzqmc_1 VARCHAR ( 100 ),
	yjjbnttbbh VARCHAR ( 20 ),
	tbbh VARCHAR ( 8 ),
	dlbm VARCHAR ( 4 ),
	dlmc VARCHAR ( 60 ),
	qsxz VARCHAR ( 2 ),
	qsdwdm VARCHAR ( 19 ),
	qsdwmc VARCHAR ( 60 ),
	zldwdm VARCHAR ( 19 ),
	zldwmc VARCHAR ( 60 ),
	yjjbnttbmj float4,
	kcdlbm VARCHAR ( 4 ),
	tkxs float4,
	tkmj float4,
	yjjbntmj float4,
	gdlx VARCHAR ( 2 ),
	yjjbntlx VARCHAR ( 1 ),
	gdpdjb VARCHAR ( 2 ),
	tbxhdm VARCHAR ( 4 ),
	tbxhmc VARCHAR ( 20 ),
	gdzzsxdm VARCHAR ( 4 ),
	gdzzsxmc VARCHAR ( 10 ),
	gddb int4,
	gddj int4,
	frdbs VARCHAR ( 1 ),
	sjnf int4,
	bz_1 VARCHAR ( 255 )
);


explain analyze 
insert into voronoi_xzqxs_inter_jbntbt_process(wkb_geometry,xzqxs_fid ,bsm ,ysdm ,xzqdm ,xzqmc ,jsmj ,bz ,jbntbt_fid ,bsm_1 ,ysdm_1 ,xzqmc_1 ,yjjbnttbbh ,tbbh ,dlbm ,dlmc ,qsxz ,qsdwdm,qsdwmc,zldwdm,zldwmc,yjjbnttbmj ,kcdlbm,tkxs ,tkmj ,yjjbntmj ,gdlx ,yjjbntlx ,gdpdjb,tbxhdm ,tbxhmc ,gdzzsxdm ,gdzzsxmc ,gddb,gddj,frdbs ,sjnf ,bz_1)
with 
a as (select * from xzqxs_voronoi),
b as (select * from yjjnbt),
resultTable as (select st_multi(ST_CollectionExtract(st_intersection(a.wkb_geometry,b.wkb_geometry),3)) as wkb_geometry ,
a.ogc_fid as xzqxs_fid, a.bsm,a.ysdm,a.xzqdm,a.xzqmc,a.jsmj,a.bz,
b.ogc_fid as jbntbt_fid,b.bsm as bsm_1,b.ysdm as ysdm_1,b.xzqmc as xzqmc_1,b.yjjbnttbbh,b.tbbh,b.dlbm,b.dlmc,b.qsxz,b.qsdwdm,b.qsdwmc,b.zldwdm,b.zldwmc,b.yjjbnttbmj,b.kcdlbm,b.tkxs,b.tkmj,b.yjjbntmj,b.gdlx,b.yjjbntlx,b.gdpdjb,b.tbxhdm,b.tbxhmc,b.gdzzsxdm,b.gdzzsxmc,b.gddb,b.gddj,b.frdbs,b.sjnf,b.bz as bz_1
from a,b where ST_Intersects(a.wkb_geometry,b.wkb_geometry))
select * from resultTable where ST_Area(wkb_geometry)>0 and ST_IsValid(wkb_geometry);


CREATE TABLE voronoi_xzqxs_inter_jbntbt (
	fid serial PRIMARY KEY,
	wkb_geometry geometry ( MultiPolygon, 4528 ),
	xzqxs_fid int4,
	bsm VARCHAR ( 18 ),
	ysdm VARCHAR ( 10 ),
	xzqdm VARCHAR ( 12 ),
	xzqmc VARCHAR ( 100 ),
	jsmj float4,
	bz VARCHAR ( 255 ),
	jbntbt_fid int4,
	bsm_1 VARCHAR ( 18 ),
	ysdm_1 VARCHAR ( 10 ),
	xzqmc_1 VARCHAR ( 100 ),
	yjjbnttbbh VARCHAR ( 20 ),
	tbbh VARCHAR ( 8 ),
	dlbm VARCHAR ( 4 ),
	dlmc VARCHAR ( 60 ),
	qsxz VARCHAR ( 2 ),
	qsdwdm VARCHAR ( 19 ),
	qsdwmc VARCHAR ( 60 ),
	zldwdm VARCHAR ( 19 ),
	zldwmc VARCHAR ( 60 ),
	yjjbnttbmj float4,
	kcdlbm VARCHAR ( 4 ),
	tkxs float4,
	tkmj float4,
	yjjbntmj float4,
	gdlx VARCHAR ( 2 ),
	yjjbntlx VARCHAR ( 1 ),
	gdpdjb VARCHAR ( 2 ),
	tbxhdm VARCHAR ( 4 ),
	tbxhmc VARCHAR ( 20 ),
	gdzzsxdm VARCHAR ( 4 ),
	gdzzsxmc VARCHAR ( 10 ),
	gddb int4,
	gddj int4,
	frdbs VARCHAR ( 1 ),
	sjnf int4,
	bz_1 VARCHAR ( 255 )
);

insert into voronoi_xzqxs_inter_jbntbt(wkb_geometry,xzqxs_fid ,bsm ,ysdm ,xzqdm ,xzqmc ,jsmj ,bz ,jbntbt_fid ,bsm_1 ,ysdm_1 ,xzqmc_1 ,yjjbnttbbh ,tbbh ,dlbm ,dlmc ,qsxz ,qsdwdm,qsdwmc,zldwdm,zldwmc,yjjbnttbmj ,kcdlbm,tkxs ,tkmj ,yjjbntmj ,gdlx ,yjjbntlx ,gdpdjb,tbxhdm ,tbxhmc ,gdzzsxdm ,gdzzsxmc ,gddb,gddj,frdbs ,sjnf ,bz_1) select st_multi(st_union(wkb_geometry)) as wkb_geometry,min(xzqxs_fid) as xzqxs_fid,min(bsm ) as bsm,min(ysdm ) as ysdm,min(xzqdm ) as xzqdm,min(xzqmc ) as xzqmc,min(jsmj ) as jsmj,min(bz ) as bz,min(jbntbt_fid ) as jbntbt_fid,min(bsm_1 ) as bsm_1,min(ysdm_1 ) as ysdm_1,min(xzqmc_1 ) as xzqmc_1,min(yjjbnttbbh ) as yjjbnttbbh,min(tbbh ) as tbbh,min(dlbm ) as dlbm,min(dlmc ) as dlmc,min(qsxz ) as qsxz,min(qsdwdm) as qsdwdm,min(qsdwmc) as qsdwmc,min(zldwdm) as zldwdm,min(zldwmc) as zldwmc,min(yjjbnttbmj ) as yjjbnttbmj,min(kcdlbm) as kcdlbm,min(tkxs ) as tkxs,min(tkmj ) as tkmj,min(yjjbntmj ) as yjjbntmj,min(gdlx ) as gdlx,min(yjjbntlx ) as yjjbntlx,min(gdpdjb) as gdpdjb,min(tbxhdm ) as tbxhdm,min(tbxhmc ) as tbxhmc,min(gdzzsxdm ) as gdzzsxdm,min(gdzzsxmc ) as gdzzsxmc, min(gddb) as gddb,min(gddj) as gddj,min(frdbs ) as frdbs,min(sjnf ) as sjnf,min(bz_1) as bz_1 from voronoi_xzqxs_inter_jbntbt_process group by xzqxs_fid,jbntbt_fid;

执行结果

create table xzqxs_voronoi as select * from xzqxs where 1=2
> OK
> 时间: 0.007s


insert into xzqxs_voronoi(ogc_fid,bsm,ysdm,xzqdm,xzqmc,jsmj,bz,wkb_geometry) select ogc_fid,bsm,ysdm,xzqdm,xzqmc,jsmj,bz,ST_Multi(freegis_polygon_split(a.wkb_geometry,10)) as wkb_geometry from xzqxs a
> Affected rows: 100
> 时间: 1.023s


CREATE INDEX spatial_xzqxs_voronoi_geom_idx
ON xzqxs_voronoi
USING GIST (wkb_geometry)
> OK
> 时间: 0.005s


CREATE TABLE voronoi_xzqxs_inter_jbntbt_process (
	fid serial PRIMARY KEY,
	wkb_geometry geometry ( MultiPolygon, 4528 ),
	xzqxs_fid int4,
	bsm VARCHAR ( 18 ),
	ysdm VARCHAR ( 10 ),
	xzqdm VARCHAR ( 12 ),
	xzqmc VARCHAR ( 100 ),
	jsmj float4,
	bz VARCHAR ( 255 ),
	jbntbt_fid int4,
	bsm_1 VARCHAR ( 18 ),
	ysdm_1 VARCHAR ( 10 ),
	xzqmc_1 VARCHAR ( 100 ),
	yjjbnttbbh VARCHAR ( 20 ),
	tbbh VARCHAR ( 8 ),
	dlbm VARCHAR ( 4 ),
	dlmc VARCHAR ( 60 ),
	qsxz VARCHAR ( 2 ),
	qsdwdm VARCHAR ( 19 ),
	qsdwmc VARCHAR ( 60 ),
	zldwdm VARCHAR ( 19 ),
	zldwmc VARCHAR ( 60 ),
	yjjbnttbmj float4,
	kcdlbm VARCHAR ( 4 ),
	tkxs float4,
	tkmj float4,
	yjjbntmj float4,
	gdlx VARCHAR ( 2 ),
	yjjbntlx VARCHAR ( 1 ),
	gdpdjb VARCHAR ( 2 ),
	tbxhdm VARCHAR ( 4 ),
	tbxhmc VARCHAR ( 20 ),
	gdzzsxdm VARCHAR ( 4 ),
	gdzzsxmc VARCHAR ( 10 ),
	gddb int4,
	gddj int4,
	frdbs VARCHAR ( 1 ),
	sjnf int4,
	bz_1 VARCHAR ( 255 )
)
> OK
> 时间: 0.011s


explain analyze 
insert into voronoi_xzqxs_inter_jbntbt_process(wkb_geometry,xzqxs_fid ,bsm ,ysdm ,xzqdm ,xzqmc ,jsmj ,bz ,jbntbt_fid ,bsm_1 ,ysdm_1 ,xzqmc_1 ,yjjbnttbbh ,tbbh ,dlbm ,dlmc ,qsxz ,qsdwdm,qsdwmc,zldwdm,zldwmc,yjjbnttbmj ,kcdlbm,tkxs ,tkmj ,yjjbntmj ,gdlx ,yjjbntlx ,gdpdjb,tbxhdm ,tbxhmc ,gdzzsxdm ,gdzzsxmc ,gddb,gddj,frdbs ,sjnf ,bz_1)
with 
a as (select * from xzqxs_voronoi),
b as (select * from yjjnbt),
resultTable as (select st_multi(ST_CollectionExtract(st_intersection(a.wkb_geometry,b.wkb_geometry),3)) as wkb_geometry ,
a.ogc_fid as xzqxs_fid, a.bsm,a.ysdm,a.xzqdm,a.xzqmc,a.jsmj,a.bz,
b.ogc_fid as jbntbt_fid,b.bsm as bsm_1,b.ysdm as ysdm_1,b.xzqmc as xzqmc_1,b.yjjbnttbbh,b.tbbh,b.dlbm,b.dlmc,b.qsxz,b.qsdwdm,b.qsdwmc,b.zldwdm,b.zldwmc,b.yjjbnttbmj,b.kcdlbm,b.tkxs,b.tkmj,b.yjjbntmj,b.gdlx,b.yjjbntlx,b.gdpdjb,b.tbxhdm,b.tbxhmc,b.gdzzsxdm,b.gdzzsxmc,b.gddb,b.gddj,b.frdbs,b.sjnf,b.bz as bz_1
from a,b where ST_Intersects(a.wkb_geometry,b.wkb_geometry))
select * from resultTable where ST_Area(wkb_geometry)>0 and ST_IsValid(wkb_geometry)
> Affected rows: 0
> 时间: 472.21s


CREATE TABLE voronoi_xzqxs_inter_jbntbt (
	fid serial PRIMARY KEY,
	wkb_geometry geometry ( MultiPolygon, 4528 ),
	xzqxs_fid int4,
	bsm VARCHAR ( 18 ),
	ysdm VARCHAR ( 10 ),
	xzqdm VARCHAR ( 12 ),
	xzqmc VARCHAR ( 100 ),
	jsmj float4,
	bz VARCHAR ( 255 ),
	jbntbt_fid int4,
	bsm_1 VARCHAR ( 18 ),
	ysdm_1 VARCHAR ( 10 ),
	xzqmc_1 VARCHAR ( 100 ),
	yjjbnttbbh VARCHAR ( 20 ),
	tbbh VARCHAR ( 8 ),
	dlbm VARCHAR ( 4 ),
	dlmc VARCHAR ( 60 ),
	qsxz VARCHAR ( 2 ),
	qsdwdm VARCHAR ( 19 ),
	qsdwmc VARCHAR ( 60 ),
	zldwdm VARCHAR ( 19 ),
	zldwmc VARCHAR ( 60 ),
	yjjbnttbmj float4,
	kcdlbm VARCHAR ( 4 ),
	tkxs float4,
	tkmj float4,
	yjjbntmj float4,
	gdlx VARCHAR ( 2 ),
	yjjbntlx VARCHAR ( 1 ),
	gdpdjb VARCHAR ( 2 ),
	tbxhdm VARCHAR ( 4 ),
	tbxhmc VARCHAR ( 20 ),
	gdzzsxdm VARCHAR ( 4 ),
	gdzzsxmc VARCHAR ( 10 ),
	gddb int4,
	gddj int4,
	frdbs VARCHAR ( 1 ),
	sjnf int4,
	bz_1 VARCHAR ( 255 )
)
> OK
> 时间: 0.01s

insert into voronoi_xzqxs_inter_jbntbt(wkb_geometry,xzqxs_fid ,bsm ,ysdm ,xzqdm ,xzqmc ,jsmj ,bz ,jbntbt_fid ,bsm_1 ,ysdm_1 ,xzqmc_1 ,yjjbnttbbh ,tbbh ,dlbm ,dlmc ,qsxz ,qsdwdm,qsdwmc,zldwdm,zldwmc,yjjbnttbmj ,kcdlbm,tkxs ,tkmj ,yjjbntmj ,gdlx ,yjjbntlx ,gdpdjb,tbxhdm ,tbxhmc ,gdzzsxdm ,gdzzsxmc ,gddb,gddj,frdbs ,sjnf ,bz_1) select st_multi(st_union(wkb_geometry)) as wkb_geometry,min(xzqxs_fid) as xzqxs_fid,min(bsm ) as bsm,min(ysdm ) as ysdm,min(xzqdm ) as xzqdm,min(xzqmc ) as xzqmc,min(jsmj ) as jsmj,min(bz ) as bz,min(jbntbt_fid ) as jbntbt_fid,min(bsm_1 ) as bsm_1,min(ysdm_1 ) as ysdm_1,min(xzqmc_1 ) as xzqmc_1,min(yjjbnttbbh ) as yjjbnttbbh,min(tbbh ) as tbbh,min(dlbm ) as dlbm,min(dlmc ) as dlmc,min(qsxz ) as qsxz,min(qsdwdm) as qsdwdm,min(qsdwmc) as qsdwmc,min(zldwdm) as zldwdm,min(zldwmc) as zldwmc,min(yjjbnttbmj ) as yjjbnttbmj,min(kcdlbm) as kcdlbm,min(tkxs ) as tkxs,min(tkmj ) as tkmj,min(yjjbntmj ) as yjjbntmj,min(gdlx ) as gdlx,min(yjjbntlx ) as yjjbntlx,min(gdpdjb) as gdpdjb,min(tbxhdm ) as tbxhdm,min(tbxhmc ) as tbxhmc,min(gdzzsxdm ) as gdzzsxdm,min(gdzzsxmc ) as gdzzsxmc, min(gddb) as gddb,min(gddj) as gddj,min(frdbs ) as frdbs,min(sjnf ) as sjnf,min(bz_1) as bz_1 from voronoi_xzqxs_inter_jbntbt_process group by xzqxs_fid,jbntbt_fid
> Affected rows: 250165
> 时间: 14.117s

6.2 ST_SubDivide 整体解决方案 6.2.1. 50个节点拆分

create table xzqxs_subdivide_n50 as select * from xzqxs where 1=2;

INSERT INTO xzqxs_subdivide_n50(ogc_fid,bsm,ysdm,xzqdm,xzqmc,jsmj,bz,wkb_geometry) select ogc_fid,bsm,ysdm,xzqdm,xzqmc,jsmj,bz,ST_Multi(ST_SubDivide(wkb_geometry,50)) wkb_geometry from xzqxs;

CREATE INDEX spatial_xzqxs_subdivide_n50_geom_idx
ON xzqxs_subdivide_n50
USING GIST (wkb_geometry);


CREATE TABLE subdivide_n50_xzqxs_inter_jbntbt_process (
	fid serial PRIMARY KEY,
	wkb_geometry geometry ( MultiPolygon, 4528 ),
	xzqxs_fid int4,
	bsm VARCHAR ( 18 ),
	ysdm VARCHAR ( 10 ),
	xzqdm VARCHAR ( 12 ),
	xzqmc VARCHAR ( 100 ),
	jsmj float4,
	bz VARCHAR ( 255 ),
	jbntbt_fid int4,
	bsm_1 VARCHAR ( 18 ),
	ysdm_1 VARCHAR ( 10 ),
	xzqmc_1 VARCHAR ( 100 ),
	yjjbnttbbh VARCHAR ( 20 ),
	tbbh VARCHAR ( 8 ),
	dlbm VARCHAR ( 4 ),
	dlmc VARCHAR ( 60 ),
	qsxz VARCHAR ( 2 ),
	qsdwdm VARCHAR ( 19 ),
	qsdwmc VARCHAR ( 60 ),
	zldwdm VARCHAR ( 19 ),
	zldwmc VARCHAR ( 60 ),
	yjjbnttbmj float4,
	kcdlbm VARCHAR ( 4 ),
	tkxs float4,
	tkmj float4,
	yjjbntmj float4,
	gdlx VARCHAR ( 2 ),
	yjjbntlx VARCHAR ( 1 ),
	gdpdjb VARCHAR ( 2 ),
	tbxhdm VARCHAR ( 4 ),
	tbxhmc VARCHAR ( 20 ),
	gdzzsxdm VARCHAR ( 4 ),
	gdzzsxmc VARCHAR ( 10 ),
	gddb int4,
	gddj int4,
	frdbs VARCHAR ( 1 ),
	sjnf int4,
	bz_1 VARCHAR ( 255 )
);


explain analyze 
insert into subdivide_n50_xzqxs_inter_jbntbt_process(wkb_geometry,xzqxs_fid ,bsm ,ysdm ,xzqdm ,xzqmc ,jsmj ,bz ,jbntbt_fid ,bsm_1 ,ysdm_1 ,xzqmc_1 ,yjjbnttbbh ,tbbh ,dlbm ,dlmc ,qsxz ,qsdwdm,qsdwmc,zldwdm,zldwmc,yjjbnttbmj ,kcdlbm,tkxs ,tkmj ,yjjbntmj ,gdlx ,yjjbntlx ,gdpdjb,tbxhdm ,tbxhmc ,gdzzsxdm ,gdzzsxmc ,gddb,gddj,frdbs ,sjnf ,bz_1)
with 
a as (select * from xzqxs_subdivide_n50),
b as (select * from yjjnbt),
resultTable as (select st_multi(ST_CollectionExtract(st_intersection(a.wkb_geometry,b.wkb_geometry),3)) as wkb_geometry ,
a.ogc_fid as xzqxs_fid, a.bsm,a.ysdm,a.xzqdm,a.xzqmc,a.jsmj,a.bz,
b.ogc_fid as jbntbt_fid,b.bsm as bsm_1,b.ysdm as ysdm_1,b.xzqmc as xzqmc_1,b.yjjbnttbbh,b.tbbh,b.dlbm,b.dlmc,b.qsxz,b.qsdwdm,b.qsdwmc,b.zldwdm,b.zldwmc,b.yjjbnttbmj,b.kcdlbm,b.tkxs,b.tkmj,b.yjjbntmj,b.gdlx,b.yjjbntlx,b.gdpdjb,b.tbxhdm,b.tbxhmc,b.gdzzsxdm,b.gdzzsxmc,b.gddb,b.gddj,b.frdbs,b.sjnf,b.bz as bz_1
from a,b where ST_Intersects(a.wkb_geometry,b.wkb_geometry))
select * from resultTable where ST_Area(wkb_geometry)>0 and ST_IsValid(wkb_geometry);


CREATE TABLE subdivide_n50_xzqxs_inter_jbntbt (
	fid serial PRIMARY KEY,
	wkb_geometry geometry ( MultiPolygon, 4528 ),
	xzqxs_fid int4,
	bsm VARCHAR ( 18 ),
	ysdm VARCHAR ( 10 ),
	xzqdm VARCHAR ( 12 ),
	xzqmc VARCHAR ( 100 ),
	jsmj float4,
	bz VARCHAR ( 255 ),
	jbntbt_fid int4,
	bsm_1 VARCHAR ( 18 ),
	ysdm_1 VARCHAR ( 10 ),
	xzqmc_1 VARCHAR ( 100 ),
	yjjbnttbbh VARCHAR ( 20 ),
	tbbh VARCHAR ( 8 ),
	dlbm VARCHAR ( 4 ),
	dlmc VARCHAR ( 60 ),
	qsxz VARCHAR ( 2 ),
	qsdwdm VARCHAR ( 19 ),
	qsdwmc VARCHAR ( 60 ),
	zldwdm VARCHAR ( 19 ),
	zldwmc VARCHAR ( 60 ),
	yjjbnttbmj float4,
	kcdlbm VARCHAR ( 4 ),
	tkxs float4,
	tkmj float4,
	yjjbntmj float4,
	gdlx VARCHAR ( 2 ),
	yjjbntlx VARCHAR ( 1 ),
	gdpdjb VARCHAR ( 2 ),
	tbxhdm VARCHAR ( 4 ),
	tbxhmc VARCHAR ( 20 ),
	gdzzsxdm VARCHAR ( 4 ),
	gdzzsxmc VARCHAR ( 10 ),
	gddb int4,
	gddj int4,
	frdbs VARCHAR ( 1 ),
	sjnf int4,
	bz_1 VARCHAR ( 255 )
);

insert into subdivide_n50_xzqxs_inter_jbntbt(wkb_geometry,xzqxs_fid ,bsm ,ysdm ,xzqdm ,xzqmc ,jsmj ,bz ,jbntbt_fid ,bsm_1 ,ysdm_1 ,xzqmc_1 ,yjjbnttbbh ,tbbh ,dlbm ,dlmc ,qsxz ,qsdwdm,qsdwmc,zldwdm,zldwmc,yjjbnttbmj ,kcdlbm,tkxs ,tkmj ,yjjbntmj ,gdlx ,yjjbntlx ,gdpdjb,tbxhdm ,tbxhmc ,gdzzsxdm ,gdzzsxmc ,gddb,gddj,frdbs ,sjnf ,bz_1) select st_multi(st_union(wkb_geometry)) as wkb_geometry,min(xzqxs_fid) as xzqxs_fid,min(bsm ) as bsm,min(ysdm ) as ysdm,min(xzqdm ) as xzqdm,min(xzqmc ) as xzqmc,min(jsmj ) as jsmj,min(bz ) as bz,min(jbntbt_fid ) as jbntbt_fid,min(bsm_1 ) as bsm_1,min(ysdm_1 ) as ysdm_1,min(xzqmc_1 ) as xzqmc_1,min(yjjbnttbbh ) as yjjbnttbbh,min(tbbh ) as tbbh,min(dlbm ) as dlbm,min(dlmc ) as dlmc,min(qsxz ) as qsxz,min(qsdwdm) as qsdwdm,min(qsdwmc) as qsdwmc,min(zldwdm) as zldwdm,min(zldwmc) as zldwmc,min(yjjbnttbmj ) as yjjbnttbmj,min(kcdlbm) as kcdlbm,min(tkxs ) as tkxs,min(tkmj ) as tkmj,min(yjjbntmj ) as yjjbntmj,min(gdlx ) as gdlx,min(yjjbntlx ) as yjjbntlx,min(gdpdjb) as gdpdjb,min(tbxhdm ) as tbxhdm,min(tbxhmc ) as tbxhmc,min(gdzzsxdm ) as gdzzsxdm,min(gdzzsxmc ) as gdzzsxmc, min(gddb) as gddb,min(gddj) as gddj,min(frdbs ) as frdbs,min(sjnf ) as sjnf,min(bz_1) as bz_1 from subdivide_n50_xzqxs_inter_jbntbt_process group by xzqxs_fid,jbntbt_fid;

create table xzqxs_subdivide_n50 as select * from xzqxs where 1=2
> OK
> 时间: 0.004s


INSERT INTO xzqxs_subdivide_n50(ogc_fid,bsm,ysdm,xzqdm,xzqmc,jsmj,bz,wkb_geometry) select ogc_fid,bsm,ysdm,xzqdm,xzqmc,jsmj,bz,ST_Multi(ST_SubDivide(wkb_geometry,50)) wkb_geometry from xzqxs
> Affected rows: 8728
> 时间: 3.12s


CREATE INDEX spatial_xzqxs_subdivide_n50_geom_idx
ON xzqxs_subdivide_n50
USING GIST (wkb_geometry)
> OK
> 时间: 0.036s


CREATE TABLE subdivide_n50_xzqxs_inter_jbntbt_process (
	fid serial PRIMARY KEY,
	wkb_geometry geometry ( MultiPolygon, 4528 ),
	xzqxs_fid int4,
	bsm VARCHAR ( 18 ),
	ysdm VARCHAR ( 10 ),
	xzqdm VARCHAR ( 12 ),
	xzqmc VARCHAR ( 100 ),
	jsmj float4,
	bz VARCHAR ( 255 ),
	jbntbt_fid int4,
	bsm_1 VARCHAR ( 18 ),
	ysdm_1 VARCHAR ( 10 ),
	xzqmc_1 VARCHAR ( 100 ),
	yjjbnttbbh VARCHAR ( 20 ),
	tbbh VARCHAR ( 8 ),
	dlbm VARCHAR ( 4 ),
	dlmc VARCHAR ( 60 ),
	qsxz VARCHAR ( 2 ),
	qsdwdm VARCHAR ( 19 ),
	qsdwmc VARCHAR ( 60 ),
	zldwdm VARCHAR ( 19 ),
	zldwmc VARCHAR ( 60 ),
	yjjbnttbmj float4,
	kcdlbm VARCHAR ( 4 ),
	tkxs float4,
	tkmj float4,
	yjjbntmj float4,
	gdlx VARCHAR ( 2 ),
	yjjbntlx VARCHAR ( 1 ),
	gdpdjb VARCHAR ( 2 ),
	tbxhdm VARCHAR ( 4 ),
	tbxhmc VARCHAR ( 20 ),
	gdzzsxdm VARCHAR ( 4 ),
	gdzzsxmc VARCHAR ( 10 ),
	gddb int4,
	gddj int4,
	frdbs VARCHAR ( 1 ),
	sjnf int4,
	bz_1 VARCHAR ( 255 )
)
> OK
> 时间: 0.011s


explain analyze 
insert into subdivide_n50_xzqxs_inter_jbntbt_process(wkb_geometry,xzqxs_fid ,bsm ,ysdm ,xzqdm ,xzqmc ,jsmj ,bz ,jbntbt_fid ,bsm_1 ,ysdm_1 ,xzqmc_1 ,yjjbnttbbh ,tbbh ,dlbm ,dlmc ,qsxz ,qsdwdm,qsdwmc,zldwdm,zldwmc,yjjbnttbmj ,kcdlbm,tkxs ,tkmj ,yjjbntmj ,gdlx ,yjjbntlx ,gdpdjb,tbxhdm ,tbxhmc ,gdzzsxdm ,gdzzsxmc ,gddb,gddj,frdbs ,sjnf ,bz_1)
with 
a as (select * from xzqxs_subdivide_n50),
b as (select * from yjjnbt),
resultTable as (select st_multi(ST_CollectionExtract(st_intersection(a.wkb_geometry,b.wkb_geometry),3)) as wkb_geometry ,
a.ogc_fid as xzqxs_fid, a.bsm,a.ysdm,a.xzqdm,a.xzqmc,a.jsmj,a.bz,
b.ogc_fid as jbntbt_fid,b.bsm as bsm_1,b.ysdm as ysdm_1,b.xzqmc as xzqmc_1,b.yjjbnttbbh,b.tbbh,b.dlbm,b.dlmc,b.qsxz,b.qsdwdm,b.qsdwmc,b.zldwdm,b.zldwmc,b.yjjbnttbmj,b.kcdlbm,b.tkxs,b.tkmj,b.yjjbntmj,b.gdlx,b.yjjbntlx,b.gdpdjb,b.tbxhdm,b.tbxhmc,b.gdzzsxdm,b.gdzzsxmc,b.gddb,b.gddj,b.frdbs,b.sjnf,b.bz as bz_1
from a,b where ST_Intersects(a.wkb_geometry,b.wkb_geometry))
select * from resultTable where ST_Area(wkb_geometry)>0 and ST_IsValid(wkb_geometry)
> Affected rows: 0
> 时间: 77.651s


CREATE TABLE subdivide_n50_xzqxs_inter_jbntbt (
	fid serial PRIMARY KEY,
	wkb_geometry geometry ( MultiPolygon, 4528 ),
	xzqxs_fid int4,
	bsm VARCHAR ( 18 ),
	ysdm VARCHAR ( 10 ),
	xzqdm VARCHAR ( 12 ),
	xzqmc VARCHAR ( 100 ),
	jsmj float4,
	bz VARCHAR ( 255 ),
	jbntbt_fid int4,
	bsm_1 VARCHAR ( 18 ),
	ysdm_1 VARCHAR ( 10 ),
	xzqmc_1 VARCHAR ( 100 ),
	yjjbnttbbh VARCHAR ( 20 ),
	tbbh VARCHAR ( 8 ),
	dlbm VARCHAR ( 4 ),
	dlmc VARCHAR ( 60 ),
	qsxz VARCHAR ( 2 ),
	qsdwdm VARCHAR ( 19 ),
	qsdwmc VARCHAR ( 60 ),
	zldwdm VARCHAR ( 19 ),
	zldwmc VARCHAR ( 60 ),
	yjjbnttbmj float4,
	kcdlbm VARCHAR ( 4 ),
	tkxs float4,
	tkmj float4,
	yjjbntmj float4,
	gdlx VARCHAR ( 2 ),
	yjjbntlx VARCHAR ( 1 ),
	gdpdjb VARCHAR ( 2 ),
	tbxhdm VARCHAR ( 4 ),
	tbxhmc VARCHAR ( 20 ),
	gdzzsxdm VARCHAR ( 4 ),
	gdzzsxmc VARCHAR ( 10 ),
	gddb int4,
	gddj int4,
	frdbs VARCHAR ( 1 ),
	sjnf int4,
	bz_1 VARCHAR ( 255 )
)
> OK
> 时间: 0.008s


insert into subdivide_n50_xzqxs_inter_jbntbt(wkb_geometry,xzqxs_fid ,bsm ,ysdm ,xzqdm ,xzqmc ,jsmj ,bz ,jbntbt_fid ,bsm_1 ,ysdm_1 ,xzqmc_1 ,yjjbnttbbh ,tbbh ,dlbm ,dlmc ,qsxz ,qsdwdm,qsdwmc,zldwdm,zldwmc,yjjbnttbmj ,kcdlbm,tkxs ,tkmj ,yjjbntmj ,gdlx ,yjjbntlx ,gdpdjb,tbxhdm ,tbxhmc ,gdzzsxdm ,gdzzsxmc ,gddb,gddj,frdbs ,sjnf ,bz_1) select st_multi(st_union(wkb_geometry)) as wkb_geometry,min(xzqxs_fid) as xzqxs_fid,min(bsm ) as bsm,min(ysdm ) as ysdm,min(xzqdm ) as xzqdm,min(xzqmc ) as xzqmc,min(jsmj ) as jsmj,min(bz ) as bz,min(jbntbt_fid ) as jbntbt_fid,min(bsm_1 ) as bsm_1,min(ysdm_1 ) as ysdm_1,min(xzqmc_1 ) as xzqmc_1,min(yjjbnttbbh ) as yjjbnttbbh,min(tbbh ) as tbbh,min(dlbm ) as dlbm,min(dlmc ) as dlmc,min(qsxz ) as qsxz,min(qsdwdm) as qsdwdm,min(qsdwmc) as qsdwmc,min(zldwdm) as zldwdm,min(zldwmc) as zldwmc,min(yjjbnttbmj ) as yjjbnttbmj,min(kcdlbm) as kcdlbm,min(tkxs ) as tkxs,min(tkmj ) as tkmj,min(yjjbntmj ) as yjjbntmj,min(gdlx ) as gdlx,min(yjjbntlx ) as yjjbntlx,min(gdpdjb) as gdpdjb,min(tbxhdm ) as tbxhdm,min(tbxhmc ) as tbxhmc,min(gdzzsxdm ) as gdzzsxdm,min(gdzzsxmc ) as gdzzsxmc, min(gddb) as gddb,min(gddj) as gddj,min(frdbs ) as frdbs,min(sjnf ) as sjnf,min(bz_1) as bz_1 from subdivide_n50_xzqxs_inter_jbntbt_process group by xzqxs_fid,jbntbt_fid
> Affected rows: 250165
> 时间: 15.03s

6.2.2. 200个节点拆分

create table xzqxs_subdivide_n200 as select * from xzqxs where 1=2;

INSERT INTO xzqxs_subdivide_n200(ogc_fid,bsm,ysdm,xzqdm,xzqmc,jsmj,bz,wkb_geometry) select ogc_fid,bsm,ysdm,xzqdm,xzqmc,jsmj,bz,ST_Multi(ST_SubDivide(wkb_geometry,200)) wkb_geometry from xzqxs;

CREATE INDEX spatial_xzqxs_xzqxs_subdivide_n200_geom_idx
ON xzqxs_subdivide_n200
USING GIST (wkb_geometry);


CREATE TABLE subdivide_n200_xzqxs_inter_jbntbt_process (
	fid serial PRIMARY KEY,
	wkb_geometry geometry ( MultiPolygon, 4528 ),
	xzqxs_fid int4,
	bsm VARCHAR ( 18 ),
	ysdm VARCHAR ( 10 ),
	xzqdm VARCHAR ( 12 ),
	xzqmc VARCHAR ( 100 ),
	jsmj float4,
	bz VARCHAR ( 255 ),
	jbntbt_fid int4,
	bsm_1 VARCHAR ( 18 ),
	ysdm_1 VARCHAR ( 10 ),
	xzqmc_1 VARCHAR ( 100 ),
	yjjbnttbbh VARCHAR ( 20 ),
	tbbh VARCHAR ( 8 ),
	dlbm VARCHAR ( 4 ),
	dlmc VARCHAR ( 60 ),
	qsxz VARCHAR ( 2 ),
	qsdwdm VARCHAR ( 19 ),
	qsdwmc VARCHAR ( 60 ),
	zldwdm VARCHAR ( 19 ),
	zldwmc VARCHAR ( 60 ),
	yjjbnttbmj float4,
	kcdlbm VARCHAR ( 4 ),
	tkxs float4,
	tkmj float4,
	yjjbntmj float4,
	gdlx VARCHAR ( 2 ),
	yjjbntlx VARCHAR ( 1 ),
	gdpdjb VARCHAR ( 2 ),
	tbxhdm VARCHAR ( 4 ),
	tbxhmc VARCHAR ( 20 ),
	gdzzsxdm VARCHAR ( 4 ),
	gdzzsxmc VARCHAR ( 10 ),
	gddb int4,
	gddj int4,
	frdbs VARCHAR ( 1 ),
	sjnf int4,
	bz_1 VARCHAR ( 255 )
);


explain analyze 
insert into subdivide_n200_xzqxs_inter_jbntbt_process(wkb_geometry,xzqxs_fid ,bsm ,ysdm ,xzqdm ,xzqmc ,jsmj ,bz ,jbntbt_fid ,bsm_1 ,ysdm_1 ,xzqmc_1 ,yjjbnttbbh ,tbbh ,dlbm ,dlmc ,qsxz ,qsdwdm,qsdwmc,zldwdm,zldwmc,yjjbnttbmj ,kcdlbm,tkxs ,tkmj ,yjjbntmj ,gdlx ,yjjbntlx ,gdpdjb,tbxhdm ,tbxhmc ,gdzzsxdm ,gdzzsxmc ,gddb,gddj,frdbs ,sjnf ,bz_1)
with 
a as (select * from xzqxs_subdivide_n200),
b as (select * from yjjnbt),
resultTable as (select st_multi(ST_CollectionExtract(st_intersection(a.wkb_geometry,b.wkb_geometry),3)) as wkb_geometry ,
a.ogc_fid as xzqxs_fid, a.bsm,a.ysdm,a.xzqdm,a.xzqmc,a.jsmj,a.bz,
b.ogc_fid as jbntbt_fid,b.bsm as bsm_1,b.ysdm as ysdm_1,b.xzqmc as xzqmc_1,b.yjjbnttbbh,b.tbbh,b.dlbm,b.dlmc,b.qsxz,b.qsdwdm,b.qsdwmc,b.zldwdm,b.zldwmc,b.yjjbnttbmj,b.kcdlbm,b.tkxs,b.tkmj,b.yjjbntmj,b.gdlx,b.yjjbntlx,b.gdpdjb,b.tbxhdm,b.tbxhmc,b.gdzzsxdm,b.gdzzsxmc,b.gddb,b.gddj,b.frdbs,b.sjnf,b.bz as bz_1
from a,b where ST_Intersects(a.wkb_geometry,b.wkb_geometry))
select * from resultTable where ST_Area(wkb_geometry)>0 and ST_IsValid(wkb_geometry);


CREATE TABLE subdivide_n200_xzqxs_inter_jbntbt (
	fid serial PRIMARY KEY,
	wkb_geometry geometry ( MultiPolygon, 4528 ),
	xzqxs_fid int4,
	bsm VARCHAR ( 18 ),
	ysdm VARCHAR ( 10 ),
	xzqdm VARCHAR ( 12 ),
	xzqmc VARCHAR ( 100 ),
	jsmj float4,
	bz VARCHAR ( 255 ),
	jbntbt_fid int4,
	bsm_1 VARCHAR ( 18 ),
	ysdm_1 VARCHAR ( 10 ),
	xzqmc_1 VARCHAR ( 100 ),
	yjjbnttbbh VARCHAR ( 20 ),
	tbbh VARCHAR ( 8 ),
	dlbm VARCHAR ( 4 ),
	dlmc VARCHAR ( 60 ),
	qsxz VARCHAR ( 2 ),
	qsdwdm VARCHAR ( 19 ),
	qsdwmc VARCHAR ( 60 ),
	zldwdm VARCHAR ( 19 ),
	zldwmc VARCHAR ( 60 ),
	yjjbnttbmj float4,
	kcdlbm VARCHAR ( 4 ),
	tkxs float4,
	tkmj float4,
	yjjbntmj float4,
	gdlx VARCHAR ( 2 ),
	yjjbntlx VARCHAR ( 1 ),
	gdpdjb VARCHAR ( 2 ),
	tbxhdm VARCHAR ( 4 ),
	tbxhmc VARCHAR ( 20 ),
	gdzzsxdm VARCHAR ( 4 ),
	gdzzsxmc VARCHAR ( 10 ),
	gddb int4,
	gddj int4,
	frdbs VARCHAR ( 1 ),
	sjnf int4,
	bz_1 VARCHAR ( 255 )
);

insert into subdivide_n200_xzqxs_inter_jbntbt(wkb_geometry,xzqxs_fid ,bsm ,ysdm ,xzqdm ,xzqmc ,jsmj ,bz ,jbntbt_fid ,bsm_1 ,ysdm_1 ,xzqmc_1 ,yjjbnttbbh ,tbbh ,dlbm ,dlmc ,qsxz ,qsdwdm,qsdwmc,zldwdm,zldwmc,yjjbnttbmj ,kcdlbm,tkxs ,tkmj ,yjjbntmj ,gdlx ,yjjbntlx ,gdpdjb,tbxhdm ,tbxhmc ,gdzzsxdm ,gdzzsxmc ,gddb,gddj,frdbs ,sjnf ,bz_1) select st_multi(st_union(wkb_geometry)) as wkb_geometry,min(xzqxs_fid) as xzqxs_fid,min(bsm ) as bsm,min(ysdm ) as ysdm,min(xzqdm ) as xzqdm,min(xzqmc ) as xzqmc,min(jsmj ) as jsmj,min(bz ) as bz,min(jbntbt_fid ) as jbntbt_fid,min(bsm_1 ) as bsm_1,min(ysdm_1 ) as ysdm_1,min(xzqmc_1 ) as xzqmc_1,min(yjjbnttbbh ) as yjjbnttbbh,min(tbbh ) as tbbh,min(dlbm ) as dlbm,min(dlmc ) as dlmc,min(qsxz ) as qsxz,min(qsdwdm) as qsdwdm,min(qsdwmc) as qsdwmc,min(zldwdm) as zldwdm,min(zldwmc) as zldwmc,min(yjjbnttbmj ) as yjjbnttbmj,min(kcdlbm) as kcdlbm,min(tkxs ) as tkxs,min(tkmj ) as tkmj,min(yjjbntmj ) as yjjbntmj,min(gdlx ) as gdlx,min(yjjbntlx ) as yjjbntlx,min(gdpdjb) as gdpdjb,min(tbxhdm ) as tbxhdm,min(tbxhmc ) as tbxhmc,min(gdzzsxdm ) as gdzzsxdm,min(gdzzsxmc ) as gdzzsxmc, min(gddb) as gddb,min(gddj) as gddj,min(frdbs ) as frdbs,min(sjnf ) as sjnf,min(bz_1) as bz_1 from subdivide_n200_xzqxs_inter_jbntbt_process group by xzqxs_fid,jbntbt_fid;

create table xzqxs_subdivide_n200 as select * from xzqxs where 1=2
> OK
> 时间: 0.007s


INSERT INTO xzqxs_subdivide_n200(ogc_fid,bsm,ysdm,xzqdm,xzqmc,jsmj,bz,wkb_geometry) select ogc_fid,bsm,ysdm,xzqdm,xzqmc,jsmj,bz,ST_Multi(ST_SubDivide(wkb_geometry,200)) wkb_geometry from xzqxs
> Affected rows: 2199
> 时间: 1.99s


CREATE INDEX spatial_xzqxs_xzqxs_subdivide_n200_geom_idx
ON xzqxs_subdivide_n200
USING GIST (wkb_geometry)
> OK
> 时间: 0.011s


CREATE TABLE subdivide_n200_xzqxs_inter_jbntbt_process (
	fid serial PRIMARY KEY,
	wkb_geometry geometry ( MultiPolygon, 4528 ),
	xzqxs_fid int4,
	bsm VARCHAR ( 18 ),
	ysdm VARCHAR ( 10 ),
	xzqdm VARCHAR ( 12 ),
	xzqmc VARCHAR ( 100 ),
	jsmj float4,
	bz VARCHAR ( 255 ),
	jbntbt_fid int4,
	bsm_1 VARCHAR ( 18 ),
	ysdm_1 VARCHAR ( 10 ),
	xzqmc_1 VARCHAR ( 100 ),
	yjjbnttbbh VARCHAR ( 20 ),
	tbbh VARCHAR ( 8 ),
	dlbm VARCHAR ( 4 ),
	dlmc VARCHAR ( 60 ),
	qsxz VARCHAR ( 2 ),
	qsdwdm VARCHAR ( 19 ),
	qsdwmc VARCHAR ( 60 ),
	zldwdm VARCHAR ( 19 ),
	zldwmc VARCHAR ( 60 ),
	yjjbnttbmj float4,
	kcdlbm VARCHAR ( 4 ),
	tkxs float4,
	tkmj float4,
	yjjbntmj float4,
	gdlx VARCHAR ( 2 ),
	yjjbntlx VARCHAR ( 1 ),
	gdpdjb VARCHAR ( 2 ),
	tbxhdm VARCHAR ( 4 ),
	tbxhmc VARCHAR ( 20 ),
	gdzzsxdm VARCHAR ( 4 ),
	gdzzsxmc VARCHAR ( 10 ),
	gddb int4,
	gddj int4,
	frdbs VARCHAR ( 1 ),
	sjnf int4,
	bz_1 VARCHAR ( 255 )
)
> OK
> 时间: 0.013s


explain analyze 
insert into subdivide_n200_xzqxs_inter_jbntbt_process(wkb_geometry,xzqxs_fid ,bsm ,ysdm ,xzqdm ,xzqmc ,jsmj ,bz ,jbntbt_fid ,bsm_1 ,ysdm_1 ,xzqmc_1 ,yjjbnttbbh ,tbbh ,dlbm ,dlmc ,qsxz ,qsdwdm,qsdwmc,zldwdm,zldwmc,yjjbnttbmj ,kcdlbm,tkxs ,tkmj ,yjjbntmj ,gdlx ,yjjbntlx ,gdpdjb,tbxhdm ,tbxhmc ,gdzzsxdm ,gdzzsxmc ,gddb,gddj,frdbs ,sjnf ,bz_1)
with 
a as (select * from xzqxs_subdivide_n200),
b as (select * from yjjnbt),
resultTable as (select st_multi(ST_CollectionExtract(st_intersection(a.wkb_geometry,b.wkb_geometry),3)) as wkb_geometry ,
a.ogc_fid as xzqxs_fid, a.bsm,a.ysdm,a.xzqdm,a.xzqmc,a.jsmj,a.bz,
b.ogc_fid as jbntbt_fid,b.bsm as bsm_1,b.ysdm as ysdm_1,b.xzqmc as xzqmc_1,b.yjjbnttbbh,b.tbbh,b.dlbm,b.dlmc,b.qsxz,b.qsdwdm,b.qsdwmc,b.zldwdm,b.zldwmc,b.yjjbnttbmj,b.kcdlbm,b.tkxs,b.tkmj,b.yjjbntmj,b.gdlx,b.yjjbntlx,b.gdpdjb,b.tbxhdm,b.tbxhmc,b.gdzzsxdm,b.gdzzsxmc,b.gddb,b.gddj,b.frdbs,b.sjnf,b.bz as bz_1
from a,b where ST_Intersects(a.wkb_geometry,b.wkb_geometry))
select * from resultTable where ST_Area(wkb_geometry)>0 and ST_IsValid(wkb_geometry)
> Affected rows: 0
> 时间: 80.451s

CREATE TABLE subdivide_n200_xzqxs_inter_jbntbt (
	fid serial PRIMARY KEY,
	wkb_geometry geometry ( MultiPolygon, 4528 ),
	xzqxs_fid int4,
	bsm VARCHAR ( 18 ),
	ysdm VARCHAR ( 10 ),
	xzqdm VARCHAR ( 12 ),
	xzqmc VARCHAR ( 100 ),
	jsmj float4,
	bz VARCHAR ( 255 ),
	jbntbt_fid int4,
	bsm_1 VARCHAR ( 18 ),
	ysdm_1 VARCHAR ( 10 ),
	xzqmc_1 VARCHAR ( 100 ),
	yjjbnttbbh VARCHAR ( 20 ),
	tbbh VARCHAR ( 8 ),
	dlbm VARCHAR ( 4 ),
	dlmc VARCHAR ( 60 ),
	qsxz VARCHAR ( 2 ),
	qsdwdm VARCHAR ( 19 ),
	qsdwmc VARCHAR ( 60 ),
	zldwdm VARCHAR ( 19 ),
	zldwmc VARCHAR ( 60 ),
	yjjbnttbmj float4,
	kcdlbm VARCHAR ( 4 ),
	tkxs float4,
	tkmj float4,
	yjjbntmj float4,
	gdlx VARCHAR ( 2 ),
	yjjbntlx VARCHAR ( 1 ),
	gdpdjb VARCHAR ( 2 ),
	tbxhdm VARCHAR ( 4 ),
	tbxhmc VARCHAR ( 20 ),
	gdzzsxdm VARCHAR ( 4 ),
	gdzzsxmc VARCHAR ( 10 ),
	gddb int4,
	gddj int4,
	frdbs VARCHAR ( 1 ),
	sjnf int4,
	bz_1 VARCHAR ( 255 )
)
> OK
> 时间: 0.008s


insert into subdivide_n200_xzqxs_inter_jbntbt(wkb_geometry,xzqxs_fid ,bsm ,ysdm ,xzqdm ,xzqmc ,jsmj ,bz ,jbntbt_fid ,bsm_1 ,ysdm_1 ,xzqmc_1 ,yjjbnttbbh ,tbbh ,dlbm ,dlmc ,qsxz ,qsdwdm,qsdwmc,zldwdm,zldwmc,yjjbnttbmj ,kcdlbm,tkxs ,tkmj ,yjjbntmj ,gdlx ,yjjbntlx ,gdpdjb,tbxhdm ,tbxhmc ,gdzzsxdm ,gdzzsxmc ,gddb,gddj,frdbs ,sjnf ,bz_1) select st_multi(st_union(wkb_geometry)) as wkb_geometry,min(xzqxs_fid) as xzqxs_fid,min(bsm ) as bsm,min(ysdm ) as ysdm,min(xzqdm ) as xzqdm,min(xzqmc ) as xzqmc,min(jsmj ) as jsmj,min(bz ) as bz,min(jbntbt_fid ) as jbntbt_fid,min(bsm_1 ) as bsm_1,min(ysdm_1 ) as ysdm_1,min(xzqmc_1 ) as xzqmc_1,min(yjjbnttbbh ) as yjjbnttbbh,min(tbbh ) as tbbh,min(dlbm ) as dlbm,min(dlmc ) as dlmc,min(qsxz ) as qsxz,min(qsdwdm) as qsdwdm,min(qsdwmc) as qsdwmc,min(zldwdm) as zldwdm,min(zldwmc) as zldwmc,min(yjjbnttbmj ) as yjjbnttbmj,min(kcdlbm) as kcdlbm,min(tkxs ) as tkxs,min(tkmj ) as tkmj,min(yjjbntmj ) as yjjbntmj,min(gdlx ) as gdlx,min(yjjbntlx ) as yjjbntlx,min(gdpdjb) as gdpdjb,min(tbxhdm ) as tbxhdm,min(tbxhmc ) as tbxhmc,min(gdzzsxdm ) as gdzzsxdm,min(gdzzsxmc ) as gdzzsxmc, min(gddb) as gddb,min(gddj) as gddj,min(frdbs ) as frdbs,min(sjnf ) as sjnf,min(bz_1) as bz_1 from subdivide_n200_xzqxs_inter_jbntbt_process group by xzqxs_fid,jbntbt_fid
> Affected rows: 250165
> 时间: 14.955s

7. PostGIS叠加结果 7.1. 步骤6.1中的数据叠加结果

select '使用泰森多边形拆分' as split_type,count(*) from voronoi_xzqxs_inter_jbntbt
union all
select '使用ST_SubDivide每50节点拆分为要素' as split_type,count(*) from subdivide_n50_xzqxs_inter_jbntbt
union all
select '使用ST_SubDivide每200节点拆分为要素' as split_type,count(*) from subdivide_n200_xzqxs_inter_jbntbt

7.2. 步骤6.1.中数据叠加的效率表

序号	方式	耗时	产生数据条数
1	使用泰森多边形拆分	487.395s(8分7秒)	250165
2	使用ST_SubDivide每50节点拆分为要素	90.869s(1分30秒)	250165
3	使用ST_SubDivide每200节点拆分为要素	97.446s(1分37秒)	250165

8. 对比ArcGIS和QGIS 8.1. ArcGIS

ArcGIS 用时145s(2分25秒)

8.2. QGIS

QGIS 用时5109s(85分09秒)

9. 其他案例

由于本文上述表达的都是使用XZQ去叠加JBNT的数据，XZQ通过拆分之后避免了IO放大和CPU放大的问题，通过拆分之后数据条数也不是非常的大，所以这一节我们叠加两个条数比较大的数据，由于我们后续这个数据也有XZQ那样，图斑大且节点多的数据，所以我们也沿用6.2.2的方案进行叠加处理

drop table nyscsyxpjjg_subdivide_n200

create table nyscsyxpjjg_subdivide_n200 as select * from nyscsyxpjjg where 1=2;

INSERT INTO nyscsyxpjjg_subdivide_n200(ogc_fid,bsm,ysdm,xzqdm,xzqmc,,bz,wkb_geometry) select ogc_fid,bsm,ysdm,xzqdm,xzqmc,bz,ST_Multi(ST_SubDivide(wkb_geometry,200)) wkb_geometry from nyscsyxpjjg;

CREATE INDEX spatial_nyscsyxpjjg_subdivide_n200_geom_idx
ON nyscsyxpjjg_subdivide_n200
USING GIST (wkb_geometry);


drop table subdivide_n200_nyscsyxpjjg_inter_jbntbt_process

CREATE TABLE subdivide_n200_nyscsyxpjjg_inter_jbntbt_process (
	fid serial PRIMARY KEY,
	wkb_geometry geometry ( MultiPolygon, 4528 ),
	nyscsyxpjjg_fid int4,
	bsm VARCHAR ( 18 ),
	ysdm VARCHAR ( 10 ),
	xzqdm VARCHAR ( 12 ),
	xzqmc VARCHAR ( 100 ),
	bz VARCHAR ( 255 ),
	jbntbt_fid int4,
	bsm_1 VARCHAR ( 18 ),
	ysdm_1 VARCHAR ( 10 ),
	xzqmc_1 VARCHAR ( 100 ),
	yjjbnttbbh VARCHAR ( 20 ),
	tbbh VARCHAR ( 8 ),
	dlbm VARCHAR ( 4 ),
	dlmc VARCHAR ( 60 ),
	qsxz VARCHAR ( 2 ),
	qsdwdm VARCHAR ( 19 ),
	qsdwmc VARCHAR ( 60 ),
	zldwdm VARCHAR ( 19 ),
	zldwmc VARCHAR ( 60 ),
	yjjbnttbmj float4,
	kcdlbm VARCHAR ( 4 ),
	tkxs float4,
	tkmj float4,
	yjjbntmj float4,
	gdlx VARCHAR ( 2 ),
	yjjbntlx VARCHAR ( 1 ),
	gdpdjb VARCHAR ( 2 ),
	tbxhdm VARCHAR ( 4 ),
	tbxhmc VARCHAR ( 20 ),
	gdzzsxdm VARCHAR ( 4 ),
	gdzzsxmc VARCHAR ( 10 ),
	gddb int4,
	gddj int4,
	frdbs VARCHAR ( 1 ),
	sjnf int4,
	bz_1 VARCHAR ( 255 )
);


explain analyze 
insert into subdivide_n200_nyscsyxpjjg_inter_jbntbt_process(wkb_geometry,nyscsyxpjjg_fid ,bsm ,ysdm ,xzqdm ,xzqmc ,bz ,jbntbt_fid ,bsm_1 ,ysdm_1 ,xzqmc_1 ,yjjbnttbbh ,tbbh ,dlbm ,dlmc ,qsxz ,qsdwdm,qsdwmc,zldwdm,zldwmc,yjjbnttbmj ,kcdlbm,tkxs ,tkmj ,yjjbntmj ,gdlx ,yjjbntlx ,gdpdjb,tbxhdm ,tbxhmc ,gdzzsxdm ,gdzzsxmc ,gddb,gddj,frdbs ,sjnf ,bz_1)
with 
a as (select * from nyscsyxpjjg_subdivide_n200),
b as (select * from yjjnbt),
resultTable as (select st_multi(ST_CollectionExtract(st_intersection(a.wkb_geometry,b.wkb_geometry),3)) as wkb_geometry ,
a.ogc_fid as nyscsyxpjjg_fid, a.bsm,a.ysdm,a.xzqdm,a.xzqmc,a.bz,
b.ogc_fid as jbntbt_fid,b.bsm as bsm_1,b.ysdm as ysdm_1,b.xzqmc as xzqmc_1,b.yjjbnttbbh,b.tbbh,b.dlbm,b.dlmc,b.qsxz,b.qsdwdm,b.qsdwmc,b.zldwdm,b.zldwmc,b.yjjbnttbmj,b.kcdlbm,b.tkxs,b.tkmj,b.yjjbntmj,b.gdlx,b.yjjbntlx,b.gdpdjb,b.tbxhdm,b.tbxhmc,b.gdzzsxdm,b.gdzzsxmc,b.gddb,b.gddj,b.frdbs,b.sjnf,b.bz as bz_1
from a,b where ST_Intersects(a.wkb_geometry,b.wkb_geometry))
select * from resultTable where ST_Area(wkb_geometry)>0 and ST_IsValid(wkb_geometry);

drop table  subdivide_n200_nyscsyxpjjg_inter_jbntbt

CREATE TABLE subdivide_n200_nyscsyxpjjg_inter (
	fid serial PRIMARY KEY,
	wkb_geometry geometry ( MultiPolygon, 4528 ),
	nyscsyxpjjg_fid int4,
	bsm VARCHAR ( 18 ),
	ysdm VARCHAR ( 10 ),
	xzqdm VARCHAR ( 12 ),
	xzqmc VARCHAR ( 100 ),
	bz VARCHAR ( 255 ),
	jbntbt_fid int4,
	bsm_1 VARCHAR ( 18 ),
	ysdm_1 VARCHAR ( 10 ),
	xzqmc_1 VARCHAR ( 100 ),
	yjjbnttbbh VARCHAR ( 20 ),
	tbbh VARCHAR ( 8 ),
	dlbm VARCHAR ( 4 ),
	dlmc VARCHAR ( 60 ),
	qsxz VARCHAR ( 2 ),
	qsdwdm VARCHAR ( 19 ),
	qsdwmc VARCHAR ( 60 ),
	zldwdm VARCHAR ( 19 ),
	zldwmc VARCHAR ( 60 ),
	yjjbnttbmj float4,
	kcdlbm VARCHAR ( 4 ),
	tkxs float4,
	tkmj float4,
	yjjbntmj float4,
	gdlx VARCHAR ( 2 ),
	yjjbntlx VARCHAR ( 1 ),
	gdpdjb VARCHAR ( 2 ),
	tbxhdm VARCHAR ( 4 ),
	tbxhmc VARCHAR ( 20 ),
	gdzzsxdm VARCHAR ( 4 ),
	gdzzsxmc VARCHAR ( 10 ),
	gddb int4,
	gddj int4,
	frdbs VARCHAR ( 1 ),
	sjnf int4,
	bz_1 VARCHAR ( 255 )
);

insert into subdivide_n200_nyscsyxpjjg_inter_jbntbt(wkb_geometry,nyscsyxpjjg_fid ,bsm ,ysdm ,xzqdm ,xzqmc ,bz ,jbntbt_fid ,bsm_1 ,ysdm_1 ,xzqmc_1 ,yjjbnttbbh ,tbbh ,dlbm ,dlmc ,qsxz ,qsdwdm,qsdwmc,zldwdm,zldwmc,yjjbnttbmj ,kcdlbm,tkxs ,tkmj ,yjjbntmj ,gdlx ,yjjbntlx ,gdpdjb,tbxhdm ,tbxhmc ,gdzzsxdm ,gdzzsxmc ,gddb,gddj,frdbs ,sjnf ,bz_1) select st_multi(st_union(wkb_geometry)) as wkb_geometry,min(nyscsyxpjjg_fid) as nyscsyxpjjg_fid,min(bsm ) as bsm,min(ysdm ) as ysdm,min(xzqdm ) as xzqdm,min(xzqmc ) as xzqmc,min(bz ) as bz,min(jbntbt_fid ) as jbntbt_fid,min(bsm_1 ) as bsm_1,min(ysdm_1 ) as ysdm_1,min(xzqmc_1 ) as xzqmc_1,min(yjjbnttbbh ) as yjjbnttbbh,min(tbbh ) as tbbh,min(dlbm ) as dlbm,min(dlmc ) as dlmc,min(qsxz ) as qsxz,min(qsdwdm) as qsdwdm,min(qsdwmc) as qsdwmc,min(zldwdm) as zldwdm,min(zldwmc) as zldwmc,min(yjjbnttbmj ) as yjjbnttbmj,min(kcdlbm) as kcdlbm,min(tkxs ) as tkxs,min(tkmj ) as tkmj,min(yjjbntmj ) as yjjbntmj,min(gdlx ) as gdlx,min(yjjbntlx ) as yjjbntlx,min(gdpdjb) as gdpdjb,min(tbxhdm ) as tbxhdm,min(tbxhmc ) as tbxhmc,min(gdzzsxdm ) as gdzzsxdm,min(gdzzsxmc ) as gdzzsxmc, min(gddb) as gddb,min(gddj) as gddj,min(frdbs ) as frdbs,min(sjnf ) as sjnf,min(bz_1) as bz_1 from subdivide_n200_nyscsyxpjjg_inter_jbntbt group by nyscsyxpjjg_fid,jbntbt_fid;
bh ,dlbm ,dlmc ,qsxz ,qsdwdm,qsdwmc,zldwdm,zldwmc,yjjbnttbmj ,kcdlbm,tkxs ,tkmj ,yjjbntmj ,gdlx ,yjjbntlx ,gdpdjb,tbxhdm ,tbxhmc ,gdzzsxdm ,gdzzsxmc ,gddb,gddj,frdbs ,sjnf ,bz_1) select st_multi(st_union(wkb_geometry)) as wkb_geometry,min(nyscsyxpjjg_fid) as nyscsyxpjjg_fid,min(bsm ) as bsm,min(ysdm ) as ysdm,min(xzqdm ) as xzqdm,min(xzqmc ) as xzqmc,min(bz ) as bz,min(jbntbt_fid ) as jbntbt_fid,min(bsm_1 ) as bsm_1,min(ysdm_1 ) as ysdm_1,min(xzqmc_1 ) as xzqmc_1,min(yjjbnttbbh ) as yjjbnttbbh,min(tbbh ) as tbbh,min(dlbm ) as dlbm,min(dlmc ) as dlmc,min(qsxz ) as qsxz,min(qsdwdm) as qsdwdm,min(qsdwmc) as qsdwmc,min(zldwdm) as zldwdm,min(zldwmc) as zldwmc,min(yjjbnttbmj ) as yjjbnttbmj,min(kcdlbm) as kcdlbm,min(tkxs ) as tkxs,min(tkmj ) as tkmj,min(yjjbntmj ) as yjjbntmj,min(gdlx ) as gdlx,min(yjjbntlx ) as yjjbntlx,min(gdpdjb) as gdpdjb,min(tbxhdm ) as tbxhdm,min(tbxhmc ) as tbxhmc,min(gdzzsxdm ) as gdzzsxdm,min(gdzzsxmc ) as gdzzsxmc, min(gddb) as gddb,min(gddj) as gddj,min(frdbs ) as frdbs,min(sjnf ) as sjnf,min(bz_1) as bz_1 from subdivide_n200_nyscsyxpjjg_inter_jbntbt group by nyscsyxpjjg_fid,jbntbt_fid;

PostGIS实现（大数据量，大图斑）图层间相交工具

大数据系统相关栏目本月热门文章